Excel fails to open Python-generated CSV files
Question: I have many Python scripts that output CSV files. It is occasionally
convenient to open these files in Excel. After installing OS X Mavericks,
Excel no longer opens these files properly: Excel doesn't parse the files and
it duplicates the rows of the file until it runs out of memory. Specifically,
when Excel attempts to open the file, a prompt appears that reads: "File not
loaded completely."

Example of code I'm using to generate the CSV files:

    
    
    import csv
    with open('csv_test.csv', 'wb') as f:
        writer = csv.writer(f)
        writer.writerow([1,2,3])
        writer.writerow([4,5,6])
    

Even the simple file generated by the above code fails to load in Excel.
However, if I open the CSV file in a text editor and copy/paste the text into
Excel, parse it with text to columns, and then save as CSV from Excel, then I
can reopen the CSV file in Excel without issue. Do I need to pass an
additional parameter in my scripts to make Excel parse the CSV files the same
way it used to? Or is there some setting I can change in OS X Mavericks or
Excel? Thanks.


Answer: Possible solution1: use *.txt instead of *.csv. In this case Excel (at least,
2010) will show you an import data wizard where you can specify delimiters,
character encoding, field types, etc.

UPD: Solution2: The python "csv" module has a "dialect" feature. For example,
the following modification of your code generates valid csv file for my
environment (Python 2.7, Excel 2010, Windows7, locale with ";" list
delimiters):

    
    
    import csv
    with open('csv_test2.csv', 'wb') as f:
        csv.excel.delimiter=';'
        writer = csv.writer(f, dialect=csv.excel)
        writer.writerow([1,2,3])
        writer.writerow([4,5,6])
    

