Type conversion error on excel to access import

[ch4meleon]

I have an excel spreadsheet on which worksheet 2 looks up values in worksheet 1
-worksheet 1 has a specific layout not very friendly that must stay the same worksheet 2 is just the info going accross the sheet to be easy for access to import.
i am then trying to import via file - get external data- import into an existing table in the database
however i get type conversion errors. all my excel file is formatted to be the same as the access table ?
I tried file-get external data- import to new table which worked fine but when i looked at the design view of the newly imported table all my number fields where showing as memo so it is presumably those that are causing the problem i have checked and excel format cells thinks they are numbers??
Please has anyone any ideas how i can resolve?

 

[SkipVought]

Hi,

Is it possible that some of the rows in this column are NUMBERS and some are STRINGS? To verify which cells are NUMBERS and which are STRING, change the NUMBER FORMAT of the column and observe which rows do NOT change format.

Could you post some examples of SOURCE data that is resulting in MEMO?

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[Ed2020]

SkipVought is almost certainly correct in his diagnosis. This is a problem I have encountered before.

Importing from Excel to Access can be a real pain when you cannot guarantee that all data in a column is of one data type.

MS Access reads the first few rows (not sure how many) to determine the data type for that column. If the data type changes further down the sheet Access doesn't recognise this, resulting in the error you describe.

I've never found an easy way around this. Importing into a predefined Access table (even if all the fields are set as Text) doesn't work. Import specs don't fix it either. Nor does linking the sheet rather than importing (Access still determines data types in the same manner).

The only way around it I found was to create an import that routine that uses Automation to open the Excel file, convert it to CSV, save the CSV file and then import that to a temporary table where all fields are text. Once you have validated the data in the temporary table this can then be appended into its final destination table, with the correct data types.

Long-winded, I know! If anyone can come up with a cleaner solution I'd be very interested to see it.

Ed Metcalfe.

Please do not feed the trolls.....