Import type conversion issue

[evergrean100]

I am trying to import an Excel 2003 sheet into my Access 2003 database.

I get error message saying sheet could not be imported and it referred me to an Import error table which showed type conversion error for one of the columns. Please advise what that means and how I can make it work?

 

[maxhugen]

Are you using TransferSpreadsheet in code, or the Wizard?

If you don't specify somehow what the field types should be, Access looks at the first x records and makes a guess - this can of course be incorrect.

To provide the correct data types, you can:
1. import into a table you've already set up.
2. Use the Import Wizard, and specify the field types there.
3. Create and save an Import Spec using the Import Wizard, and then specify it in the TransferSpreadsheet method.

Max Hugen
Australia

 

[evergrean100]

This is using a Wizard and I understand the first two recommendations but not the last one:
3. Create and save an Import Spec using the Import Wizard, and then specify it in the TransferSpreadsheet method.

Can you give more info on how I do that part?

Thanks

 

[maxhugen]

:-( My apologies, I've checked and you can only create and use an Import Spec when importing/exporting text files. The TransferText method allows a saved spec to be used, but not TransferSpreadsheet. Odd!

If you want to import using code (TransferSpreadsheet), I'd suggest you do (1) above. Import it into a temp table if you need to 'massage' the data before using it.

Max Hugen
Australia

 

[Ed2020]

Max,

Things may be different in Access 2003 but when I came across this problem in Access 2000 importing into a predefined table didn't solve the problem - Access still insisted on estimating the necessary data types on the first x number of rows and gave import errors for records further down the file.

From what I remember there are two solutions:

1. Create code to automate the conversion of the Excel sheet into a CSV file and then import the new file (I can provide code for this, if wanted/needed).

2. Change Access's behaviour via a registry hack. If I remember correctly Remou or TheAceMan1 posted details of how to di this last time it was discussed here - hopefully if one of them reads this they'll post the solution again.

Ed Metcalfe.

Please do not feed the trolls.....