Full Version: Importing Spreadsheet Errors
UtterAccess Forums > Microsoft® Access > Access Automation
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...")
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)
Don't import the sheet, Link to it and then run an APPEND query that converts the data.
hi bbarbarito (what is your name?)
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 UA. To view the full version with more information, formatting and images, please click here.