leading 0s in zipcode missing when exporting to csv file

[Ted]

Hi all,

I'm hoping this is a quick fix. for some reason when i export my table to a
csv file the leading 0s to my zipcodes get cut off. For example if i have
08770 as a zip it shows 08770 in the table but when i look at the csv file
it comes up with 8770. in the table the zipcode field has a data type of
TEXT. any direction would be very much appreciated.

TIA
Ted

 

[Guest]

Create a query based on the table.
For the zipcode field, do something like:

Zips: CStr([zipcode])

Export the query.

You could also try an export specification from the Advanced button on the
Export Wizard.

 

[Ted]

I tried both options. If I export the table or query directly to excel it
shows leading 0s.
If I export to a txt file it shows leading 0s. If I open the txt file with
Excel and i choose text as the data type it shows leading 0s but as soon as
I convert it to a csv file all leading 0s get truncated. Does anyone have
any ideas? Everywhere I look it says Text but as soon as it goes to csv its
like it gets converted to a number

I also tried using Export Specs where it specifically says Zipcode is a text
field.

Create a query based on the table.
For the zipcode field, do something like:

Zips: CStr([zipcode])

Export the query.

You could also try an export specification from the Advanced button on the
Export Wizard.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi all,

I'm hoping this is a quick fix. for some reason when i export my table to
a
csv file the leading 0s to my zipcodes get cut off. For example if i have
08770 as a zip it shows 08770 in the table but when i look at the csv
file
it comes up with 8770. in the table the zipcode field has a data type of
TEXT. any direction would be very much appreciated.

TIA
Ted

 

[Guest]

Hi Ted

Unfortunately, this is just how Excel behaves.

As you have been able to see for yourself, the file saved by Access is
correct.

ie something like...

"0001"
"0002"
etc

If you name the file mydata.txt and open it with Excel you are asked to
specify how the file is delimited and whether each column is "General" or
"Text" etc and the leading zeroes are kept.

However, if you do nothing with the content, so it is the same data, but
change the filename to mydata.csv and open it with Excel, I'm afraid Excel
makes a load of assumptions that it shouldn't and the result is the leading
zeroes disappear!

I've played with this issue and it looks like you either have to export to
Excel format (.xls) or try the following...

Create a query and where you have a column with leading zeroes use the
following syntax...

Select "=""" & tblMyData.ZipCode & """"

Export as CSV

The resulting file, viewed in a text editor, will look like...

"=""08770"""
"=""07654"""
etc

Open the CSV in Excel and the display is...

08770
07654


hth

Andy Hull

I tried both options. If I export the table or query directly to excel it
shows leading 0s.
If I export to a txt file it shows leading 0s. If I open the txt file with
Excel and i choose text as the data type it shows leading 0s but as soon as
I convert it to a csv file all leading 0s get truncated. Does anyone have
any ideas? Everywhere I look it says Text but as soon as it goes to csv its
like it gets converted to a number

I also tried using Export Specs where it specifically says Zipcode is a text
field.

Create a query based on the table.
For the zipcode field, do something like:

Zips: CStr([zipcode])

Export the query.

You could also try an export specification from the Advanced button on the
Export Wizard.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi all,

I'm hoping this is a quick fix. for some reason when i export my table to
a
csv file the leading 0s to my zipcodes get cut off. For example if i have
08770 as a zip it shows 08770 in the table but when i look at the csv
file
it comes up with 8770. in the table the zipcode field has a data type of
TEXT. any direction would be very much appreciated.

TIA
Ted

 

[Ted]

it does seem how excel behaves with csv files. i've done a bunch of research
and haven't found anyway to display the leading 0s in our zipcodes. thank
you for your input.

Hi Ted

Unfortunately, this is just how Excel behaves.

As you have been able to see for yourself, the file saved by Access is
correct.

ie something like...

"0001"
"0002"
etc

If you name the file mydata.txt and open it with Excel you are asked to
specify how the file is delimited and whether each column is "General" or
"Text" etc and the leading zeroes are kept.

However, if you do nothing with the content, so it is the same data, but
change the filename to mydata.csv and open it with Excel, I'm afraid Excel
makes a load of assumptions that it shouldn't and the result is the
leading
zeroes disappear!

I've played with this issue and it looks like you either have to export to
Excel format (.xls) or try the following...

Create a query and where you have a column with leading zeroes use the
following syntax...

Select "=""" & tblMyData.ZipCode & """"

Export as CSV

The resulting file, viewed in a text editor, will look like...

"=""08770"""
"=""07654"""
etc

Open the CSV in Excel and the display is...

08770
07654


hth

Andy Hull

I tried both options. If I export the table or query directly to excel it
shows leading 0s.
If I export to a txt file it shows leading 0s. If I open the txt file
with
Excel and i choose text as the data type it shows leading 0s but as soon
as
I convert it to a csv file all leading 0s get truncated. Does anyone have
any ideas? Everywhere I look it says Text but as soon as it goes to csv
its
like it gets converted to a number

I also tried using Export Specs where it specifically says Zipcode is a
text
field.

Create a query based on the table.
For the zipcode field, do something like:

Zips: CStr([zipcode])

