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?
6  7719 
"JimmyKoolPantz" <ko*******@gmail.comwrote in message
news:4c**********************************@w56g2000 hsf.googlegroups.com...
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.
On Dec 25, 4:46*pm, "Michael D. Ober" <obermd.@.alum.mit.edu.nospam.>
wrote:
"JimmyKoolPantz" <kohl.m...@gmail.comwrote in message
news:4c**********************************@w56g2000 hsf.googlegroups.com...
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.- 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?
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
On Dec 26, 6:43*am, "Martin H." <hk...@gmx.netwrote:
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
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.
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
"JimmyKoolPantz" wrote:
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?
On Dec 26, 9:54*am, Terry <Ter...@nospam.nospamwrote:
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
"JimmyKoolPantz" wrote:
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?- Hide quoted text -
- 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: samik_tanik |
last post by:
I need to export a datagrid to Excel.
I could did this. But, also need to keep the leading
zeros in the data.
How can I acheive this?
Any help would be appreciated.
--
Thanking you in...
|
by: mmmgood1 |
last post by:
Help, I'm linking an excel spreadsheet in access and I have datafields
with leading zeros (01021). When the file is linked in access, I get
a #num in the field with the leading zeros. The zeros...
|
by: Clint Stowers |
last post by:
Using A2k
Exporting a query to a CSV file. The problem is any text fields (i.e. 000345)
lose any leading zeros.
Exporting to an excel file this problem does not exist.
Tried to create a...
|
by: OneDay |
last post by:
I've got a field that has some old data with text in it, but all forward
data will be a 3 digit number. But many of the numbers are still only 2
digits. I would like to force the leading zero in...
|
by: Rich Raffenetti |
last post by:
How can one format an integer into a hex string with leading zeros? Suppose
an integer is 512 which in Hex is 200. I wish to print the 4-byte integer
as 0200 or even 0x0200. The HEX function...
|
by: Monty |
last post by:
Hi All,
I am having a problem with leading zeros being stripped from fields in a CSV
file when I bring them in using Jet/OleDB. In VB.Net/VS 2008, I am accessing
a CSV file like so:
sSQL =...
|
by: jenniferhelen |
last post by:
I am working in Access 2007 and trying to import a .csv file. One of the fields in the .csv file, called Locations, contains numbers with leading zeros and also the last record contains letters. I...
|
by: Jessica Tubbs |
last post by:
Hi,
I am trying to add leading zeros to a field but the zeros are not displying in the results.
The code below will display the added zeros because I have a space at the beginning of my leading...
|
by: Deekay |
last post by:
Is there a way to preserve possible leading zeros in a number even when it is passed as text?
I have this code on a form which runs off a command button taking two values from user input, passing...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
