How to disable Excel's auto recognition of numbers and text

Question

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!

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

To write actual .xls files in Python, rather than .csv ones that Excel might misinterpret, you can use the xlwt third-party Python package.