Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #89977 > unrolled thread

Writing list of dictionaries to CSV

Back to article view | Back to comp.lang.python

Contents

  Writing list of dictionaries to CSV Kashif Rana <kashifrana84@gmail.com> - 2015-05-05 10:09 -0700
    Re: Writing list of dictionaries to CSV MRAB <python@mrabarnett.plus.com> - 2015-05-05 19:11 +0100
    Re: Writing list of dictionaries to CSV Skip Montanaro <skip.montanaro@gmail.com> - 2015-05-05 13:25 -0500
    Re: Writing list of dictionaries to CSV Cecil Westerhof <Cecil@decebal.nl> - 2015-05-05 20:44 +0200
    Re: Writing list of dictionaries to CSV Matthew Ruffalo <mmr15@case.edu> - 2015-05-05 15:43 -0400
    Re: Writing list of dictionaries to CSV Tim Chase <python.list@tim.thechases.com> - 2015-05-05 12:34 -0500
    Re: Writing list of dictionaries to CSV Kashif Rana <kashifrana84@gmail.com> - 2015-05-05 22:32 -0700
      Re: Writing list of dictionaries to CSV Chris Angelico <rosuav@gmail.com> - 2015-05-06 16:34 +1000
      Re: Writing list of dictionaries to CSV Cecil Westerhof <Cecil@decebal.nl> - 2015-05-06 08:50 +0200
      Re: Writing list of dictionaries to CSV Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2015-05-06 09:14 -0400
      Re: Writing list of dictionaries to CSV MRAB <python@mrabarnett.plus.com> - 2015-05-06 19:08 +0100
      Re: Writing list of dictionaries to CSV Tim Chase <python.list@tim.thechases.com> - 2015-05-06 13:22 -0500
      Re: Writing list of dictionaries to CSV Ian Kelly <ian.g.kelly@gmail.com> - 2015-05-06 12:27 -0600
      Re: Writing list of dictionaries to CSV Tim Chase <python.list@tim.thechases.com> - 2015-05-06 13:37 -0500
      Re: Writing list of dictionaries to CSV Denis McMahon <denismfmcmahon@gmail.com> - 2015-05-06 23:31 +0000
        Re: Writing list of dictionaries to CSV Tim Chase <python.list@tim.thechases.com> - 2015-05-06 20:22 -0500
        Re: Writing list of dictionaries to CSV Jon Ribbens <jon+usenet@unequivocal.co.uk> - 2015-05-07 01:55 +0000
        Re: Writing list of dictionaries to CSV [correction] Tim Chase <python.list@tim.thechases.com> - 2015-05-06 20:52 -0500

#89977 — Writing list of dictionaries to CSV

Hello Experts

When I am writing list of dictionaries to CSV file, the key 'schedule' has value 'Mar 2012' becomes Mar-12. I really do not have clue why thats happening. Below is the code. 

dic_1 = {'action': 'permit',
 'dst-address': 'maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169',
 'from': 'DMZ Web',
 'id': '1000',
 'log': 'Enable, session-init',
 'name': 'Test Rule Temporary ',
 'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL',
 'src-address': 'sparkeregap1, sparkeregap2',
 'to': 'Trust'}

dic_2 {'action': 'permit',
 'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net',
 'from': 'DMZ Web',
 'id': '4000',
 'log': 'Enable, session-init',
 'schedule': 'Mar 2012',
 'service': 'SMTP',
 'src-address': 'sparkeregap1, sparkeregap2',
 'to': 'Trust'}
 
 my_list = 
 [{'to': 'Trust', 'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL', 'from': 'DMZ Web', 'dst-address': 'maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169', 'name': 'Test Rule Temporary ', 'action': 'permit', 'id': '1000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable, session-init'}, {'to': 'Trust', 'from': 'DMZ Web', 'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net', 'service': 'SMTP', 'schedule': 'Mar 2012', 'action': 'permit', 'id': '4000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable, session-init'}]

 pol_keys = ['id', 'name', 'from', 'to', 'src-address', 'dst-address', 'service', 'action', 'nat_status', 'nat_type', 'nat_src_ip', 'nat_dst_ip', 'nat_dst_port', 'log', 'schedule']
 
