Importing data from Excel

[Guest]

I'm attempting to import data from an Excel spread sheet into an Access 2002
database.

I have no problem importing the data to a new table, but when I want to
append additional data from another spread sheet that has identical columns
and headers, and data types, I get the error: "An error occurred trying to
import file (file path and name). The file was not imported."

I made sure that all columns and rows contained the same kind of data, the
headers were identical and so forth. I have tried several iterations of
importing. The option I need to use is to store the data "In an Existing
Table:" which I pick from the drop down.

Nevertheless, it continues to present me with the same error message each
time.

I know it must be possible, because it offers the option to import the data
to an existing table. I will need to import spreadsheet data each week to
continue to build on the existing table. There will be differing number of
rows each week.

Any ideas why the error?

Thanks for your help.

 

[Guest]

Hi.

I have no problem importing the data to a new table, but when I want to
append additional data from another spread sheet that has identical columns
and headers, and data types, I get the error: "An error occurred trying to
import file (file path and name). The file was not imported."

Is your spreadsheet missing the name of a column in the column headers?
Does the spreadsheet have more than one column name that's identical to
another? One reason the import can fail on the second try with the same
spreadsheet is due to a mismatch of the column names, and either of these
conditions will cause a mismatch from the source to the target, because Jet
has to create a new column name in the target table to replace any invalid
column name. Access is doing the import with an equivalent to the “INSERT
INTO TableName…†SQL statement. For this SQL statement to execute
successfully, the column names must match and be in the same order in both
the source and the target, and the data types of these columns must be the
same or convertible from the source data type into the target data type.

Access automatically creates a column name for an invalid or missing column
name in the column header by using the variable name “Field†and
concatenating the ordinal number of the column. For example, if the
spreadsheet is missing a column name and the first import creates a column
named “Field4†in the new table, then the spreadsheet doesn’t contain this
column name in the headers, thereby creating a mismatch. You may notice that
during the second attempt to import the spreadsheet, the Import Wizard will
automatically name the blank column in the spreadsheet as “Field4.†“Field4â€
is what the column name will be after a successful import, not what the
column in the spreadsheet is _currently_ named, so this is an important
distinction.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.