Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Re: Formatting a csv column as 'number'

by Tux (Canon)
on Mar 13, 2012 at 07:03 UTC ( #959253=note: print w/replies, xml ) Need Help??

in reply to Formatting a csv column as 'number'

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
  • Comment on Re: Formatting a csv column as 'number'

Replies are listed 'Best First'.
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 :(

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (3)
As of 2022-06-27 04:59 GMT
Find Nodes?
    Voting Booth?
    My most frequent journeys are powered by:

    Results (86 votes). Check out past polls.