How to Write Python Output into an Excel CSV File?

[PeterDonis]

I would be surprised if Python did not have similar methods.

Pandas, which is a Python library that has precisely such methods, has already been referred to several times in this thread.

 

[FactChecker]

Pandas, which is a Python library that has precisely such methods, has already been referred to several times in this thread.

Oh, sorry! I stand corrected. I did not realize that it was a Python library. I thought that an entirely different language was being recommended.

 

[harborsparrow]

Speaking from extensive experience in exporting data into .csv files, I would not use a library or try to automate Excel. Versions of Excel change, but a .csv file (always openable by Excel) is plain text. You'll want to be careful about encoding--use at least UTF-8 and make it explicit. You'll want to check each cell's worth of data for commas, and if you surround each cell with ", you'll want to watch for and handle that in each data item also. It's not that hard to do--takes a bit of tinkering--and if you treat it like a plain text file and handle it yourself, it will continue to work no matter what version of libraries or Excel comes along in a few years.

I could give you C# code that does this in one instance (try the link here--it's a list of algae taxa):

http://diatom.ansp.org/taxaservice/ExcelExportNawqaTaxaWithFlags.ashx?taxonomy_id=21&diatom=0

The above file comes from a REST web service (ASP.NET) written in C#; I just looked at the code. It pulls data from a database, converts everything to text and stuffs it into a "StringBuilder" object (I imagine Python may have something similar), thus assembling the entire file in memory, then converts it to a String and writes the text to the output.

Once someone downloads the file, they can open it in Excel (whatever version) and save it as any version of Excel they want. They will not be able to edit the .csv file in Excel, but it should always open. You can look at its contents in detail using any text editor.

 

[FactChecker]

You'll want to check each cell's worth of data for commas, and if you surround each cell with ", you'll want to watch for and handle that in each data item also. It's not that hard to do--takes a bit of tinkering--and if you treat it like a plain text file and handle it yourself, it will continue to work no matter what version of libraries or Excel comes along in a few years.

If the cell might contain general text that may include commas and quotes, it gets messy.

 

[harborsparrow]

As noted, for comma-delimited quoted data cells, you'll have to do something about , and " in the actual data. There should be a way to "escape" those characters in Excel (as it is a database first and foremost). Once you know the escape sequence, it's a simple string substitution before writing data into a cell.

"cell data", "cell2 data", "cell3 data", "data with , is fine", "data with a quote "" requires that the quote be doubled", "another data item"

It's not that bad--just check each data item and escape any double quotes as "" first, THEN add it to the current line of text. Delimit as always. Each line starts and ends with " and "," goes between each data item.