Question

[Ann Scharpf]

-- I am trying to import data from an Excel spreadsheet into my Access 2003
database. I am sure I did this successfully a few weeks ago (with my last
Excel data dump) but today it is not working and I can't figure out what is
different.

If I import the data to a NEW table, the import works fine. However, if I
try to import to an existing table, I get the following error message:

An error occurred trying to import file 'C:\Documents and
Settings\ANN_SCHARPF\My Documents\Businesss Office Stuff\CSC Cost Report
Recon\Source Data Modified for database\Cost Report CDRL (3-17-08).xls'.
The file was not imported.

I have confirmed that ALL the column headings in the Excel file exactly
match the field names in the Access table AND that they appear in the same
order. I have selected and deleted all extraneous columns and rows to the
right of and below the data in the Excel worksheet.

I do have a multiple field key established in the Access table. Last time I
did this import, the key caused records to be skipped - which was what I
wanted. I have a lot of TBD rows in the data dump and I only got one record
with all the extra TBDs skipped.

I can't think of anything else to tell you. Can anyone point me in the
direction to investigate why this import won't work anymore?

Thanks in advance for any help you can give me.

Ann Scharpf

 

[Jeanette Cunningham]

Ann,
a frequent cause of this error is a mismatch between number data types and
text data types.

The import process includes a scan of each column in excel right at the
start of the import process.
If you have the most common registry settings for access, the scan looks at
the first 8 to 25 rows of each column .
If it finds any text data type in the rows it scans, it will decide that all
the data in that column is of text data type.
If you are trying to import this particular column of data into a field of
type number in your existing table, access will not allow you to import text
into that field.
You will get the error message that you posted.

Have a look at that import that worked using a new table. Examine the field
types of any fields that should be number data type in your existing table
to see if any are text data type in the new table. That is your clue to
which field is causing the failure of the import process.

Back in the excel spreadsheet, for that particular column, make sure that
the first 8 to 25 rows are all number data type, so that the scan process
will decide that this particular column is number data type.
Further check that all entries in this column are number data type, if there
is even one row that is text data type, it can not go into the number field
in your existing table.

Jeanette Cunningham

 

[Ann Scharpf]

Hi, Jeanette:

Thanks for responding to my question. After reading your message, I did a
comparison of the data in the Excel sheet and Access data table.

The first six fields in the Access table are defined as Text, 255
characters, no data compression. The first six columns of the Excel sheet
are entirely alphanumeric.

The next 26 fields in Access are numeric and are defined as Double with Auto
for the decimal places. I confirmed that there is NO alpha only data
anywhere in those columns in Excel – aside from the column headings.

So I don't think I have the type of data mismatch you describe. Could I be
missing something?

Thanks again.

 

[Jeanette Cunningham]

Ann,
the most common cause of the failure to import is trying to import data into
numeric fields into access from excel.
It is most likely that the data in the columns in excel after the first 6
columns are a problem for the import.
These are the columns that correspond to the access numeric fields.

Your reply stated:

I confirmed that there is NO alpha only data
anywhere in those columns in Excel - aside from the column headings.

Double check that you specify the first row of excel contains column
headings when you do the import and the import still fails.
If you want to troubleshoot this further, make several copies of the table
you are trying to import into and try the imports into the copies.

Do a comparison import where you only import the first 6 columns from excel.
Do a second import where you only import the columns for the numeric fields
in access.
Are there any empty cells anywhere in the columns that match the numeric
fields in access?
For example if there are any empty cells in the first 8 to 25 rows, the
import process may decide that columns with empty cells are of text type
even if further down the spreadsheet the data is all numeric.

Have you tried importing using TransferSpreadsheet? You can use the range
argument and tell the import process which cells to import.

Jeanette Cunningham