TransferSpreadsheet acImport Type Conversion Failure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SimonC - 23 Nov 2004 16:37 GMT

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

Reply to this Message

Folbaj - 26 Nov 2004 16:29 GMT

> When I do
> DoCmd.TransferSpreadsheet acImport, 8, "TableName", "FileName.xls", True, ""
[quoted text clipped - 16 lines]
> TIA
> SimonC

Reply to this Message

Folbaj - 26 Nov 2004 16:33 GMT

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.

> When I do
> DoCmd.TransferSpreadsheet acImport, 8, "TableName", "FileName.xls", True, ""
[quoted text clipped - 16 lines]
> TIA
> SimonC

Reply to this Message

SimonC - 29 Nov 2004 11:03 GMT

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.

> 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
[quoted text clipped - 25 lines]
> > TIA
> > SimonC

Reply to this Message

Jamie Collins - 29 Nov 2004 16:31 GMT

> 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.

--

Reply to this Message