Full Version: Importing Spreadsheet Errors
UtterAccess Discussion Forums > Microsoft® Access > Access Automation
bbarbarito
I am importing an excel spreadsheet (using DoCmd.TransferSpreadsheet acImport) into an access data base table. I am getting a type conversion import error on a field.

The field is defined as a text field. If the first record of the file has alpha characters in the field, I don't get any errors; however, if the first record of the file has only numeric characters in the field, I get the error on records that contain alpha characters in that field

Any help would be greatly appreciated
Thank you
Jeff B.
If you ask Access to import a spreadsheet, it looks at the data type in the first ("n") columns and decides what the data type is for the field.

If you don't want Access to decide, create a table that has the data types you KNOW are correct, then import the data from the spreadsheet into THAT table.

(that should have been "...in the first ("n") rows...")
bbarbarito
HI Jeff, Thank you for your time

The excel spreadsheet is being imported into a table (using append) that is already defined and in some circumstance already has data in it. I have attached a snippet of the table's design view

Thank you
Jeff B.
Here's another approach ...

If the data MUST be text, even if digits, then:
1) set up your table so that field is text (sounds like you've already done this)
2) link to or import your "raw" data
3) build a query that coerces that problem field into text (see CStr() function in Access HELP)
4) use that query to append to your permanent, well-normalized table (see #1)
ScottGem
QUOTE (bbarbarito @ Feb 15 2012, 12:45 PM) *
HI Jeff, Thank you for your time

The excel spreadsheet is being imported into a table (using append) that is already defined and in some circumstance already has data in it. I have attached a snippet of the table's design view

Thank you


Don't import the sheet, Link to it and then run an APPEND query that converts the data.
strive4peace
hi bbarbarito (what is your name?)

I often make a dummy row of data in Excel after the column headers. For each text cell, write "dummy". for each date cell, push ctrl ; and for each number, enter 0

then delete it or filter it out
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.