Export the query.

You could also try an export specification from the Advanced button on
the
Export Wizard.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi all,

I'm hoping this is a quick fix. for some reason when i export my table
to
a
csv file the leading 0s to my zipcodes get cut off. For example if i
have
08770 as a zip it shows 08770 in the table but when i look at the csv
file
it comes up with 8770. in the table the zipcode field has a data type
of
TEXT. any direction would be very much appreciated.

TIA
Ted

 

[Guest]

Did you try the last bit of my last post?

ie
Create a query and where you have a column with leading zeroes use the
following syntax...

Select "=""" & tblMyData.ZipCode & """"

Export as CSV

The resulting file, viewed in a text editor, will look like...
"=""08770"""
"=""07654"""
etc

Open the CSV in Excel and the display is...

08770
07654

it does seem how excel behaves with csv files. i've done a bunch of research
and haven't found anyway to display the leading 0s in our zipcodes. thank
you for your input.

Hi Ted

Unfortunately, this is just how Excel behaves.

As you have been able to see for yourself, the file saved by Access is
correct.

ie something like...

"0001"
"0002"
etc

If you name the file mydata.txt and open it with Excel you are asked to
specify how the file is delimited and whether each column is "General" or
"Text" etc and the leading zeroes are kept.

However, if you do nothing with the content, so it is the same data, but
change the filename to mydata.csv and open it with Excel, I'm afraid Excel
makes a load of assumptions that it shouldn't and the result is the
leading
zeroes disappear!

I've played with this issue and it looks like you either have to export to
Excel format (.xls) or try the following...

Create a query and where you have a column with leading zeroes use the
following syntax...

Select "=""" & tblMyData.ZipCode & """"

Export as CSV

The resulting file, viewed in a text editor, will look like...

"=""08770"""
"=""07654"""
etc

Open the CSV in Excel and the display is...

08770
07654


hth

Andy Hull

I tried both options. If I export the table or query directly to excel it
shows leading 0s.
If I export to a txt file it shows leading 0s. If I open the txt file
with
Excel and i choose text as the data type it shows leading 0s but as soon
as
I convert it to a csv file all leading 0s get truncated. Does anyone have
any ideas? Everywhere I look it says Text but as soon as it goes to csv
its
like it gets converted to a number

I also tried using Export Specs where it specifically says Zipcode is a
text
field.

Create a query based on the table.
For the zipcode field, do something like:

Zips: CStr([zipcode])

Export the query.

You could also try an export specification from the Advanced button on
the
Export Wizard.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi all,

I'm hoping this is a quick fix. for some reason when i export my table
to
a
csv file the leading 0s to my zipcodes get cut off. For example if i
have
08770 as a zip it shows 08770 in the table but when i look at the csv
file
it comes up with 8770. in the table the zipcode field has a data type
of
TEXT. any direction would be very much appreciated.

TIA
Ted

 

[Guest]

Since your field is all numeric, Excel will see it as a number and present it
that way regardless of whether you export it as a number or text. In excel,
preceding a numeric value with a single quote will cause it to display as text
--
Dave Hargis, Microsoft Access MVP

it does seem how excel behaves with csv files. i've done a bunch of research
and haven't found anyway to display the leading 0s in our zipcodes. thank
you for your input.

Hi Ted

Unfortunately, this is just how Excel behaves.

As you have been able to see for yourself, the file saved by Access is
correct.

ie something like...

"0001"
"0002"
etc

If you name the file mydata.txt and open it with Excel you are asked to
specify how the file is delimited and whether each column is "General" or
"Text" etc and the leading zeroes are kept.

However, if you do nothing with the content, so it is the same data, but
change the filename to mydata.csv and open it with Excel, I'm afraid Excel
makes a load of assumptions that it shouldn't and the result is the
leading
zeroes disappear!

I've played with this issue and it looks like you either have to export to
Excel format (.xls) or try the following...

Create a query and where you have a column with leading zeroes use the
following syntax...

Select "=""" & tblMyData.ZipCode & """"

Export as CSV

The resulting file, viewed in a text editor, will look like...

"=""08770"""
"=""07654"""
etc

Open the CSV in Excel and the display is...

08770
07654


hth

Andy Hull

I tried both options. If I export the table or query directly to excel it
shows leading 0s.
If I export to a txt file it shows leading 0s. If I open the txt file
with
Excel and i choose text as the data type it shows leading 0s but as soon
as
I convert it to a csv file all leading 0s get truncated. Does anyone have
any ideas? Everywhere I look it says Text but as soon as it goes to csv
its
like it gets converted to a number

I also tried using Export Specs where it specifically says Zipcode is a
text
field.

Create a query based on the table.
For the zipcode field, do something like:

Zips: CStr([zipcode])

Export the query.

You could also try an export specification from the Advanced button on
the
Export Wizard.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi all,

I'm hoping this is a quick fix. for some reason when i export my table
to
a
csv file the leading 0s to my zipcodes get cut off. For example if i
have
08770 as a zip it shows 08770 in the table but when i look at the csv
file
it comes up with 8770. in the table the zipcode field has a data type
of
TEXT. any direction would be very much appreciated.

TIA
Ted