Data import from Excel causing type conversion error

[Isaac]

Last couple years of minimal Access use must have me really rusty because I am doing something pretty basic and still confused.

I have a table with a column where the column datatype is Short Text with length 255 and format @
Using TransferSpreadsheet:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "SnapshotTable_TempImport", strWorkbookPath_new, True, "a1:fc" & lastrow

Everything works great, the spreadsheet headers are all perfectly matched to the identical table column names.
Except a few records end up in the table: a1:fc59_ImportErrors

I've identified the anomaly on the spreadsheet ... in the problem column, 90% of the records are numbers, like 2.5 or 7. Then occasionally on the 'problem' records, the value is two consecutive dashes, like --

So sure, I get why Access thought they were numbers and then got surprised, but why does this matter? The destination column is short text!
Please don't tell me I have to resort to row-by-row recordset adds or something...I already tested it in my scenario, terribly slow.

The only other hacky thing I can think of is to add more code to loop through any Import Errors table and try to figure out how to update them manually, but hopefully there is some straightforward solution that I probably knew once upon a time but cannot remember now.
Is the only way out of this to link to the Excel file and do an append query?

 

[June7]

I agree, seems should be irrelevant what data is in the Excel column when Access field is set for text but try formatting the column in Excel to treat all values as text even if numeric.

 

[Isaac]

Of course! Why didn't I think of that. Just changing the existing Excel cells to Text didn't solve it (just tried), BUT, I think you're right - if I format the sheet as text FIRST, and then paste values into it, I think it will work. Off to get that done and will post back w/results - thanks!

 

[The_Doc_Man]

The general rule of thumb with imports like this is that the import wizard looks at the first 20 or so lines of the sheet in order to decide what kind of format to apply to the table as a whole - if you are importing to a new table. But if you are importing to an existing table, I thought it would try to honor the destination data type.

 

[Isaac]

I agree, seems should be irrelevant what data is in the Excel column when Access field is set for text but try formatting the column in Excel to treat all values as text even if numeric.

Alright, I added some code to manipulate the Excel situation to first format all columns (except one date column) as Text, then paste values from one sheet into that destination sheet, and then use TransferSpreadsheet on the newly created sheet - works great! Thanks again.

Yeah, that seems like a bit of a bad design. Like if I were using another ETL tool like SSIS, if the destination column is a particular datatype, all the tool will require is that incoming data CAN fit in it, no matter what it is, but M.S. seems not to have given Access that particular grace.

Glad it is solved thanks for weighing in.

 

[June7]

I tested importing from Excel sheet where a column has nothing but numeric data and it is set to be treated as text. Imports without issue. I also tested with that column set as General number and still imports to text field without issue.

 

[Isaac]

My scenario was:
- excel column was formatted General
- access table column was Short Text
- most values in excel column were numbers
- values on a few rows were --
As TDM mentioned I think it's a matter of mixed data in Excel, and how Access reacts to that.
(I am sure the person who programmed it honestly believed it would be quite helpful at the time but underestimated how it would also be a barrier and overestimated its helpfulness). hindsight 20 20

 

[June7]

Ahha, that "--" record errors when column is left as General but changing it back to Text worked. No having to copy to another sheet.

 

[Pat Hartman]

I tested importing from Excel sheet where a column has nothing but numeric data and it is set to be treated as text. Imports without issue. I also tested with that column set as General number and still imports to text field without issue.

Apparently Access does not use the target data type when appending from a linked spreadsheet.

If you append a spreadsheet, you get a dialog that lets you step through the columns and specify a data type but if you link the spreadsheet, Access and Excel collude to determine the data type of each column. They use the first n rows (may be 20, may be more) . If the data type of the column is General (which is the Excel default), then if all the data in those rows is consistent, it will assume a data type such as double or date or text. If the data type of the column has been specifically formatted as numeric or Date, it will assume that data type and log any deviation as an import error. If the data type of the column in the spreadsheet has been specifically formatted as Text, it will assume text.

So, when my apps take data input, I try to get the user to send .csv because that allows me to create an import spec and I can anticipate issues and define columns as text when I know the values will be mixed. If the users want a spreadsheet, I give them a template that I format with the correct headers and the columns formatted as I want them to be formatted. As long as they use the template, things go smoothly. If they don't, I have to handle the errors smoothly which is not a simple task.