Problem with data type when importing from Excel (1 Viewer)

B

Big Pat

Registered User.

Local time

Today, 23:37

Joined

Sep 29, 2004

Messages

555

• Jan 5, 2009

• #1

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!

 

Attachments

• Screenshot.pdf

  14.1 KB · Views: 164

gemma-the-husky

Super Moderator

Staff member

Local time

Today, 23:37

Joined

Sep 12, 2006

Messages

15,652

• Jan 5, 2009

• #2

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.

 

N

namliam

The Mailman - AWF VIP

Local time

Tomorrow, 00:37

Joined

Aug 11, 2003

Messages

11,695

• Jan 5, 2009

• #3

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

AWF VIP

Local time

Today, 17:37

Joined

Jul 31, 2008

Messages

1,493

• Jan 5, 2009

• #4

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

 

D

David Eagar

Registered User.

Local time

Tomorrow, 08:37

Joined

Jul 2, 2007

Messages

924

• Jan 5, 2009

• #5

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

 

Dennisk

AWF VIP

Local time

Today, 23:37

Joined

Jul 22, 2004

Messages

1,649

• Jan 6, 2009

• #6

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.