Import Excel to Access

[Sandy]

I would like to import an Excel file into an existing Access DB. I created
the Excel file to have the same headings and information (in the same order)
as the 'Main Table' I have in Access. When I import it I get an error
message that states that... Field 'SECTION NUMBER' doesn't exist in
destination table 'MAIN TABLE.' I looked at my db called Main Table and
there is a field called SECTION NUMBER. So I am confused with the error.
Any suggestions?

 

[Klatuu]

This may be a case of incorrect naming. Spaces should not be in any names in
your database. You will see sometime people will enclose a bad name in
brackets, like: [SECTION NUMBER] to avoid the problem, but since you are
probably using TransferSpreadsheet, you can't do that. I would suggest you
use an underscore instead of a space:SECTION_NUMBER and see if that resolves
the issue. Just in case, double check spelling between the two names.

 

[Sandy]

Some background info. I am using a db that someone created for me years ago
so that I can enter information that comes to me in paper form. So this db
has reports, queries, etc. associated with it and it runs fine. The reason
for the import is that another university that sends us information uses
Excel for their records and they were hoping to not have to send us the paper
form. I did see in design view that there are spaces in the field names in
my db. I am afraid if I change the name to SECTION_NUMBER in the MAIN TABLE
that it will affect the reports and queries that use SECTION NUMBER. Am I
correct in that assumption?

This may be a case of incorrect naming. Spaces should not be in any names in
your database. You will see sometime people will enclose a bad name in
brackets, like: [SECTION NUMBER] to avoid the problem, but since you are
probably using TransferSpreadsheet, you can't do that. I would suggest you
use an underscore instead of a space:SECTION_NUMBER and see if that resolves
the issue. Just in case, double check spelling between the two names.
--
Dave Hargis, Microsoft Access MVP

I would like to import an Excel file into an existing Access DB. I created
the Excel file to have the same headings and information (in the same order)
as the 'Main Table' I have in Access. When I import it I get an error
message that states that... Field 'SECTION NUMBER' doesn't exist in
destination table 'MAIN TABLE.' I looked at my db called Main Table and
there is a field called SECTION NUMBER. So I am confused with the error.
Any suggestions?

 

[Klatuu]

If you change that name, then yes, you would have to modify everything that
uses that field. But, first try just changing the field name and doing the
import. If that doesn't work, then we will need to dig a little deeper. If
it does, then change the names where needed.

Another approach would be rather than a direct import, try linking to the
Excel sheet, running an append query to move the data into your Access table,
then drop the link to the Excel sheet.
--
Dave Hargis, Microsoft Access MVP

Some background info. I am using a db that someone created for me years ago
so that I can enter information that comes to me in paper form. So this db
has reports, queries, etc. associated with it and it runs fine. The reason
for the import is that another university that sends us information uses
Excel for their records and they were hoping to not have to send us the paper
form. I did see in design view that there are spaces in the field names in
my db. I am afraid if I change the name to SECTION_NUMBER in the MAIN TABLE
that it will affect the reports and queries that use SECTION NUMBER. Am I
correct in that assumption?

This may be a case of incorrect naming. Spaces should not be in any names in
your database. You will see sometime people will enclose a bad name in
brackets, like: [SECTION NUMBER] to avoid the problem, but since you are
probably using TransferSpreadsheet, you can't do that. I would suggest you
use an underscore instead of a space:SECTION_NUMBER and see if that resolves
the issue. Just in case, double check spelling between the two names.
--
Dave Hargis, Microsoft Access MVP

I would like to import an Excel file into an existing Access DB. I created
the Excel file to have the same headings and information (in the same order)
as the 'Main Table' I have in Access. When I import it I get an error
message that states that... Field 'SECTION NUMBER' doesn't exist in
destination table 'MAIN TABLE.' I looked at my db called Main Table and
there is a field called SECTION NUMBER. So I am confused with the error.
Any suggestions?

 

[Sandy]

I did change the name and it did work. So I changed the others also and it
all imported. Of course, like you stated, some of the reports aren't
working. I slowly have been trying to fix things as I find them. (I am
doing this in a test db) Some things still aren't working right, so I may
try the Link you suggested in the morning to see if that is better. Thanks
for the help.

 

[Klatuu]

As to making the changes. Here is a link to a utility you can download that
will change the names in all your database objects.

http://www.rickworld.com/download.html

The free version should do it for your, but even if you want to purchase
it,it is only $39.00 US. I think it is the best $39.00 I ever spent on
software. I use it all the time.

 

[gllincoln]

Klatuu had some good suggestions - and cleaning up the names would be a
generally good thing to do.

However, I am perennially lazy and behind schedule - so here's how I have
kludged my way through various issues where Excel and Access don't play
nicely and I had to get results now.

Constraints: nulls will sometimes ruin an import of an Excel sheet - and
Excel to Access 'assumes' a datatype that is just plain wrong once in a
while; getting that info into Access can be a problem (for instance the
first 20 rows or so contain what looks like a long integer but further on
down the road, we have alpha characters.) Another issue - if someone edits
an Excel sheet - deletes a column to the right - sometimes an import into
Access 'sees' that phantom column and it fails because field28 (or whatever)
doesn't exist in the target table.

So I create a sort of middleman table - with all of the column names in
proper order, no required fields, all type text, with 255 length. As soon
as I hit phantom col failures I add those dummy columns to the middleman
table, however many I need to import the file successfully. If you get too
many phantom columns, copy just the working data area of the Excel sheet to
a blank worksheet, save that and import.

The secret here is that we construct a query that then appends the middleman
table to our real target table - we can even do a little data cleanup and
validation, coerce data types, impose some formatting etc. along the way.
The phantom columns we ignore.

This can be reduced down to a series of macro steps (or a VBA routine), once
you are confident that your data is importing smoothly, you can knock the
human interaction down to a click of a button.

The benefit of doing this with VBA is you can test the data and use two
targets - one table is catching the stuff that doesn't meet your expected
criteria - I usually call that my review_[tablename] and the stuff that
passes whatever tests is directed to your normal target table. Once all of
the records are moved to one table or the other, then empty the table and
you can go to the next round. After the sheet is imported - bring up the
review_ table, look it over, fix what failed (where possible), and you can
copy those rows back to the middleman table and run the import process/query
on them.

Hope this gives you some ideas you can work with.

Gordon

 

[Sandy]

I checked the link and it shows ver. 9.0 for Office 2003. do you think it
will still work for Office 2007? I can see where this would be a life saver.
I tried changing a field name before and it was one big pain.