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:

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

In reply to Re: Formatting a csv column as 'number' by Tux
in thread Formatting a csv column as 'number' by viffer

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.