with open('test.csv', 'wb') as f:
	w = csv.DictWriter(f, pol_keys)
	w.writeheader()
	w.writerows(my_list)

[toc] | [next] | [standalone]

#89985

On 2015-05-05 18:09, Kashif Rana wrote:
> Hello Experts
>
> When I am writing list of dictionaries to CSV file, the key 'schedule' has value 'Mar 2012' becomes Mar-12. I really do not have clue why thats happening. Below is the code.
>
> dic_1 = {'action': 'permit',
>   'dst-address': 'maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169',
>   'from': 'DMZ Web',
>   'id': '1000',
>   'log': 'Enable, session-init',
>   'name': 'Test Rule Temporary ',
>   'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL',
>   'src-address': 'sparkeregap1, sparkeregap2',
>   'to': 'Trust'}
>
Missing '=' in the next line.

> dic_2 {'action': 'permit',
>   'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net',
>   'from': 'DMZ Web',
>   'id': '4000',
>   'log': 'Enable, session-init',
>   'schedule': 'Mar 2012',
>   'service': 'SMTP',
>   'src-address': 'sparkeregap1, sparkeregap2',
>   'to': 'Trust'}
>
>   my_list =
>   [{'to': 'Trust', 'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL', 'from': 'DMZ Web', 'dst-address': 'maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169', 'name': 'Test Rule Temporary ', 'action': 'permit', 'id': '1000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable, session-init'}, {'to': 'Trust', 'from': 'DMZ Web', 'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net', 'service': 'SMTP', 'schedule': 'Mar 2012', 'action': 'permit', 'id': '4000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable, session-init'}]
>
>   pol_keys = ['id', 'name', 'from', 'to', 'src-address', 'dst-address', 'service', 'action', 'nat_status', 'nat_type', 'nat_src_ip', 'nat_dst_ip', 'nat_dst_port', 'log', 'schedule']
>
> with open('test.csv', 'wb') as f:
> 	w = csv.DictWriter(f, pol_keys)
> 	w.writeheader()
> 	w.writerows(my_list)
>
It works OK for me.

(I'm assuming that you're reading the CSV file in a text editor, not
some other application that might be trying to be "clever" by
"interpreting" what it thinks looks a date as a date and then
displaying it differently...)

[toc] | [prev] | [next] | [standalone]

#89988

On Tue, May 5, 2015 at 1:11 PM, MRAB <python@mrabarnett.plus.com> wrote:
> I'm assuming that you're reading the CSV file in a text editor, not
> some other application that might be trying to be "clever" by
> "interpreting" what it thinks looks a date as a date and then
> displaying it differently...

More likely, viewing the CSV file in Excel, Gnumeric, or some other
spreadsheet which interprets some inputs as dates and formats them
according to its default rules.

Skip

[toc] | [prev] | [next] | [standalone]

#89992

Op Tuesday 5 May 2015 19:09 CEST schreef Kashif Rana:

