Leading Zeros - CSV File

[JimmyKoolPantz]

Task: Customer wants a script of the data that was processed in a
"CSV" file.

Problem: Zip-Code leading zeros are dropped


Basically we have a client that has requested a custom script for each
file that he has us process. He wants this in a Comma Delimited
Format.

I did some research and noticed everyone has the same problem I have,
with the leading zero's not being brought over during the conversion.
I did not find the answer I was looking for so, i am posting this to
see if someone has a possible solution.

The original files are in a .dbf format. I load the dbf file into
memory and then change the column headers as the client has requested,
and planned on writing out to a filestream. However, when I do this,
and open the .csv file, my leading zeros are missing.

I have tried putting an apostrophy in front of the number (example:
'0001) and open the csv file, the leading zeros are there but i have
an apostrophy in front of the number.

Basically, what i'm trying to accomplish here is create a few
functions that saves the data as a .csv file (retain leading zeros)
and upload the file to the customers ftp site. I do not want to
convert it, and then open the file and change column formats. I just
want to do this programmically without playing with it.

Any suggestions?

 

[Michael D. Ober]

Task: Customer wants a script of the data that was processed in a
"CSV" file.

Problem: Zip-Code leading zeros are dropped


Basically we have a client that has requested a custom script for each
file that he has us process. He wants this in a Comma Delimited
Format.

I did some research and noticed everyone has the same problem I have,
with the leading zero's not being brought over during the conversion.
I did not find the answer I was looking for so, i am posting this to
see if someone has a possible solution.

The original files are in a .dbf format. I load the dbf file into
memory and then change the column headers as the client has requested,
and planned on writing out to a filestream. However, when I do this,
and open the .csv file, my leading zeros are missing.

I have tried putting an apostrophy in front of the number (example:
'0001) and open the csv file, the leading zeros are there but i have
an apostrophy in front of the number.

Basically, what i'm trying to accomplish here is create a few
functions that saves the data as a .csv file (retain leading zeros)
and upload the file to the customers ftp site. I do not want to
convert it, and then open the file and change column formats. I just
want to do this programmically without playing with it.

Any suggestions?

From your description, I am guessing that you are opening the CSV file in
Excel. Excel drops leading zeros. If you open the file in Notepad or
Wordpad, what do you see?

Mike.

 

[JimmyKoolPantz]

From your description, I am guessing that you are opening the CSV file in
Excel.  Excel drops leading zeros.  If you open the file in Notepad or
Wordpad, what do you see?

Mike.- Hide quoted text -

- Show quoted text -

If I open the file in a txt format, then I see leading zero's. But my
concern is, if the client opens the file using excel then the leading
zeros are not displayed. Also, I believe the client will be adding
these scripts to a master database. Another concern that I have, is
what will happen if you adds the records from the csv file to the
master file, will there be leading zeros?

 

[Martin H.]

Hello Jimmy,

Problem: Zip-Code leading zeros are dropped
Basically we have a client that has requested a custom script for each
file that he has us process. He wants this in a Comma Delimited
Format.

It depends on how the data file looks like...

Let's say it looks like this:

1,"Item 1",000012.50
2,"Item 2",000001.00

then the leading zeros are dropped as Excel recognizes them as a numeric
format.

But if the format looked like this:
1,"Item 1","000012.50"
2,"Item 2","000001.00"

Excel would use the leading zeros as they are part of a string.

The original files are in a .dbf format. I load the dbf file into
memory and then change the column headers as the client has requested,
and planned on writing out to a filestream. However, when I do this,
and open the .csv file, my leading zeros are missing.

I have tried putting an apostrophy in front of the number (example:
'0001) and open the csv file, the leading zeros are there but i have
an apostrophy in front of the number.

With this you changed from numeric format to string format (as an
apostrophe is not a numeric character), so Excel couldn't drop the
leading zeros.

Best regards,

Martin

 

[JimmyKoolPantz]

Hello Jimmy,


It depends on how the data file looks like...

Let's say it looks like this:

1,"Item 1",000012.50
2,"Item 2",000001.00

then the leading zeros are dropped as Excel recognizes them as a numeric
format.

But if the format looked like this:
1,"Item 1","000012.50"
2,"Item 2","000001.00"

Excel would use the leading zeros as they are part of a string.



With this you changed from numeric format to string format (as an
apostrophe is not a numeric character), so Excel couldn't drop the
leading zeros.

Best regards,

Martin

I ran a few tests, i created 2 text files with the following data.

test1:
"Item1",000001

test2:
"item1","000001"

I then changed the file extention to ".csv", and opened the file in
excel. Both test, same results. Missing leading zeros.

Test 3: I imported test2.csv into a microsoft access database, and
leading zeros are there.

The default application to open .csv files at work is Microsoft
Excel. My question now is, "Is it possible to create a csv file
programmically, which would display leading zeros when any user opens
the .csv file using Microsoft excel?"

I think I have tried everything except changing microsoft excel
formatting properties.

 

[Terry]

Using Excel 2002 - don't name the file with the .csv extension - make it .txt
- then Excel will give you the chance to tell it the format for each column
and you can switch the column with leading 0's from general to text. You
also go through this series of choices if you 'import data' and import the
csv file as opposed to just using the open dialog.

 

[JimmyKoolPantz]

Using Excel 2002 - don't name the file with the .csv extension - make it .txt
- then Excel will give you the chance to tell it the format for each column
and you can switch the column with leading 0's from general to text.  You
also go through this series of choices if you 'import data' and import the
csv file as opposed to just using the open dialog.
--
Terry












- Show quoted text -

Well, the reason I was given this task was so we can "automate" the
customers request for a ".csv" script. So, creating a text file is
not an option. I talked to my boss, and he basically said there is
nothing I can do. He said if I open the file as a text file and see
the leading zeros I would be fine.