Importing from Excel 2000 to Access 2000 - data changed

[Wayne H via AccessMonster.com]

I have a spreadsheet that is being pulled out of a db with Crystal Reports
and is then converted to Excel. On one of the columns [ID Number] there is
either a 6 digit number or the word NULL. When I import this this spreadsheet
into Access, it gives me an error saying some of the data could not be
imported. Then I open the table, and every entry in the [ID Number] column
that was NULL, is now blank.

There are other columns that have entries as numbers as well as NULL, and
they import fine.

Also, there is a true/false column. After it is imported, it is displayed as
either 0 or -1. How can I change this to display as true/false.

Any ideas would be greatly appreciated.

Wayne

 

[George Nicholson]

When Access imports data *into a new table* it looks at the first x records
(8 rows? not sure) and makes a guess as to the type of data it contains and
creates the appropriate field type, then does the import. If "Null" appears
in first x records of the incoming data, it will probably create a Text
field. If "Null" does not appear in the first x records, only numbers or
blanks, it would probably create a Number field. If "Null" appears later in
that Number field, Access will leave that field blank since it can't put the
word "Null" in a number field, and advise you of the import error.

To Access, "Null" in a field = a string of text characters. An empty field
"is" Null (but an empty field is not equal to Null. Nothing ever equals
Null, it is an invalid comparison that always returns False. Something
"IsNull" or isn't.)

There are other columns that have entries as numbers as well as NULL, and
they import fine.

And in table design those fields are defined as Text, not Number, aren't
they? While Access defined [ID Number] as a Number field, didn't it?


0 = False. Anything else (including -1) = True. This is usually just a
display/formatting issue.

You might want to consider creating a table in Access (where all fields with
"Null" are defined as Text) and import your data into that rather than
importing directly into a new table. This gives you more control over what
you end up with.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I have a spreadsheet that is being pulled out of a db with Crystal Reports
and is then converted to Excel. On one of the columns [ID Number] there is
either a 6 digit number or the word NULL. When I import this this
spreadsheet
into Access, it gives me an error saying some of the data could not be
imported. Then I open the table, and every entry in the [ID Number] column
that was NULL, is now blank.

There are other columns that have entries as numbers as well as NULL, and
they import fine.

Also, there is a true/false column. After it is imported, it is displayed
as
either 0 or -1. How can I change this to display as true/false.

Any ideas would be greatly appreciated.

Wayne