Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Retaining leading Zeros in csv file

by harishnuti (Beadle)
on Jul 02, 2008 at 07:39 UTC ( #695091=perlquestion: print w/replies, xml ) Need Help??
harishnuti has asked for the wisdom of the Perl Monks concerning the following question:

Ohh my dear monks, iam stuck with an task of retaining leading zeros in .csv file which i have created..
# reading a text file delimited by ! while (<FILE>){ my @array = split(/\!/,$_); #to my surprise $array[10] field may #have leading zeros, #for ex:- 00090,00089,003 etc $str = join(",",@array); print FILE $str ; # iam writing this to some file #i figured one approach as below. $array[10] = qq~="$array[10]"~; #i havent tested in code , but manually #when i prepended = and including element #in double quotes worked #now when i open csv in excel i can #see leading zeros #just like a formula ="place your element here" #pls let me know if any better #approach to achive above

Replies are listed 'Best First'.
Re: Retaining leading Zeros in csv file
by Tux (Abbot) on Jul 02, 2008 at 07:48 UTC
    1. Never use split for CSV files. Use Text::CSV_XS or Text::CSV
    2. Viewing data with M$-Excel is not a way of proving any conversion or scanning to be successful, as Excel is more likely to alter your data than your perl program
    3. Use tools like csv2xls to convert CSV to XLS
    4. Leading zeroes get lost when you use the scalar in numeric context, like checking against a number

    Enjoy, Have FUN! H.Merijn

      Expanding point #2: MSExcel does trim leading zeros from any field data recognized as number (i.e. can be converted to a number) when reading from a CSV file.

      It is a very annoying "feature" and I never found a way to disable it. The only "solution" I found is renaming the .csv file to .txt to prevent automatic parsing, importing it as a text delimited file and then forcing all column to text mode.

      If you need to generate a file from Perl to be used by Excel I strongly suggest Spreadsheet::WriteExcel

      Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."

        Thx for the suggestions.. seems nothing can be done from coding side to overcome the above excel functionality ...i will consider renaming it into .txt and then importing from excel ....
Re: Retaining leading Zeros in csv file
by igelkott (Priest) on Jul 03, 2008 at 18:48 UTC

    Not a perfect solution but a leading single quote (') is enough to prevent excel's auto-conversion.

    The only catch is that when the data is read from a file, the quote will be visible until the cell is reevaluated (eg, cursor hitting return after the cursor is in the cell for editing). Might try a simple macro to touch each cell?

    The "call it a text file" approach seems better but this is an alternative if a text file is inconvenient.

Re: Retaining leading Zeros in csv file
by Anonymous Monk on Dec 31, 2008 at 05:32 UTC
    Hi The solution is very simple. I can explain you the steps. I have done this myself and it works. The idea is to convert the number column in excel into text and save it. Contact me at for more info.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://695091]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2018-04-20 01:39 GMT
Find Nodes?
    Voting Booth?