Path: csiph.com!usenet.pasdenom.info!nntpfeed.proxad.net!news.muarf.org!news.roellig-ltd.de!open-news-network.org!border2.nntp.ams1.giganews.com!nntp.giganews.com!newsfeed.xs4all.nl!newsfeed2a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Date: Wed, 6 May 2015 13:37:12 -0500
From: Tim Chase <python.list@tim.thechases.com>
To: Ian Kelly <ian.g.kelly@gmail.com>
Cc: Python <python-list@python.org>
Subject: Re: Writing list of dictionaries to CSV
In-Reply-To: <CALwzid=yoTxetHRuVDaDnCW9ASXVruF02gE2CZgsszJ6=3YxWQ@mail.gmail.com>
References: <5bd0afb3-d2d4-469f-be39-5869f01adc92@googlegroups.com> <072badd6-0542-468a-99bd-cc3a4ffc7156@googlegroups.com> <554A58B4.80700@mrabarnett.plus.com> <20150506132255.510c2eec@bigbox.christie.dr> <CALwzid=yoTxetHRuVDaDnCW9ASXVruF02gE2CZgsszJ6=3YxWQ@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.186.1430937980.12865.python-list@python.org>
Lines: 49
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:90069

On 2015-05-06 12:27, Ian Kelly wrote:
> On Wed, May 6, 2015 at 12:22 PM, Tim Chase
> <python.list@tim.thechases.com> wrote:
> > On 2015-05-06 19:08, MRAB wrote:
> >> You could tell it to quote any value that's not a number:
> >>
> >>       w = csv.DictWriter(f, pol_keys,
> >> quoting=csv.QUOTE_NONNUMERIC)
> >>
> >> It looks like all of the values you have are strings, so they'll
> >> all be quoted.
> >>
> >> I would hope that Excel will then treat it as a string; it would
> >> be stupid if it didn't! :-)
> >
> > Sadly, Excel *is* that stupid based on the tests I tried just
> > now. :-(
> >
> > Regardless of whether "Mar 2015" is quoted or unquoted in the
> > source CSV file, Excel tries to outwit you and mangles the
> > presentation.
> 
> Quoting a value in csv doesn't mean it's a string; it just means
> that it's a single field.
> 
> You *can* force Excel to treat a value as a string by prefixing it
> with an apostrophe, though.

Excel takes the apostrophe in the CSV file and puts it in the
content, rather than stripping it as an escape/formatting character:

  c:\temp> type test.csv
  "'Mar 2015",Mar 2015,3,2015
  "Apr 2015",Apr 2015,4,2015
  "2015-12",2015-12,12,2015
  c:\temp> start test.csv

A1 has the unformatted text, but includes the apostrophe in the
value.  B1, A2, and B2 get munged like the OP described to the form
"Apr-15".  The items in row #3 come through untouched.

At least on Excel 2003 on WinXP which is what I happen to have on
hand.

-tkc