General problems importing Excel into Access

[Guest]

Hello, peeps! Does anyone else bump into these when importing Excel into Access

* If the first few dozen or so rows have numeric data, Access imports it as numeric, even if importing to a Text field, and instructed to import data as Text, not General. Ex: zip codes in format "00000-0000" need to be stored as text, but if the first 50 rows are all "00000" format, Access tries to munch them as numbers and dies when it hits the first dash

* Access won't parse "null" in a numeric field. If a numeric field is storing the text string "null" Access really ought to interperet this as zero

* Access doesn't understand that "$ -" is zero. Excel uses a dash for a zero value when using "accounting" format; if Access can parse away the dollar sign, and interperet paranthesis as indicating a negative value, it should understand that "$ - " is zero, especially when its coming from Excel

A few thoughts, maybe I'm doing something wrong. This is with XP and XP. Thanks!

 

[John Nurick]

Hi JS,

Excel doesn't have a concept of field types, only of cell formats, and
Access mostly ignores the cell formats.

For columns with a mix of text and numeric data, you need to ensure that
there's at least one value in the first dozen or so rows that can only
be intepreted as text. One way to achieve this (especially with things
like zip codes that look like numbers but are in fact just strings of
digits) is to prepend an apostrophe to each value in the worksheet. This
forces Excel and Access to treat the number as text, but is not
displayed on the worksheet, returned in the cell's .Value and .Formula
properties or imported to Access. The Excel macros at the end of this
message will add or remove apostrophes.

I don't agree that Access should treat the word "null" as magical.
Remember, Excel doesn't have "numeric fields". (But there's nothing to
stop you writing a custom import routine that uses Automation to process
individual cell values any way you like.) Access does however import an
empty cell as a Null value.

Similarly, Access doesn't (and IMO shouldn't) assume that the string "$
-" signifies the number zero. If I format a column of cells each of
which contains a number as "Accounting", Access imports them correctly,
zeros and all. Are you sure that your sheet contains zeros and not text
values in these fields? Most Excel users are quite dumb enough to type
the three characters
$ -
instead of formatting the cell correctly and typing 0.

 

[Graham Mandeno]

Hello JS

The engine for linking/importing Excel into Access is very inflexible and
makes a lot of assumptions. Often I find it is necessary to save the Excel
sheet as a CSV file and import it into Access as text. The ability to
specify a text import specification in Access is very powerful and flexible,
and it does the job *most* of the time

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello, peeps! Does anyone else bump into these when importing Excel into Access?

* If the first few dozen or so rows have numeric data, Access imports it

as numeric, even if importing to a Text field, and instructed to import data
as Text, not General. Ex: zip codes in format "00000-0000" need to be
stored as text, but if the first 50 rows are all "00000" format, Access
tries to munch them as numbers and dies when it hits the first dash.

* Access won't parse "null" in a numeric field. If a numeric field is

storing the text string "null" Access really ought to interperet this as
zero.

* Access doesn't understand that "$ -" is zero. Excel uses a dash for a

zero value when using "accounting" format; if Access can parse away the
dollar sign, and interperet paranthesis as indicating a negative value, it
should understand that "$ - " is zero, especially when its coming from
Excel.

A few thoughts, maybe I'm doing something wrong. This is with XP and

XP. Thanks!