Writing data into a CSV file

  • Python
  • Thread starter Arman777
  • Start date
  • #27
rbelli1
Gold Member
995
379
I am just clicking data.csv to open the file.
This tells excel to load the text file into the first column of the spreadsheet rather than import the text as a spreadsheet. This is a spectacularly bad and frustrating mode of operation. I would consider it a bug. I usually use LibreOffice when working with text type spreadsheets as it imports by deafult when you open them and it also handles them better in my opinion. Pasting into it also works better than Excel.

Follow the instructions Ibix posted in #16 and you will be good to go.

BoB
 
  • #28
FactChecker
Science Advisor
Gold Member
6,266
2,434
I always hesitate to recommend a complete change of tools/language used because I believe that is usually not the answer asked for or desired. I have not investigated a solution, but I believe that there are methods using OLE to get the job done. I did determine to my satisfaction that there are no command-line flags that could be set to tell EXCEL to separate the data at commas.
 
  • #29
PeterDonis
Mentor
Insights Author
2020 Award
34,229
12,497
I believe that there are methods using OLE to get the job done

Since the starting point is a Python program, OLE, if Python libraries for it even exist, would be at least as much of a change of tools as pandas. For one thing, if Excel is going to use OLE to grab the file data, it will expect to do it from an OLE server, which means the Python program would have to be a server, not just a script. That opens a whole new can of worms that is much better left alone. If pandas can simply write the data as an xlsx file, that would be a lot simpler.
 
  • Like
Likes FactChecker
  • #30
FactChecker
Science Advisor
Gold Member
6,266
2,434
Since the starting point is a Python program, OLE, if Python libraries for it even exist, would be at least as much of a change of tools as pandas. For one thing, if Excel is going to use OLE to grab the file data, it will expect to do it from an OLE server, which means the Python program would have to be a server, not just a script. That opens a whole new can of worms that is much better left alone. If pandas can simply write the data as an xlsx file, that would be a lot simpler.
That is a good point, and I think it is one possible trade-off that needs to be discussed when one recommends switching to a different language or toolset. For instance, if there were command-line switches that would make EXCEL behave correctly, it would be a relatively simple solution. I think EXCEL does not have those switches. There might be other options that would be simple to implement without switching away from EXCEL. I did all my work in Perl, which had convenient methods for writing directly to EXCEL spreadsheets. I would be surprised if Python did not have similar methods. This looks fairly convenient.
 
  • #31
PeterDonis
Mentor
Insights Author
2020 Award
34,229
12,497
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.
 
  • Like
Likes FactChecker
  • #32
FactChecker
Science Advisor
Gold Member
6,266
2,434
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.
 
  • #33
harborsparrow
Gold Member
611
154
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.
 
Last edited:
  • #34
FactChecker
Science Advisor
Gold Member
6,266
2,434
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.
 
  • #35
harborsparrow
Gold Member
611
154
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.
 
Last edited:
  • Like
Likes FactChecker

Related Threads on Writing data into a CSV file

Replies
1
Views
190
  • Last Post
Replies
9
Views
4K
  • Last Post
Replies
2
Views
2K
Replies
3
Views
2K
Replies
9
Views
16K
Replies
6
Views
9K
Replies
2
Views
1K
  • Last Post
Replies
2
Views
3K
Replies
2
Views
20K
Replies
1
Views
5K
Top