Import from EXcel to Access

[Guest]

Hello,
I have 5 spread sheets with same columns but different data that I need to
import into one table in Access.

I can import the first sheet then when I try to import the second sheet to
the existing table I get an error message stating an error has occurred.

Any idea's to what can be causing it
Thank you very much...your offered is truly appreciated.

 

[Guest]

Without details, it is impossible to give you an exact answer. What is most
likely happening is a data type problem. Is it creating an import errors
table? If so, it will shed some light on what the error may be.

If you are importing into a new table, Access makes some guesses on what the
data type should be based on looking at the first few rows in the
spreadsheet. Because Excel is very lax about what you can put in a cell,
columns can end up with ambiguous data. Some text, some number, etc.
Access, on the other hand, is more rigid. If Access has determined a column
should be numeric and then encounters text, it will create this problem.

Another thing to be aware of. If you are importing into an existing table,
the import process overwrites the current data in the table, so if you import
five spreadsheets into it, you will only have the data in the last sheet.

I would suggest you create your destination table to match the columns in
the spreadsheet and define the field sizes and data types you want. Then,
rather than an import, link to the spreadsheet. Use an append query that
will copy the data in the spreadsheet into the table. That way, you are less
likely to encounter an error and the table will contain the data from all the
sheets.

 

[zifiri]

Thanks for your clear explanation,

I have inserted a row under the field names in Excel. This row simple
tells each column what the validation should be
It worked magically and I did not get import errors anymore.

The second row's values were like " text, text, 0,00, 01.12.2006, text,
12345 " etc etc.

Thanks again
Best Wishes
Atila Akal

 

[Guest]

I have inserted a row under the field names in Excel. This row simple

tells each column what the validation should be

That's interesting. I would not have thought of that. Isn't that row
ending up as a row in your Access table? And, are you using append queries
so all 5 sheets are getting into the table?

 

[onedaywhen]

That's interesting. I would not have thought of that. Isn't that row
ending up as a row in your Access table?

This is a standard trick to workaround Excel data typing issues.

Good point about the dummy row ending up in the target table. If this
is undesirable or it is not possible to change the source data,
changing the registry values may help. Details and background here:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Jamie.

--

 

[Guest]

Good info. We do a lot of Excel imports and exports here. I have them
pretty well under my control since I took ownership of this database;
however, I see some possibility for improvement.

My question has to do with registry modification (a scary idea), how can I
control that where there are 30 users scattered across a large manufacturing
facility with 40 or so buildings?

 

[onedaywhen]

My question has to do with registry modification (a scary idea), how can I
control that where there are 30 users scattered across a large manufacturing
facility with 40 or so buildings?

Note the reg keys are hkey_local_machine. If all the users have admin
rights to their machines then you could provide VBA code; if the system
admins have the machines locked down there's not much you can do.

Changing the registry keys is not the solution in all cases, of course;
with 30 machines it may not even be worth contemplating. Not using
Excel to store data may be the solution <vbg>.

Jamie.

--

 

[Guest]

Thanks, that is about what I expected.

Not using Excel to store data may be the solution

What planet do you live on? I want to do there, too. On our world, we have
to live with what the users want to support the business model.

Actually, we don't "store" data in Excel. Our monthly data feed comes in in
Excel, but they have been kind enough to send it in csv format. The part I
can't fix, because the users all love Excel, is that data is entered in
spreadsheets that I then have to pull in, clean up, and store in the database.

The absolute worst is that each month end, I produce a spreadsheet the users
make adjustments to that is then reimported into the database. This is my
biggest nightmare, because there is no validation in the spreadsheet data
entry and very often they forget to change field A when they change field B
which then causes inconsistencies in the database.

I have tried to get them to do the adjustments in Access where I can
validate the entries as they happen, but this bunch rejects that. "Excel is
so much easier to use and we really know it, and we can do mutiple rows at a
time, and it is fast, and by the way, why are the numbers not correct in the
database?"

of course I'm mad, I've always been mad.....
Pink Floyd

 

[zifiri]

.. Isn't that row

ending up as a row in your Access table?

Yes it does but I can easly clean them up by filtering and deleting
these dummy rows.



And, are you using append queries

so all 5 sheets are getting into the table?

I am using more then 20 sheets to append actually.

By the way I have discovered that if the dummy rows were 10 or 15 then
it would work better.

Best Wishes

Atila Akal