3

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

ChrisArmstrong
  • 2,491
  • 8
  • 37
  • 60
  • 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 http://stackoverflow.com/questions/137359/excel-csv-number-cell-format – Mark Ransom Oct 18 '12 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 Oct 19 '12 at 01:40

2 Answers2

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/).

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • 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 – Chen Xie Oct 18 '12 at 21:50
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.

richie
  • 17,568
  • 19
  • 51
  • 70
  • 1
    Actually you don't need to do this explicitly, `csv.writer` has a `quoting` flag that can be set: http://docs.python.org/2/library/csv.html#csv.QUOTE_ALL – filmor Apr 17 '13 at 12:48