Trying to Import Excel data

[bconner]

Hi All,
I am trying to import data from Excel 2010 into Access 2010. I am having a problem with one column not importing when there is a space in the value like A245609 345. I have the field set as datatype TEXT in access because there are both alpha and numeric and spaces. Access won't import any of the records where there is a space in the column. Any help would be appreciated.

 

[CJ_London]

if the column has only numeric values in the first few rows? If so, the importer will assume the column is numeric and not import the text items.

There are two solutions.

1. sort your excel data so there is alpha type values in the top few rows
2. In the data importer, review the column being imported for type - it is probably saying long or single so change to text

 

[bconner]

Hi CJ London,
That worked when I first imported the file however when I clear the table and try to import into the existing table the data with the spaces in it like A245609 345 still fails as type conversion error.

 

[GinaWhipp]

How are you importing the data via the menu with or without a specification or are you using code?

 

[bconner]

GinaWhipp, both times I used the excel icon under external data on the top menu..

 

[CJ_London]

both times I used the excel icon under external data on the top menu..

and the second time did you use the same import specification (i.e. convert the relevent column to text?) as the first time or create a new one?

 

[bconner]

On the second import because I'am importing into an existing table it doesn't give me the option to Change the specs

 

[CJ_London]

because I'am importing into an existing table it doesn't give me the option to Change the specs

doh! of course.:banghead:

However I've just replicated what you are trying to do and all data was imported without a problem. See second item down. I imported a .xls worksheet into Access 2010. The below is the second import.

Only thing I can suggest is to delete the table, compact the db and try again.

 

[bconner]

Ok Thank you I will try that....