Excel Hidden Columns Show Twice Install A Python

This caters to Mac users, leaving Windows people in the lurch; and it doesnt answer the original question - an encoding that works on both platforms. Thanks.

I tried converting to UTF-8 with BOM; ExcelWin is fine with it, ExcelMac shows gibberish.

The field separator is comma, but semicolon doesnt change things.

Ill try this: mbconvertencoding(str, UTF-16LE); in my export code and post the results here.

Everything worked except UTF-16LE BOM (same result as yours on Windows) and UTF-16BE BOM (which parsed fields correctly but the BOM was included as the first 2 characters in A1).

Since its basically Microsofts own proprietary character set, one can assume it will work on both the Mac and the Windows version of MS-Excel.

Both versions at least include a corresponding File origin or File encoding selector which correctly reads the data.

Be advised that ISO-8859-1 is missing some characters from WINDOWS-1252 as shown here.

However, suppose you have a file queryresult.csv which you know is UTF-8 encoded.

In your case you would first have to know (or guess) the encoding used in your.csv file with european characters.

Then you can convert it to WINDOS-1252, which will most probably be correctly interpreted by both Mac and Windows Excel.

The reason it works is that Excel actually ends up using its Unicode.txt parser.

The problem now is that when you re-open the file Excel assumes its real CSV (with commas), sees that its not Unicode, so parses it as comma-delimited - and hence will make a hash of it.

Not being computer-savvy, the end user needs to open it in Excel without any hurdles.

I start my output string with ufeff as a byte order mark (BOM), then using t tabs in place of commas for field separation, and encoding the file with utf-16LE.

Opening the file by double-click loaded the file correctly, with special characters and line breaks within cells intact.

Downside: the sep, header is not recognized by any program except Excel as far as Ive seen.

But OpenOffice LibreOffice do not require this hack anyway (line-breaks in cell contents work just fine, whereas loading from text file using the text into columns assistant in Excel does not properly handle line breaks in cells).

If you had tried tab-separated CSV (also called TSV) you would have found the answer.

Thats a pity. It often means that you allow manual editing of TSV files, which probably is not a good idea.

Furthermore editors can be set to display a special character to mark tabs.

I have found a script made by Konrad Foerstner, which I have improved little bit by adding support for different delimiter characters.

Excel Hidden Columns Show Twice Install A Python

In order to run it you will need to install a python module openpyxl for Excel file manipulation: sudo easyinstall openpyxl.

The best proof for this is to make a file in Excel 2011 for MAC with special chars, save it as CSV and then open it in MAC text editor and the chars are scrambled.

This caters to Mac users, leaving Windows people in the lurch; and it doesnt answer the original question - an encoding that works on both platforms.

Excel For Mac Hidden Columns Show Twice