trouble importing from Excel

[Guest]

I have successfully imported one spreadsheet (complete with headers/field
names) into Access, and now want to do other spreadsheets with the exact same
field names into the same Access table, but consistently receive an error
message (with no explanation). I have even tried importing the exact same
spreadsheet into the exact same table again (because I know for certain the
field names match exactly) but with no success. I also tried without
specifying that the first row is NOT column headings, just to see if that
would work, but then it does not give me the option of importing into an
existing table. Thanks for the help.

 

[John Nurick]

Hi Allyson,

More information needed, including at least:

-which of the various possible techniques you are using to import the
data

-the exact error message you getting

-what field(s) are in the primary key of the Access table

-whether an XXXX_ImportErrors table is being created (XXXX being the
name of your table) and what clues it contains.

 

[Guest]

Sorry for the delay in responding - I've not been at work, so I hope it's not
too late! To answer your questions:
1) I am using the File --> Get external data technique to import the excel
sheet.
2) the error message I get is "an error occurred trying to import the file
c:/my documents/abc.xls'. The file was not imported." When I tried before I
at least got an error table which was incomprehensible to me but now I don't
even get that.
3) I have no primary field in the Excel spreadsheet, but had Access create
one when I first imported (which worked just fine; it's subsequent imports
that are a problem, even if it is the exact same data from the exact same
worksheet). I have tried creating a primary field in the Excel spreadsheet
just to conform to the Access table exactly but that didn't help;
4) see above about import error tables. Access used to give me one giving me
a "Type Conversion Failure" error in almost every field (with no rhyme or
reason to which field that I could discern) but now I don't get them at all,
although I've tried many times today to generate one.

I have also tried importing without doing any changes to the initial table
created by Access, as well as formatting all the fields to better match the
cell formatting I did in Excel, but to no avail. Suggestions are most
welcome, as I am at the end of my rope.

Thanks! Allyson

 

[John Nurick]

This problem can have some quite subtle causes. The artist normally
known as '69 Camaro posted the following in response to a similar
question here on 15 April:

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.

If that doesn't cover it, try searching http://groups.google.com for
something like this:
"error occurred trying to import the file" Access Excel
and you'll find some other messages that may help.