trouble with excel import

  • Thread starter Thread starter TimR
  • Start date Start date
T

TimR

Very new to Access...I am trying to import a 4000 row/record Excel file into
Access via the get external data import method. I can get the sheet into
Access...but I am getting errors in the first column from/to Excel/Access.

This column has 8digit numbers mostly...but some have an alpha numeric ( a,
b, etc) at the end of the number. (about 20% of the total. ) When I import I
get errors reported that correspond to the Excel rows that have the alpha
numeric character in the cell/row. The resulting Access field is blank..but
all the numeric only rows have been imported

Is there something I can do to get the numbers with the alpha character
imported along with those rows without the alpha character ?

This is how my Excel column looks:

19124187
19124188
19124189A
19124190B
19124194
19124197
19124199D
and so on

Thanks, Tim R
 
Hi Tim,

Try adding a dummy row at the beginning of the Excel sheet. In the first
cell of this row, put something that cannot possibly be a number (e.g.
"AAAAA"). In the remaining cells, put alphabetic or numeric values as
appropriate.

This will usually allow the data to be imported cleanly. Afterwards,
delete the dummy row.
 
Tim

In addition to John's suggestion, you could create a table in Access that
has the correct field types, then import into THAT table, rather than
letting Access create whatever fields it thinks it will need.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks Jeff & John...with regards to an 8 digit number that sometimes has a
letter at the end ( 9 characters) what is the best type of dell format in
Excel to eliminate the issue ?

Tim
 
There isn't a cell format that will help. You can eliminate the issue,
by prefixing the value in each Excel cell (or at least the numeric ones)
with an apostrophe:
007 becomes '007
so Access/Jet can't interpret it as a number.

Or you could store the 8-digit numbers and the optional letters in
separate columns in Excel, thus avoiding the question of mixed data
types.

For more information, see
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819

Finally, Access 2007 allows control of data types as you import from
Excel.
 
Back
Top