> When I am writing list of dictionaries to CSV file, the key
> 'schedule' has value 'Mar 2012' becomes Mar-12. I really do not have
> clue why thats happening. Below is the code.
>
> dic_1 = {'action': 'permit', 'dst-address': 'maxprddb-scan-167,
> maxprddb-scan-168, maxprddb-scan-169', 'from': 'DMZ Web', 'id':
> '1000', 'log': 'Enable, session-init', 'name': 'Test Rule Temporary
> ', 'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL',
> 'src-address': 'sparkeregap1, sparkeregap2', 'to': 'Trust'}
>
> dic_2 {'action': 'permit',
> 'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net',
> 'from': 'DMZ Web',
> 'id': '4000',
> 'log': 'Enable, session-init',
> 'schedule': 'Mar 2012',
> 'service': 'SMTP',
> 'src-address': 'sparkeregap1, sparkeregap2',
> 'to': 'Trust'}
>
> my_list = [{'to': 'Trust', 'service': '1521,
> Oraccle-Maximo-Scan-1550, PING-ALL', 'from': 'DMZ Web',
> 'dst-address': 'maxprddb-scan-167, maxprddb-scan-168,
> maxprddb-scan-169', 'name': 'Test Rule Temporary ', 'action':
> 'permit', 'id': '1000', 'src-address': 'sparkeregap1, sparkeregap2',
> 'log': 'Enable, session-init'}, {'to': 'Trust', 'from': 'DMZ Web',
> 'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net',
> 'service': 'SMTP', 'schedule': 'Mar 2012', 'action': 'permit', 'id':
> '4000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable,
> session-init'}]
>
> pol_keys = ['id', 'name', 'from', 'to', 'src-address',
> 'dst-address', 'service', 'action', 'nat_status', 'nat_type',
> 'nat_src_ip', 'nat_dst_ip', 'nat_dst_port', 'log', 'schedule']
>
> with open('test.csv', 'wb') as f:
> 	w = csv.DictWriter(f, pol_keys)
> 	w.writeheader()
> 	w.writerows(my_list)

Well, I get:
    id,name,from,to,src-address,dst-address,service,action,nat_status,nat_type,nat_src_ip,nat_dst_ip,nat_dst_port,log,schedule
    1000,Test Rule Temporary ,DMZ Web,Trust,"sparkeregap1, sparkeregap2","maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169","1521, Oraccle-Maximo-Scan-1550, PING-ALL",permit,,,,,,"Enable, session-init",
    4000,,DMZ Web,Trust,"sparkeregap1, sparkeregap2","sparkcas01, sparkcas02, email.ab.spark.net",SMTP,permit,,,,,,"Enable, session-init",Mar 2012

So there is something different on your system.


By the way: next time make sure that we can copy paste your code:
- dic_2 does not have a '='
- get rid of the space before my_list
- make sure the '[' is at the same line as my_list (or use a \)

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#89995

On 2015-05-05 14:25, Skip Montanaro wrote:
> More likely, viewing the CSV file in Excel, Gnumeric, or some other
> spreadsheet which interprets some inputs as dates and formats them
> according to its default rules. Skip 

This is depressingly common, and I've even received CSV and plain text
data files that had some gene names(!) coerced to dates via a trip
through Excel. SEPT9 was one such gene, I believe.

MMR...

[toc] | [prev] | [next] | [standalone]

#90006

On 2015-05-05 10:09, Kashif Rana wrote:
> When I am writing list of dictionaries to CSV file, the key
> 'schedule' has value 'Mar 2012' becomes Mar-12. 

How are you making this determination?  Are you looking at the raw
CSV output, or are you looking at the CSV file loaded into a
spreadsheet like Excel?

I suspect that it's a matter of the file being what you want, but
your spreadsheet mangling/reformatting it visually.

-tkc

[toc] | [prev] | [next] | [standalone]

#90027

Hello guys

thanks for the feedback. I think its problem with excel itself, showing wrong value. Because when I opened the csv file in text editor, I can see correct value but opening in excel showing wrong value. What I can do to see correct in excel as well.

Regards

On Tuesday, May 5, 2015 at 9:09:40 PM UTC+4, Kashif Rana wrote:
> Hello Experts
> 
> When I am writing list of dictionaries to CSV file, the key 'schedule' has value 'Mar 2012' becomes Mar-12. I really do not have clue why thats happening. Below is the code. 
> 
> dic_1 = {'action': 'permit',
>  'dst-address': 'maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169',
>  'from': 'DMZ Web',
>  'id': '1000',
>  'log': 'Enable, session-init',
>  'name': 'Test Rule Temporary ',
>  'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL',
>  'src-address': 'sparkeregap1, sparkeregap2',
>  'to': 'Trust'}
> 
> dic_2 {'action': 'permit',
>  'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net',
>  'from': 'DMZ Web',
>  'id': '4000',
>  'log': 'Enable, session-init',
>  'schedule': 'Mar 2012',
>  'service': 'SMTP',
>  'src-address': 'sparkeregap1, sparkeregap2',
>  'to': 'Trust'}
>  
>  my_list = 
>  [{'to': 'Trust', 'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL', 'from': 'DMZ Web', 'dst-address': 'maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169', 'name': 'Test Rule Temporary ', 'action': 'permit', 'id': '1000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable, session-init'}, {'to': 'Trust', 'from': 'DMZ Web', 'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net', 'service': 'SMTP', 'schedule': 'Mar 2012', 'action': 'permit', 'id': '4000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable, session-init'}]
> 
>  pol_keys = ['id', 'name', 'from', 'to', 'src-address', 'dst-address', 'service', 'action', 'nat_status', 'nat_type', 'nat_src_ip', 'nat_dst_ip', 'nat_dst_port', 'log', 'schedule']
>  
> with open('test.csv', 'wb') as f:
> 	w = csv.DictWriter(f, pol_keys)
> 	w.writeheader()
> 	w.writerows(my_list)

[toc] | [prev] | [next] | [standalone]

#90031

On Wed, May 6, 2015 at 3:32 PM, Kashif Rana <kashifrana84@gmail.com> wrote:
> thanks for the feedback. I think its problem with excel itself, showing wrong value. Because when I opened the csv file in text editor, I can see correct value but opening in excel showing wrong value. What I can do to see correct in excel as well.
>

Simple fix: Ditch Excel.

Slightly more complicated fix: Open the file, then select everything,
and tell Excel that it's plain text. You can't say that in the CSV
file.

ChrisA

[toc] | [prev] | [next] | [standalone]

#90035

Op Wednesday 6 May 2015 07:32 CEST schreef Kashif Rana:

> thanks for the feedback. I think its problem with excel itself,
> showing wrong value. Because when I opened the csv file in text
> editor, I can see correct value but opening in excel showing wrong
> value. What I can do to see correct in excel as well.

A Python mailing list is not the right place to ask that. You can
better use a Microsoft (office) mailing list.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#90054

On Wed, 6 May 2015 16:34:33 +1000, Chris Angelico <rosuav@gmail.com>
declaimed the following:

>On Wed, May 6, 2015 at 3:32 PM, Kashif Rana <kashifrana84@gmail.com> wrote:
>> thanks for the feedback. I think its problem with excel itself, showing wrong value. Because when I opened the csv file in text editor, I can see correct value but opening in excel showing wrong value. What I can do to see correct in excel as well.
>>
>
>Simple fix: Ditch Excel.
>
>Slightly more complicated fix: Open the file, then select everything,
>and tell Excel that it's plain text. You can't say that in the CSV
>file.
>
	Or (manually): /don't/ "open" the CSV file... Open a blank Excel
document, and IMPORT the CSV -- as part of the import process you can then
define what type of data each column should be treated as.
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#90066

On 2015-05-06 06:32, Kashif Rana wrote:
> Hello guys
>
> thanks for the feedback. I think its problem with excel itself, showing wrong value. Because when I opened the csv file in text editor, I can see correct value but opening in excel showing wrong value. What I can do to see correct in excel as well.
>
> Regards
>
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! :-)

> On Tuesday, May 5, 2015 at 9:09:40 PM UTC+4, Kashif Rana wrote:
>> Hello Experts
>>
>> When I am writing list of dictionaries to CSV file, the key 'schedule' has value 'Mar 2012' becomes Mar-12. I really do not have clue why thats happening. Below is the code.
>>
>> dic_1 = {'action': 'permit',
>>  'dst-address': 'maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169',
>>  'from': 'DMZ Web',
>>  'id': '1000',
>>  'log': 'Enable, session-init',
>>  'name': 'Test Rule Temporary ',
>>  'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL',
>>  'src-address': 'sparkeregap1, sparkeregap2',
>>  'to': 'Trust'}
>>
>> dic_2 {'action': 'permit',
>>  'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net',
>>  'from': 'DMZ Web',
>>  'id': '4000',
>>  'log': 'Enable, session-init',
>>  'schedule': 'Mar 2012',
>>  'service': 'SMTP',
>>  'src-address': 'sparkeregap1, sparkeregap2',
>>  'to': 'Trust'}
>>
>>  my_list =
>>  [{'to': 'Trust', 'service': '1521, Oraccle-Maximo-Scan-1550, PING-ALL', 'from': 'DMZ Web', 'dst-address': 'maxprddb-scan-167, maxprddb-scan-168, maxprddb-scan-169', 'name': 'Test Rule Temporary ', 'action': 'permit', 'id': '1000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable, session-init'}, {'to': 'Trust', 'from': 'DMZ Web', 'dst-address': 'sparkcas01, sparkcas02, email.ab.spark.net', 'service': 'SMTP', 'schedule': 'Mar 2012', 'action': 'permit', 'id': '4000', 'src-address': 'sparkeregap1, sparkeregap2', 'log': 'Enable, session-init'}]
>>
>>  pol_keys = ['id', 'name', 'from', 'to', 'src-address', 'dst-address', 'service', 'action', 'nat_status', 'nat_type', 'nat_src_ip', 'nat_dst_ip', 'nat_dst_port', 'log', 'schedule']
>>
>> with open('test.csv', 'wb') as f:
>> 	w = csv.DictWriter(f, pol_keys)
>> 	w.writeheader()
>> 	w.writerows(my_list)
>

[toc] | [prev] | [next] | [standalone]

#90067

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.

-tkc

[toc] | [prev] | [next] | [standalone]

#90068

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.

[toc] | [prev] | [next] | [standalone]

#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

[toc] | [prev] | [next] | [standalone]

#90080

On Tue, 05 May 2015 22:32:28 -0700, Kashif Rana wrote:

> thanks for the feedback. I think its problem with excel itself, showing
> wrong value. Because when I opened the csv file in text editor, I can
> see correct value but opening in excel showing wrong value. What I can
> do to see correct in excel as well.

You need to format your CSV date into a date format that Excel 
understands when it imports it.

First thing to try would be to export some dates from excel as CSV and 
see what format excel puts them in.

The see if excel recognises them as dates when you re-import the same 
file.

If excel recognises it's own csv exported dates, reformat your dates to 
match the excel ones when you generate the csv.

Otherwise, you might need to convert the dates to a numeric value and 
tell excel to format the field as date after input.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#90082

On 2015-05-06 23:31, Denis McMahon wrote:
> On Tue, 05 May 2015 22:32:28 -0700, Kashif Rana wrote:
> > thanks for the feedback. I think its problem with excel itself,
> > showing wrong value. Because when I opened the csv file in text
> > editor, I can see correct value but opening in excel showing
> > wrong value. What I can do to see correct in excel as well.
> 
> You need to format your CSV date into a date format that Excel 
> understands when it imports it.

That's part of the problem.  Excel is taking the OP's literal values
and interpreting them as dates (and formatting them in the way Excel
wants to) rather than accepting them as literal values.

As ChrisA posted earlier, you have to use Excel's Import
functionality (there are several ways to get this wizard, but not all
ways of opening a .csv trigger the wizard), then specify those
particular columns as "Text" rather than "General" (which
auto-detects as a Date).  This prevents Excel from overthinking the
problem, allowing Excel to import the text as-is like the OP wants.

> First thing to try would be to export some dates from excel as CSV
> and see what format excel puts them in.
> 
> The see if excel recognises them as dates when you re-import the
> same file.

If you create the formatting on such cells the way that the OP wants
them, then export, then re-import, Excel still mangles them since
the CSV spec doesn't preserve formatting.

-tkc

[toc] | [prev] | [next] | [standalone]

#90083

On 2015-05-06, Denis McMahon <denismfmcmahon@gmail.com> wrote:
> You need to format your CSV date into a date format that Excel 
> understands when it imports it.
>
> First thing to try would be to export some dates from excel as CSV and 
> see what format excel puts them in.

Beware of assuming that Excel can import its own exported data.
You may be disappointed.

[toc] | [prev] | [next] | [standalone]

#90084 — Re: Writing list of dictionaries to CSV [correction]

On 2015-05-06 20:22, Tim Chase wrote:
> As ChrisA posted earlier, you have to use Excel's Import
> functionality (there are several ways to get this wizard, but not
> all ways of opening a .csv trigger the wizard), then specify those
> particular columns as "Text" rather than "General"

Sorry, it was Dennis Lee Bieber responding to ChrisA with that
suggestion.

-tkc

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web