Trouble importing Excel field correctly

[Guest]

I am trying to import an Excel file that has a field formated like a zip code
(Must have 5 characters). Some of the numbers in this field have leading
zeroes. In Excel the format is correct (after I format the field for zip
codes), but when I import it into Access 2003 Access strips out the leading
zeroes. I have tried creating the table before import using an imput mask and
that does not work. I can do a find and replace and make the fields correct,
but that is a huge waste of time and since I have to do this every month for
thousands of records not really an option. I export this data from a 3rd
application so entering the data directly into Access is not an option
either. Does anyone know how I can retain this format during import. Thanks
for any help.

 

[Garfield]

I met similar problem before. The only different is that I was trying
to import text format data and I got a lot of "#N/A"s. That was
because excel only set up the cells but not the data in the cells when
you format a field. You can try format the field, then copy and paste
the data back to the field. It will be even better if you can use
formula to convert the data to the format you need and then copy and
paste the value back to formatted field. This way will give you 100%
correct data.

 

[Van T. Dinh]

Your Zip Code values are imported into Access as numerical values since
01234 = 1234 numerically.

You can simply use the Format() function like:

Format([ZipCode], "00000")

whenever you need to display as 5-digit codes.

Personally, I prefer it to be stored as Text in Access and I would use the
Excel spreadsheet function TEXT() to convert the whole Column in Excel to
Text values before importing into Access.

 

[Ron2005]

1) You might try the formatting as txt first - it is the easiest. But I
have found Excell to sometimes be too smart for my own good and not do
it well. In one instance I had to make a macro to add a colum formated
as txt and then select and copy the zip formated colume of data into it
and then delete the original.

2) I have also had the import be too smart for me and refuse to import
it as txt but create it as a numeric always ( but I may have been using
a link and append then.) I had to end up adding vba code to first open
the spreadsheet insert a row 2 and then go down the row and put a
letter or a number in each column as required to make the link/import
recognize it as the proper type. Then ran query to delete the row once
it was moved over into my full access table.

3) Also agree with suggestion that zip be alpha. At some point Canadian
alpabetic zip will kill you if you do not. At least this way you will
not have to convert to alpha whenever you end up having to handle
Canadian zip. {[Pay me now, or pay me later syndrome.]}

Ron

 

[Guest]

Thanks for all the suggestions. I will test them out.

I have set the Excel column to test, but when Access imports all it sees are
numbers and converts the field to nuumric during the import. The field type
is greyed out during import so I can't change the type. It's very
frustrating. Access thinks it knows best and won't allow you to change the
field type. I will try your suggestions though.

 

[Van T. Dinh]

Have you tried the Excel spreadsheet TEXT() function as I suggested?

Add an additional Column "Zipnext to the ZipCode column in your *Excel
spreadsheet* and make this as "calculated Column" of the ZipCode Column
using the TEXT() function (check Excel Help). When you import the
spreadsheet into Access, skip the original ZipCode column and import the
ZipText Column.

 

[John Vinson]

Thanks for all the suggestions. I will test them out.

I have set the Excel column to test, but when Access imports all it sees are
numbers and converts the field to nuumric during the import. The field type
is greyed out during import so I can't change the type. It's very
frustrating. Access thinks it knows best and won't allow you to change the
field type. I will try your suggestions though.

Van's Text() column suggestion sounds like the best bet, but I've
often resorted to creating an empty table in Access with the desired
datatypes, and importing into that table (rather than letting the
import routine create the table, making its guesses at the appropriate
datatype).

Once I recall I even had to put in a "dummy" row at the top of the
spreadsheet with "This Is Text" in the column containing
numbers-as-text data. Then I had to delete the dummy record.

John W. Vinson[MVP]

 

[Guest]

Van you ROCK!!! That fixed my problem!!! Everyone thanks for the help.