distal-attribute
distal-attribute
distal-attribute
distal-attribute

csv and Excel encoding

User, date Message
Written by Galanty
3 years ago
Category: General
7 posts since Thu, 14 Apr 11
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
Written by kalvaro
3 years ago
593 posts since Thu, 29 Nov 07
I believe that the simplest method is to open the dump in a text editor (even Notepad will do) and save it as ANSI.
Written by Galanty
3 years ago
7 posts since Thu, 14 Apr 11
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.
Written by kalvaro
3 years ago
593 posts since Thu, 29 Nov 07
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.
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
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.
Written by Galanty
3 years ago
7 posts since Thu, 14 Apr 11
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!
Written by kalvaro
3 years ago
593 posts since Thu, 29 Nov 07
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 :)
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
See issue #1499 for enhancements on this feature.
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
ANSI + Excel TSV is now selectable in the new export dialog. (r3796)
Written by Galanty
3 years ago
7 posts since Thu, 14 Apr 11
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 :)
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
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.
Written by Galanty
3 years ago
7 posts since Thu, 14 Apr 11
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.
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
... and you selected "Excel TSV" format in HeidiSQL?
Written by Galanty
3 years ago
7 posts since Thu, 14 Apr 11
Yes, and I have saved file as csv because there was no other way.

Excel 2010.
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
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".
Written by Galanty
3 years ago
7 posts since Thu, 14 Apr 11
Great!
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
Done in r3798
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
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?
Written by djn
3 years ago
15 posts since Wed, 19 Sep 07
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?
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
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?
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
Ah, just found this explanation on the Wiki page:

Wiki wrote: 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).
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
Done in r3813
Written by kalvaro
3 years ago
593 posts since Thu, 29 Nov 07
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.
Written by ansgar
3 years ago
4986 posts since Fri, 07 Apr 06
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
 

Please login to leave a reply, or register at first.