Problem with data type when importing from Excel

[Big Pat]

Hi,
I need to import 4 large spreadsheets to a single table, but I'm having trouble with one field. Study Recruit ID should be text because some values are alphanumeric, but when I use the Import Spreadsheet wizard, I can't change this. See attached. It could be because the first 30-40 records ARE numeric, but further down there are definitely some alpha values.

If I go ahead and accept the wizard's settings, then when the data is imported, those records display #NUM!

Help!

 

[gemma-the-husky]

i tend to get around this by adding an extra row 2 to the xls file, and in the offending column adding some explicit text in row 2, which forces the import as text. Then delete the row from the imported file

alternatively, save the xls as a csv, and import using transfertext, as you get more control that way.

 

[namliam]

Or sort the offending column descending, alpha fields then come on top of the sheet.

Offcourse this only works if you have 1 column

This is just one of the many reasons Excel should NOT be used for data transfers.

 

[Alansidman]

If you are using the import wizard, click on the advanced button on the lower left of the import window. You can then select the properties for the fields being imported. I find that this works every time and ensures that you are telling Access how to import the fields, rather than have Access try to guess. It also lets you name the fields if they don't have captioned headers in Excel. I also agree with Gemma on the .csv instead of an .xls file.

Alan

 

[David Eagar]

Another method is to format the column in excel as text, not leave it as general

 

[Dennisk]

Whenever I have problems like this transfering data from Excel to Access, I export the data from Excel to a comma delimited file, then import that.