trouble with excel import

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TimR - 23 Feb 2007 03:10 GMT

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

Reply to this Message

John Nurick - 23 Feb 2007 07:02 GMT

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.

>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
[quoted text clipped - 21 lines]
>
>Thanks, Tim R

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Reply to this Message

Jeff Boyce - 24 Feb 2007 00:40 GMT

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

> Very new to Access...I am trying to import a 4000 row/record Excel file
> into
[quoted text clipped - 25 lines]
>
> Thanks, Tim R

Reply to this Message

TimR - 28 Feb 2007 02:14 GMT

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

> Tim
>
[quoted text clipped - 36 lines]
> >
> > Thanks, Tim R

Reply to this Message

John Nurick - 28 Feb 2007 06:31 GMT

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.

>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
[quoted text clipped - 46 lines]
>> >
>> > Thanks, Tim R

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Reply to this Message