TransferSpreadsheet acImport Type Conversion Failure

[Guest]

When I do
DoCmd.TransferSpreadsheet acImport, 8, "TableName", "FileName.xls", True, ""
(or click File\Get External Data .. etc)
I get Type Conversion Failures with the following data:
Row HouseNumber
1 81
2 165
3 52
:
84 1a
It objects to row 84. The destination table TableName has a Text(5) type for
field HouseNumber. Changing the Excel cell format from General to Text has no
effect.
...The import tries to be clever and assumes that because the first x rows
are numeric, it must do a type conversion for the whole column.. Except row
84 is text & fails a num->txt convert.

Is there a way 'round this?.. I've ~300 files each with ~3000 rows.
TIA
SimonC

 

[Guest]

When I do
DoCmd.TransferSpreadsheet acImport, 8, "TableName", "FileName.xls", True, ""
(or click File\Get External Data .. etc)
I get Type Conversion Failures with the following data:
Row HouseNumber
1 81
2 165
3 52
:
84 1a
It objects to row 84. The destination table TableName has a Text(5) type for
field HouseNumber. Changing the Excel cell format from General to Text has no
effect.
..The import tries to be clever and assumes that because the first x rows
are numeric, it must do a type conversion for the whole column.. Except row
84 is text & fails a num->txt convert.

Is there a way 'round this?.. I've ~300 files each with ~3000 rows.
TIA
SimonC

 

[Guest]

In the first data row of the spreadsheet, insert a "dummy" data row, with
each column having the data type that you want. Do this especially for
columns that you want to force to text type. Use a unique value for the
row to enable deletion later.

Or copy the field names row as the first data row and all data will
transfer as text. Import into an existing Access table and the data type
will convert successfully or not with errors posted when it doesn't.

 

[Guest]

Thanks, I was aware of this fudge... I should have said. Just thought there
might be some way of avoiding opening and editing 300 files... Maybe will try
SQL server next.. Might not try to second guess so much!
Cheers,
Simon.

 

[Jamie Collins]

might be some way of avoiding opening and editing 300 files... Maybe will try
SQL server next.. Might not try to second guess so much!

No such luck <g>. The usual assumption here is that MS Access is doing
the guessing of data types. Wrong. What is actually happening is that
Excel is scanned in a separate process. Think of it as being scanned
by Jet on the Excel side and being a done deal before it leaves Excel.

If you want to influence the process, you will have do take action in
Excel (data values and column formats) or in the registry. See:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Jamie.

--