csv and Excel encoding

Hi,

I often use Excel for sending queries results to others.
But when exporting from Heidi to csv file and opening it in Excel (2010) i get wrong encoding.

My table is filled with UTF-8 data.

There is an option in Excel to import file - in this way I can choose encoding (ex. UTF-8), but it is much more uncomfortable then just opening Excel file.

Can anything be done?

Regards, Galanty

I believe that the simplest method is to open the dump in a text editor (even Notepad will do) and save it as ANSI.

It's working, but i think that Heidi people should implement excel file format export.

It's easy to do and it will bring a lot of benefits.

I agree and it's been suggested before.

Whatever, good old Mysql-Front used to have a broken though practical ODBC import/export feature. I don't know if ODBC is still a mainstream technology but it has the benefit of being more generic since you have ODBC drivers for almost any data source, including Excel.

If you have UTF-8 data, you probably break the contents when converting to ANSI, right? Heidi is a pure Unicode tool, and Excel offers to select the file encoding when you use the data importer. Another option is to set the right control chars in Tools > Preferences > Export and use the "Copy as CSV" feature in HeidiSQL and paste it in Excel. You just have to set \t as the field separator. But the good thing is here that Excel suddenly has no problem with Unicode data in your clipboard.

kalvaro:
ODBC is a cool thing. I'm using it for predefined rapports.
But while working with mysql by hand I prefer my favourite editor :)

anse: Copy as CSV is not bad solution for this problem, thx :)

Meanwhile I'm still voting for excel export!

Anse: when converting to ANSI you won't break anything if your DB only contains data written in your language since it'll normally match your computer's code page. Of course, that's not something HeidiSQL can dare to guess, thus Galanty's suggestion to export to native Excel format.

I'd particularly prefer ODBC :)

See issue #1499 for enhancements on this feature.

ANSI + Excel TSV is now selectable in the new export dialog. (r3796)

Encoding select is fine and working good, but
If I will save file as csv, excel won't open it correctly (without importing file)
If I will save ( manualy ext. change! ) as .tsv, and then manualy assign excel to this file types - it's working. But this is still very complicated solution.

We need xls(x) export :)

Hehe, that won't happen I think. You should be more detailed when you say "excel won't open it correctly (without importing file)". I can probably fix that whatever you mean.

I mean that, while opening with double click on the file(csv) Excel don't recognize that fields are tabs separated.

All data are in one column.

... and you selected "Excel TSV" format in HeidiSQL?

Yes, and I have saved file as csv because there was no other way.

Excel 2010.

Confirmed. Excel is so weird - opening a file via doubleclick expects semicolon as separator, pasting requires tabs. I could change this so that the file option uses semicolon automatically instead of tabs. And I should rename the output format when you select file output, to "Excel, semicolon separated".

Great!

Done in r3798

I just wonder about my Excel not accepting the comma in a csv file as a separator - only works when I use semicolon. Does comma really work for you, Galanty?

Anse, this is one of those "WTF were they thinking" cases. The folks who wrote Excel decided that the correct separator in a "*comma* separated values" file has to be locale-dependent: a comma on an anglo-saxon PC and a semicolon on an italian one, I found out. I guess your PC speaks German?

Yes, german. I'll try to find a mapping list somewhere on the net with locale => separator, hopefully there is one. I guess not only the Italian version has semicolon or?

Ah, just found this explanation on the Wiki page:

depending on the system's regional settings, it may expect a semicolon as a separator instead of a comma, since in some languages the comma is used as the decimal separator

So, the solution in Heidi should be to use comma by default and semicolon if decimalseparator is comma (which is the case in Germany).

Done in r3813

There is a sort of spec:

http://www.rfc-editor.org/rfc/rfc4180.txt

Of course, it's not clear whether Microsoft Excel cares about it and Excel behaviour is what actually matters to almost everyone.

I also wonder if I should add some input for the user to pass a value used for NULLs. But I highly guess I should just use blank strings. Then again, if you import CSV files into MySQL again, a "NULL" string will be imported as NULL value. Hm