473,695 Members | 2,907 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Leading Zeros - CSV File

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?

Dec 25 '07 #1
6 7756
"JimmyKoolPantz " <ko*******@gmai l.comwrote in message
news:4c******** *************** ***********@w56 g2000hsf.google groups.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.
Dec 25 '07 #2
On Dec 25, 4:46*pm, "Michael D. Ober" <obermd.@.alum. mit.edu.nospam. >
wrote:
"JimmyKoolPantz " <kohl.m...@gmai l.comwrote in message

news:4c******** *************** ***********@w56 g2000hsf.google groups.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?
Dec 26 '07 #3
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.5 0"
2,"Item 2","000001.0 0"

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
Dec 26 '07 #4
On Dec 26, 6:43*am, "Martin H." <hk...@gmx.netw rote:
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.5 0"
2,"Item 2","000001.0 0"

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",000 001

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.
Dec 26 '07 #5
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?

Dec 26 '07 #6
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.
Dec 28 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
11503
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 anticipation, Regards,
1
4550
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 are needed. I've formated the field to be a text field, general number, and number and still I get the #num! error, or the leading zero is dropped. Can someone help before I pull more of my hair. Thanks a bunch.
6
5301
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 SpecificationName via the Export Wizard without success. Obviously doing something wrong.
5
20409
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 the entry of the field. For example if the number 77 is entered into the field, 077 will display. How do I format to force the leading zero?
6
19179
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 doesn't create leading zeros. The Format function (format(value,"X") doesn't create leading zeros. I believe there should be a simple way that doesn't involving measuring and padding the string.
0
4097
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 = "SELECT * FROM " sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ Microsoft.VisualBasic.FileIO.FileSystem.GetParentPath(msFile) & _
6
7972
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 am importing into a predefined table and the field Locations is set up with Text formatting. When I import my file, the leading zeros are lost and the last record that contains letters is not loaded. The error I receive is “Type Conversion”. I...
3
2867
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 zeros: SELECT ' 0000000'+ STR(DATE_FIELD,7,0) as DATEFMT FROM tbl_1
15
9988
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 them to a function and is placing the returned value as a string back in a thrid textbox ('regNo'). The function 'generatecode' is correctly returning a string of digits which may be between 6 to 11 digits long and might contain between 0 and 3...
0
8640
marktang
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8582
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9001
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8860
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8832
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7672
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6498
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4348
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2281
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.