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.I am just clicking data.csv to open the file.
I believe that there are methods using OLE to get the job done
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.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.
If the cell might contain general text that may include commas and quotes, it gets messy.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.