Formatting a csv column as 'number'

viffer has asked for the wisdom of the Perl Monks concerning the following question:

Afternoon most munificent ones

I have a, hopefully, simple query.
I am writing a comma separated output file called xyz.csv
One of the columns has a large digit number in which, when opening in Excel, displays as (for example) 1.20201E+13 rather than 12011621300726.
I am not using "Spreadsheet::WriteExcel" but a simple

open( OUT, '>', "$output_file" ) or croak "Unable to open $output_file
+, $!";
print OUT "$number[2], $number[$i], $number[$i+1]\n";
[download]

Is there a way within that simple print statement that you can replicate the type of code you'd use in 'write excel' that would enable the large digit field ($number$i) to be viewed as if the cell had been formatted as 'number'?

my $fmt = $workbook->add_format( num_format => '0' );
  $worksheet->write_string( 'A2', $number[$i], $fmt );
[download]

Thanks

Comment on Formatting a csv column as 'number' Select or Download Code

Replies are listed 'Best First'.
Re: Formatting a csv column as 'number' by Tux (Canon) on Mar 13, 2012 at 07:03 UTC
Don't put spaces in CSV output like that, it may very well break (some) readers/parsers. If your CSV - as in your example - only has numbers, be aware that if the current locale has "," as TRIAD_SEP you can get into weird problems (maybe not in the perl part, but you are talking about a tool chain here). If you want to make sure your CSV is valid CSV, use a module like Text::CSV and/or Text::CSV_XS to generate the CSV. I have not tested with recent Excel versions, but what bitimgduck suggests, might work, but then again, might not because Excel is known to ignore "strings" in CSV imports. If the value after parsing looks like a date (M$Excel speak), it will convert it to a date. Most of the time, it will do it wrong, as "date" in Excel is always US date format (MM/DD/YYYY), even if the current locale is close to ISO (YYYY-MM-DD or DD-MM-YYYY). If your column consists of plain "numbers" (not dates) and one of the "numbers" comes up as a date in the Excel sheet after import, do not be surprised. Then there is three ways of opening a CSV in Excel: Double-click on the csv file in the explorer; Open Excele, and then Alt-F->Open the file; Open Excel and then Data->Import from file. These all interpret CSV data differently! YMMV So, the only way to make your data look consistent in Excel is to write an Excel book yourself, and - as you have shown already - use formatting specifications. Spreadsheet::WriteExcel works perfect and is able to write reproducible formatting. Enjoy, Have FUN! H.Merijn	[reply]
Re^2: Formatting a csv column as 'number' by viffer (Beadle) on Apr 02, 2014 at 02:01 UTC
I'm sorry, it looks as though I never thanked people for their replies. Very remiss of me and I do humbly belatedly apologise :(	[reply]
Re: Formatting a csv column as 'number' by bitingduck (Chaplain) on Mar 13, 2012 at 05:37 UTC
It sounds like it's more of an Excel problem than a Perl problem-- Excel doesn't seem to like large numbers, and will mindlessly convert them to scientific notation and fill the trailing digits with zeros. If you wrap your large number in quotes, it should import into excel and display properly, but will likely be treated as a string (I didn't bother to test it, but I've had it do that to me in similar cases). edit: it might also help if you prefix it with a single quote like this: '12345 which is what you do when you type entries when you want to force them to be treated as text.	[reply]
Re: Formatting a csv column as 'number' by hawtin (Prior) on Mar 13, 2012 at 20:37 UTC
This sounds like an Excel problem to me, for example if you create a text file containing: `'0100',"0100",0100` [download] Open it in Excel then save it. You will see that anything Excel thinks could be a number (or a date, or fraction, or whatever) it treats as one and messes up accordingly. It makes some jobs that should be simple a real pain	[reply] [d/l]