I used Python to generate a CSV file. But when I open it in Excel, Excel will auto recognize a string into a number if it could be converted.
e.g.33E105 becomes 33*10^105, which is actually an ID, not a number.
How to disable this in Excel while opening a CSV file? Or I need to resort to a excel-python library to output a excel file and specify the format myself?
I also found a similar question without good answers on the web.
Thanks!
I think you can put a single ' in front to stop numeric strings being turned into ints
Eg.
...,"'33E105",...
OpenXML is a more flexible (and complicated) option, but only works with Office2003 or newer and is overkill for this problem. More suited to cases when you need highlighting of cells/multiple sheets, etc.
You could precede it with a single quote, forcing it to text.
A fun answer is you could keep the first eight rows blank (it only processes the first eight rows to determine data type), although I think this may blank all your data entirely. You could hide those empty rows.
You have 3 options:
Output a specially formatted XML file that Excel can read. I don't recall any details but if interested, I can dig them up. We did that with our Perl code. I don't know if excel-python does just that.
Place ' (apostrophe) before each string.
Use a library which outputs native Excel file with proper formatting. No clue if such exists for Python, but it is kind of an overkill variant of the first option on any case.
If you dont' want to change how you're writing the CSV file you can use the Text Import Wizard in Excel to specify how you want the individual columns to be treated. There is one step in the wizard that lets you select what type of data is in each column. Set the column(s) you want to Text and it will treat it as a string.
To write actual .xls files in Python, rather than .csv ones that Excel might misinterpret, you can use the xlwt third-party Python package.