Create excel-compatible CSV file with python?

Question

I am trying to create a csv file using python that is truly Excel-compatible (I am using Excel 2007, if that makes any difference).

Here is the sort of thing I'm trying to do

import csv

data = [ ['9-1', '9-2', '9-3'] ]

fd = open('test.csv', 'wb')
try:
    writer = csv.writer(fd, dialect='excel', quotechar='"', quoting=csv.QUOTE_ALL)
    writer.writerows(data)

finally:
    fd.close()

This creates a csv file that contains:

"9-1","9-2","9-3"

When I load it in Excel, I get the columns:

09-Jan  09-Feb  09-Mar

[Grrr, thanks Excel. Seriously, what is the point of quotes?]

Now, I have read elsewhere that in order to get Excel to treat strings as literal they must be prefixed by an equals sign ="Like This". So what I really want to produce is:

="9-1",="9-2",="9-3"

Is there any way to do this with the csv module in python? I'm guessing it will involve creating a custom dialect, but I can't see any variable that would give me the desired result.

Answer 1

If your aim is just writing a list as a table on Excel. You can try below sample, where ";" and dialect='excel-tab' property enables us to switch between coloumns.

import csv

RESULTS = [
    ['val_col1;','val_col2;','val_col3']
]
resultFile = open("testExcel.csv",'wb')
resultWriter= csv.writer(resultFile, dialect='excel-tab')
resultWriter.writerows(RESULTS)

Answer 2

The problem is that you're importing your CSV file into Excel using Excel's CSV importer. Counterintuitive, I know, but you should not import CSV files that way. Instead, import them as text files (i.e., name with a txt extension). Then you will have the opportunity to specify the type of each column and can properly choose Text for columns that look like dates to Excel.

If you're looking for a turnkey "here is a file you can open in Excel" approach, try xlwt (not my original recommendation pyXLWriter) and avoid CSV entirely.