Excel and access

[Tom]

Hi all,

Now my company work with Excel like this:

1 set of data is downloaded from a certain website as xls file.

second set of data (different data) is downloaded from the company website
as xls file.

Then, we combine to 2 files into one xls file add some columns and process
the data.

I need to convert this to access:
Can I import from xls file into a table with a push of a button? can I do
append from xls to a table?

How do I start this DB application? Any ideas?

TIA,
Tom

 

[Jeff Boyce]

Tom

What is it that you think Access will be able to do with the data that you
are not already able to do with Excel?

You can use Access to import XLS (and many other) data files, but Access
decides what the datatypes are.

Your best bet, if you want to be able to use the data with the tools that
Access offers (as a relational database -- it is not a 'spreadsheet on
steroids'), you'll need to first do the work to normalize your (Access) data
structure. Then you'd build queries to parse the imported Excel data into
the well-normalized table design.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom]

Thanks Jeff for your response.
The intension wasn't to get a better spreadsheet, but to handle better the
data, so it will be easy to create queries and avoid dups.
Isn't that access purpose?
Thing is that data that has to be processed comes in xls format(which I
don't have control on the format), and I`m not sure how to integrate the
data.

TIA,
Tom

 

[John Vinson]

Yes. See "TransferSpreadsheet" in the VBA help file.

Ummmm... by opening Access? What "application" are you trying to
start? It sounds like you're saying it's already written...

John W. Vinson[MVP]

 

[Tom]

Thank you John.
I'm sorry if I mislead you.
I didn't write the actual application yet, first I try to figure how the app
will run and what functions I will need and then I write it.
The isuue is that right now, on the spreadsheet there are endless columns
and more than 25000 entries, I thought to move these process to access will
make it easier in terms of queries, sorting and reports. Please correct me
if I'm wrong.
I tried the "TransferSpreadsheet" function and most of the data converted
but I had some conversion errors.
What is the best way to avoid these error?
Second question:
The data come in XLS format, and has to be add to the application and avoid
dups, what is the best way to do that?
I tought to create macro that convert the xls file into a temp table, run an
append query and delete the temp table, but how do I avoid dups or how do I
let the user choose which one to keep and which to delete?

TIA,
Tom

 

[Jeff Boyce]

Tom

see comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you John.
I'm sorry if I mislead you.
I didn't write the actual application yet, first I try to figure how the
app will run and what functions I will need and then I write it.
The isuue is that right now, on the spreadsheet there are endless columns
and more than 25000 entries, I thought to move these process to access
will make it easier in terms of queries, sorting and reports. Please
correct me if I'm wrong.

If all you do is put the Excel structure in an Access .mdb file, you will
not get the benefits that Access offers. To best use Access'
features/functions, your data has to be well-normalized. "...endless
columns..." describes a spreadsheet, but not a relational table.

If the term "normalized" does not compute, read up on it before proceeding
any further. It is not a trivial exercise. See:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

(especially Database Design 101)

I tried the "TransferSpreadsheet" function and most of the data converted
but I had some conversion errors.
What is the best way to avoid these error?

We're not there. We can't see the error messages you received. It's a bit
tough to tell you how to avoid something if we don't know what it is...

Second question:
The data come in XLS format, and has to be add to the application and
avoid dups, what is the best way to do that?

What is your definition of "dups"? This is not facetious ... for instance,
do you consider the following to be dups:?
12345 Elm St
12345 Elm Street
12345 Elm St SE
12345 Elm
12354 Elm St
12345 Elm St, Apt 101
If you can't tell Access (or some other person) how to evaluate "what is a
dup", you won't be able to avoid them. And in some cases, you won't even be
able to tell Access, so you'll have to rely on USB (using someone's brain).

I tought to create macro that convert the xls file into a temp table, run
an append query and delete the temp table, but how do I avoid dups or how
do I let the user choose which one to keep and which to delete?

Not a trivial exercise ... see previous.

 

[Tom]

