Retaining leading Zeros in csv file

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
[download]

Comment on Retaining leading Zeros in csv file Download Code

Replies are listed 'Best First'.
Re: Retaining leading Zeros in csv file by Tux (Canon) on Jul 02, 2008 at 07:48 UTC
Never use split for CSV files. Use Text::CSV_XS or Text::CSV 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 Use tools like csv2xls to convert CSV to XLS Leading zeroes get lost when you use the scalar in numeric context, like checking against a number Enjoy, Have FUN! H.Merijn	[reply]
Re^2: Retaining leading Zeros in csv file by psini (Deacon) on Jul 02, 2008 at 08:40 UTC
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."	[reply]
Re^3: Retaining leading Zeros in csv file by harishnuti (Beadle) on Jul 02, 2008 at 15:13 UTC
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 ....	[reply]
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.	[reply] [d/l]
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 battery_sel@yahoo.com for more info.	[reply]