Type Conversion Error

[austin1539]

I am having a problem importing an Excel spreadsheet into Access.
I have an existing table with all fields as Text.
One of the columns in the spreadsheet is mostly numeric, but occassionaly has an alpha character. Access was returning a Type Conversion Failure any time there was an alpha in that column (even though I forced Text format on the spreadsheet).

I finally got it to import those fields by inserting a dummy row at the top of the spreadsheet of all alpha characters to prevent Access from guessing a numeric format. However, now every entry that did not have any alphas is importing into Access as a number in Scientific format (ex: 4.27395e+006) and I cannot change it back.

Any suggestions on how to get the numbers back to a standard format (ex: 4273947) or a different way to make Access accept an alpha when most entries are numeric?

Thanks

 

[Fifty2One]

Did you try formatting the entire column in the source Excel file as TEXT

 

[austin1539]

Yes. Both the spreadsheet and Access field are formatted as Text.

 

[gemma-the-husky]

i had that the other day with a csv file

it looked as if the entries were text (they had leading zeroes that i WANTED as text) , the access wizard said they were text, but access still loaded them as numbers.

save the file spec, and examine it - when i looked the file spec had the fields stored as numbers not text- i changed it to text in the spec file, and it went away

unfortunately, you cant use a file spec directly with an excel file, but by exporting to a csv first you might get around it

 

[austin1539]

Thanks for the help. I actually found that rather than adding a dummy row into the spreadsheet, I could add an alpha to the end of every value in the column and then remove it with an update statement once it was in the table. This preserved the correct formatting and I didn't have to export it again.

 

[neileg]

Formatting in Excel doesn't change the data. If you need to repeat this saving the import spec is the way to go.

 

[Dennisk]

I had this problem recently and to get around it I exported the data from excel to .CSV format then imported that file. Worked fine.