Writing to CSV - string being recognized as date in Excel

Question

This is probably an easy fix, but I can't seem to figure it out...

outputting a list to CSV in Python using the following code:

w = csv.writer(file('filename.csv','wb'))
w.writerows(mylist)

One of the list items is a ratio, so it contains values like '23/54', '9/12', etc. Excel is recognizing some of these values (like 9/12) as a date. What's the easiest way to solve this?

Thanks

The problem is in the format - Excel automatically applies recognition rules to .csv files and there's no way to turn it off. See also stackoverflow.com/questions/137359/excel-csv-number-cell-format — Mark Ransom
– Mark Ransom, Commented Oct 18, 2012 at 22:07
Hmm, maybe as a workaround I could split the ratio column into 2 columns...ie '9/12' becomes 2 integer columns of 9, 12. — ChrisArmstrong
– ChrisArmstrong, Commented Oct 19, 2012 at 1:40

Martijn Pieters · Accepted Answer · 2012-10-18 21:47:29Z

3

Because csv is a text-only format, you cannot tell Excel anything about how to interpret the data, I am afraid.

You'd have to generate actual Excel files (using xlwt for example, documentation and tutorials available on http://www.python-excel.org/).

answered Oct 18, 2012 at 21:47

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chen Xie Over a year ago

Agreed. I suspect if its the data presentation function in Excel who "interpreted" and "displayed" those as dates. The original file should contain the well-formatted data as expected

richie · Accepted Answer · 2013-04-17 12:43:32Z

2

You could do this:

# somelist contains data like '12/51','9/43' etc
mylist = ["'" + val + "'" for val in somelist]
w = csv.writer(open('filename.csv','wb'))
for me in mylist:
    w.writerow([me])

This would ensure your data is written as it is to csv.

answered Apr 17, 2013 at 12:43

richie

18.7k19 gold badges53 silver badges71 bronze badges

1 Comment

filmor Over a year ago

Actually you don't need to do this explicitly, csv.writer has a quoting flag that can be set: docs.python.org/2/library/csv.html#csv.QUOTE_ALL

Collectives™ on Stack Overflow

Writing to CSV - string being recognized as date in Excel

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related