Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Formatting a csv column as 'number'

by viffer (Beadle)
on Mar 13, 2012 at 05:15 UTC ( #959235=perlquestion: print w/replies, xml ) Need Help??

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";
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 );

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
      I'm sorry, it looks as though I never thanked people for their replies. Very remiss of me and I do humbly belatedly apologise :(
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.
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:


    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

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://959235]
Approved by Ratazong
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (5)
As of 2022-05-23 11:05 GMT
Find Nodes?
    Voting Booth?
    Do you prefer to work remotely?

    Results (81 votes). Check out past polls.