Feb 14 2012, 06:31 PM
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
Feb 14 2012, 07:35 PM
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...")
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
Feb 15 2012, 04:31 PM
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)
Feb 15 2012, 04:35 PM
Don't import the sheet, Link to it and then run an APPEND query that converts the data.
Feb 17 2012, 12:51 AM
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