Error importing Excel into Access

[Guest]

I am getting an error message ("an error occurred. the file was not
imported") while importing an excel spreadsheet into Access. I am allowing
Access to create the table from scratch and make a primary key. There are no
duplicate field names, nor are there extra fields or data beyond my headers.
I am not being allowed to edit the type of fields created in the import
process so do not have any idea what I need to do to fix the spreadsheet to
make it importable. I am working in Office 2003. Thank you!

 

[Guest]

There is a high probability that one or more columns in the spreadsheet is
formatted as "General" and contains numeric values. That is okay as long as
every row in the column has a number in it; however, if whomever created the
spreadsheet left any rows in the column blank, this problem will occur (I
have first hand experience at this). What happens is that Access is trying
to determine what data type the field should be. It sees a lot of numbers,
so it makes it a numeric field (Long if I remember correctly). Then, it hits
the blank cells and sees them as Null. This causes the error. The simplest
solution is to open the spreadsheet and format the offending columns as a
number format. Access will then see the blank cells as 0 and all will be
forgiven.

 

[Guest]

Thanks, but that didn't work. I only have one field that has a number, so I
changed it from "general" to number in Excel but that didn't fix the problem.
I did fix a couple of field names so that now I can import a worksheet into
Access into its own new table, but not into an existing table with the exact
same field names. Any thoughts? The worksheet has about 100 fields, so I
painstakingly manually copied and pasted each field name into a new Access
table in design view, and was able to import one (1!) worksheet into it, but
no more. argh.

 

[Michael J. Strickland]

You should be able to edit the field types.

In the Import Wizard, at the last step, before you click "Finish",
click the "Advanced" button and change all the field types as required.

 

[Guest]

How are you doint the import?

Thanks, but that didn't work. I only have one field that has a number, so I
changed it from "general" to number in Excel but that didn't fix the problem.
I did fix a couple of field names so that now I can import a worksheet into
Access into its own new table, but not into an existing table with the exact
same field names. Any thoughts? The worksheet has about 100 fields, so I
painstakingly manually copied and pasted each field name into a new Access
table in design view, and was able to import one (1!) worksheet into it, but
no more. argh.