Import problem with Excel spreadsheet

[JKnope]

I am importing an Excel spreadsheet with item data from our mainframe. The
warehouse field in this spreadsheet may contain numbers or alpha characters.
As a result, I set the field data type in my table as "text". When I import,
I continue to get 'type conversion failure' errors on any records that begin
with numbers, then contain text. An example is "00LE" is a value within the
spreadsheet. Rather than getting any data in my table, it doesn't import at
all. Numeric warehouse values are imported properly, any that contain text
are appearing in the errors table and do not import.

I've formatted the entire column in the Excel spreadsheet to be text,
imported, no change. Any advice would be greatly appreciated. I've worked
with Access for 10+ years, and I have never seen this problem before.

 

[mnature]

Try importing the excel file to a new table, rather than setting up a table
beforehand. Once you have the data, you can rename the table. This would
avoid any problems with data types.

 

[JKnope]

I did try that as well, but because the first several rows had a warehouse
value which was numeric, it assumed the field to be a "number" data type. As
a result, none of the rows with a text warehouse value are showing.

With no changes made - I let the wizard handle the import, and I receive
over 6,000 errors.

 

[John Nurick]

Some possible approaches, in no particular order:

-Export the data from Excel to a CSV file, then import that.

-Put a dummy row of data at the top of the Excel table, with values in
the text fields that cannot be construed as numbers.

-Prefix the values in Excel with apostophes, e.g. '0033 rather than
0033. This forces Excel and Access to treat them as text. The
apostrophes aren't displayed in the Excel worksheet and are stripped off
during the import process.

-Tweak the registry settings as described in
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

 

[mnature]

Set up a dummy first row which will "set" all the data types correctly. You
can always delete that record once everything imports.

 

[John Vinson]

I am importing an Excel spreadsheet with item data from our mainframe. The
warehouse field in this spreadsheet may contain numbers or alpha characters.
As a result, I set the field data type in my table as "text". When I import,
I continue to get 'type conversion failure' errors on any records that begin
with numbers, then contain text. An example is "00LE" is a value within the
spreadsheet. Rather than getting any data in my table, it doesn't import at
all. Numeric warehouse values are imported properly, any that contain text
are appearing in the errors table and do not import.

I've formatted the entire column in the Excel spreadsheet to be text,
imported, no change. Any advice would be greatly appreciated. I've worked
with Access for 10+ years, and I have never seen this problem before.

Since formats in Excel are advisory not enforced, Access must guess at
the appropriate datatype based on the first few rows of the imported
data. As you have seen, it's often going to guess wrong!

Two suggestions: *link* to the Excel spreadsheet rather than
importing, and run an Append query into your table. If that doesn't
help, put a "dummy" row at the top of the spreadsheet with an
unambiguous text value ( "TEXT" say) in the column which you wish to
import into a text field.

John W. Vinson[MVP]

 

[George Nicholson]

Insert a dummy record in Excel, somewhere within the 1st 8 records. Fill all
columns of this record with a text value (ex: "DeleteMe"). That should force
the Access import wizard to treat every column as a text field and allow the
import to proceed without interruption.

Then delete that record after the import. (Not particularly elegant, I
know.)

Another possibility to consider would be to save the Excel file as comma
delimited and then read that text file into Access. Importing text files
allows much greater control over field types etc. than the assumptions that
are built into spreadsheet imports. Importing text files also allows you to
have sets of saved Import specifications.

HTH,