Thank you Jeff for your input, I appreciate that.
I`m not access expert, but I manage to write some well normalized
application and I understand the concepts.
but it was all relatively easy when I needed to create a blank DB and when
the users are entering the data.
In this case, 95% of the data come from uncontrolled (I can't change the
format or write a web app) web sources in xls format, in each file, there is
about 2000 record. it's better to leave it as is than enter the data
manually.
How do you break this endless columns spreadsheet into tables?
I'll try to give some more info:
First set of data is basic information about customers: name, dob, addresses
etc..
Second set is billing: credit card, name, billing address etc..
we paste these 2 into one spreadsheet, then we add some more culomns
regarding different info that we fill out manualy.
So, even if I import parts into different tabels, I need these latter
columns to be added.
I hope I'm clear.
In your opinion, is it better to leave it excel or is there a some way to
approach this so I can convert it to access?

Thanks,
Tom

 

[John Vinson]

Thank you John.
I'm sorry if I mislead you.
I didn't write the actual application yet, first I try to figure how the app
will run and what functions I will need and then I write it.

sorry I misread your post - that's pretty clear actually!

The isuue is that right now, on the spreadsheet there are endless columns
and more than 25000 entries, I thought to move these process to access will
make it easier in terms of queries, sorting and reports. Please correct me
if I'm wrong.

Well... again: Excel is a spreadsheet, a good one. Access is a
relational database. They are NOT EQUIVALENT. Yes, you'll use
normalized Tables, Queries, Reports, and almost surely some VBA
modules (called "macros" in Excel jargon, Access macros are something
else). You can probably accomplish what you need to do in Access, but
to do so you will have to rethink *how* you are doing it, and how the
database must be structured to allow it.

I tried the "TransferSpreadsheet" function and most of the data converted
but I had some conversion errors.

Access has "strong data typing" and Excel doesn't; this can often
cause such problems when you're moving data from Excel into Access.
For example in Excel you could very plausibly have a column of numbers
with a few rows containing "N/A" (not available). No problem, Excel
will ignore this text string in calculations; but it will cause Access
to choke with an error when you attempt to load it into a Number
field.

What is the best way to avoid these error?

Clean the data; or deal with the errors as they occur, manually or in
VBA code.

Second question:
The data come in XLS format, and has to be add to the application and avoid
dups, what is the best way to do that?

I'd suggest creating an Access table with fields of the appropriate
datatypes and sizes; define a unique Index on those fields which
constitues a "dup" (see below!!!); link to the spreadsheet and run an
Append query to migrate the data into the table. Duplicates will be
removed in the process, with a warning message (which can be
suppressed if you so choose).

Duplicates are tricky. If you have people's names, bear in mind that
names are NOT unique - I know three Fred Browns, right here in the
little town of Parma.

I tought to create macro that convert the xls file into a temp table, run an
append query and delete the temp table, but how do I avoid dups or how do I
let the user choose which one to keep and which to delete?

You can prevent dups by setting a unique Index. You can find dups
using a "find duplicates" query, and present them to the user on a
Form to allow the user to decide. Depends on the business need!


John W. Vinson[MVP]

 

[Jeff Boyce]

John Vinson provides some excellent directions ...

My advise would be similar...

It doesn't MATTER what form the Excel data is in, it can stay that way.

For Access to do its best, the data has to be well-normalized. You'll need
to work out how to get the ?un-normalized data from the structure it comes
in (current Excel) into one/more tables in Access. John points out that you
can use Append queries to do this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom]

Thank you John and Jeff for your input.
Tom

sorry I misread your post - that's pretty clear actually!


Well... again: Excel is a spreadsheet, a good one. Access is a
relational database. They are NOT EQUIVALENT. Yes, you'll use
normalized Tables, Queries, Reports, and almost surely some VBA
modules (called "macros" in Excel jargon, Access macros are something
else). You can probably accomplish what you need to do in Access, but
to do so you will have to rethink *how* you are doing it, and how the
database must be structured to allow it.


Access has "strong data typing" and Excel doesn't; this can often
cause such problems when you're moving data from Excel into Access.
For example in Excel you could very plausibly have a column of numbers
with a few rows containing "N/A" (not available). No problem, Excel
will ignore this text string in calculations; but it will cause Access
to choke with an error when you attempt to load it into a Number
field.


Clean the data; or deal with the errors as they occur, manually or in
VBA code.


I'd suggest creating an Access table with fields of the appropriate
datatypes and sizes; define a unique Index on those fields which
constitues a "dup" (see below!!!); link to the spreadsheet and run an
Append query to migrate the data into the table. Duplicates will be
removed in the process, with a warning message (which can be
suppressed if you so choose).

Duplicates are tricky. If you have people's names, bear in mind that
names are NOT unique - I know three Fred Browns, right here in the
little town of Parma.


You can prevent dups by setting a unique Index. You can find dups
using a "find duplicates" query, and present them to the user on a
Form to allow the user to decide. Depends on the business need!


John W. Vinson[MVP]