Zip Code import from Excel

[Guest]

Hi, I'm importing zipcodes with leading zeros from excel.
Access is reading them as number and dropping the first 0. How do I keep
all digits including the first 0?

Thanks

 

[Guest]

Go to your table that has the zipcode field, and enter six zeroes (000000) as
the format for that field. That should force a number field to require six
numerics, which will allow your leading zeros.

 

[Jeff Boyce]

Your answer is in your question. "Access is reading them as number".

Zip codes are ... codes, not numbers. You don't
add/subtract/multiply/divide zip codes.

Rather than importing and letting Access specify "number" as the data type,
you could define your own import table (and fields), and specify the zip
code field as Text.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Thx but this doesn't seem to work. I've right clicked on the excel cell,
formated as custom and entered 6 zeros or even 5 and it looks right on excel.
(ie. 01234). However as soon as I import into Access, the field is formated
as number and it looks like 1234 again. Help. Any specific ideas here?

 

[John Vinson]

Hi, I'm importing zipcodes with leading zeros from excel.
Access is reading them as number and dropping the first 0. How do I keep
all digits including the first 0?

Thanks

Access is interpreting the field as numeric, since Excel does not have
strong datatypes. It's just looking at the first few rows, finding
nothing but numbers, and creating an Integer field.

Instead, create the table in the table designer (or modify your
existing imported table); specify that the Zip field is of Text type,
and import to that table rather than letting Access guess.

John W. Vinson[MVP]

 

[Guest]

I think we are close. I have created a table with the Field names ready and
no records in the blank Access table. I formated the "zip" field as text. I
then import (file, external database etc) the excel sheet but it gives me an
error and says it cannot import file.

 

[Guest]

Go to your Access table that has the zipcode field (numeric), and enter six
zeroes (000000) as the format for that field. Then your imported data should
be OK. You don't have to import the data as text.

 

[Guest]

Sorry, I regularly work with 6-digit numbers with leading zeros. Forgot that
zip codes are 5-digit. Method is the same, though.

Go to your Access table that has the zipcode field (numeric), and enter five
zeroes (00000) as the format for that field. Then your imported data should
be OK. You don't have to import the data as text. Setting the field as
numeric, and limiting the number of digits, should actually save you a small
amount of disk space as compared to using text.

 

[John Vinson]

I think we are close. I have created a table with the Field names ready and
no records in the blank Access table. I formated the "zip" field as text. I
then import (file, external database etc) the excel sheet but it gives me an
error and says it cannot import file.

The Format of the field IS IRRELEVANT. It only controls how the field
is displayed, not what's stored. I must disagree with mrnature's
proposed solution - for one thing it will prohibit ever entering Zip+4
codes such as 83660-6354.

Instead use the Text *datatype*.

To populate the table, use File... Get External Data... Link to *link*
to the spreadsheet (rather than importing it), and create an Append
query to append the data into the pre-built Access table. If this is
giving you an error, please post the error number and description,
along with an example of the type of data being imported.

John W. Vinson[MVP]

 

[Guest]

I, in turn, must disagree with John. It isn't that his method wouldn't work,
but that there are sometimes several ways of getting to a similar place.

If you want a "long" zip code, you can simply put 00000-0000 as the format.
This will prevent you from putting any letters, as you should only be able to
put numbers in. A text field will not prevent you from putting letters in.
If you put this in as the format, you do not need to type the hyphen. Just
put the numbers in, and the hyphen will place itself at the point you have
indicated.

Knowing two, or more, ways of getting to a solution can be very handy. Just
use whichever one fits best to what you are doing.

 

[Jeff Boyce]

Why would you want to prevent entry of any letters? A "digit-only" postal
code is very USA-centric. What about UK postal codes?

The main reason, in my mind, for not using a number data type is that
zip/postal codes ARE NOT NUMBERS! You don't add/subtract/multiply/divide
them ... OK, so you can, but you get meaningless answers. If they aren't
numbers, then they are characters ... text.

Just one person's opinion

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Oops! Good point, Jeff. I am in the USA, and do get a bit USA-centric. I
will go sit in the corner, with my dunce cap on.

 

[Jeff Boyce]

No need ... I usually end up over-engineering (you should see the deck!).

Jeff