TransferSpreadsheet Type Conversion Failure

[jjgray]

When transfering .xls into Access table I get a Type Conversion Failure
error. Example: I have column in Excel with numeric and text values in a
column. If the top row of the imported Excel is numeric and several rows
down is Text in the same column the error occurs. If the top row is Text and
everything below it is numeric the error does not occur. Any assistance
would be appreciated.

 

[Yanick]

It seems Access will automatically assign the type by looking at the first
value of the field. I often got this problem to. Here is how I work around
it.

Save your Excel file into CSV file (sort of text file) and import from that.
In the Import Table Wizard click on the Advance button. You will be able to
set the type of each field your self. Then you can save your setting for this
file and use it every time you will need to import your file again.

Hope it will help.

Yanick

 

[Len Robichaud]

This is a problem as Access thinks it knows the Excel data is going to be
numeric based on the first row's data. I only know of two ways around this:
the best way is to export the Excel data first into a .txt file (using Save
As) and then importing the .txt file... the next best is to insert a False
row of data in Excel as the first row (for example zzz, zzz, zzz, etc.) so
Access does not see numeric data as the first record (don't forget to delete
the false data after the transfer).

Len

 

[jjgray]

Thank you. Will the Macro repeat the error every time it runs under the same
conditions AFTER the first import?
Thanks again!

 

[Yanick]

There is no macro involved in this. It is just a save of your import setting
so you can just select it the next time you want to import the same file
again without having to set the type of your field again.

Nothing more.

Yanick