CSV Linked Table

[Steven M. Britton]

I have a CSV file that I overwrite daily from an e-mail,
from the CSV file I run some querys that record the CSV's
information into a table.

My question is on the CSV file there is no formating so on
the zipcode I lose the preceding Zeros for anything in the
upper east cost. Is there a way to have Access force the
zero onto the front of any number that is only 4 digits
long?

-Steven M. Britton

 

[Sal Rosario]

You can create an Update Query that will go through the table after the
import, and then add the necessary the zero before those zip codes that have
4 characters.

UPDATE YOURTABLE SET ZipCode = "0" & [ZipCode] WHERE Len([ZipCode])="4"

Sal
www.ctgtraining.com

 

[John Nurick]

Hi Steven,

The Zip codes should be stored in the CSV file like this:
...,"77022",...
...,"01234",...
and in that case they will be correctly imported into a Text field.

If you're missing the zeros, either they're not in the CSV file or the
field isn't surrounded by quotes
...,77022,...
...,01234,...
in which case Access will default to importing it as a number field - in
which case the leading zeros are not significant and are lost.

Your choices are:

1) change the software that produces the CSV file.

2) create and save an import specification that imports the Zip file to
a text field, thus preserving the leading zeros (assuming they exist in
the CSV file). To do this, import or link the CSV file once manually and
click the Advanced... button in the import wizard. Then use the
specification each time you import or link the CSV in future.

3) use a number field and set the Format property of the field and any
controls that display it to "00000". This is the least satisfactory in
the long run.

I have a CSV file that I overwrite daily from an e-mail,
from the CSV file I run some querys that record the CSV's
information into a table.

My question is on the CSV file there is no formating so on
the zipcode I lose the preceding Zeros for anything in the
upper east cost. Is there a way to have Access force the
zero onto the front of any number that is only 4 digits
long?

-Steven M. Britton

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.