Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

csv and Excel encoding

Galanty's profile image Galanty posted 9 years ago in General Permalink
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
kalvaro's profile image kalvaro posted 9 years ago Permalink
I believe that the simplest method is to open the dump in a text editor (even Notepad will do) and save it as ANSI.
Galanty's profile image Galanty posted 9 years ago Permalink
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.
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
kalvaro's profile image kalvaro posted 9 years ago Permalink
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.
ansgar's profile image ansgar posted 9 years ago Permalink
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.
Galanty's profile image Galanty posted 9 years ago Permalink
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!
kalvaro's profile image kalvaro posted 9 years ago Permalink
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 :)
ansgar's profile image ansgar posted 9 years ago Permalink
See issue #1499 for enhancements on this feature.
ansgar's profile image ansgar posted 9 years ago Permalink
ANSI + Excel TSV is now selectable in the new export dialog. (r3796)
Galanty's profile image Galanty posted 9 years ago Permalink
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 :)
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 9 years ago Permalink
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.
Galanty's profile image Galanty posted 9 years ago Permalink
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.
ansgar's profile image ansgar posted 9 years ago Permalink
... and you selected "Excel TSV" format in HeidiSQL?
Galanty's profile image Galanty posted 9 years ago Permalink
Yes, and I have saved file as csv because there was no other way.

Excel 2010.
ansgar's profile image ansgar posted 9 years ago Permalink
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".
Galanty's profile image Galanty posted 9 years ago Permalink
Great!
Code modification/commit from ansgar.becker, 9 years ago, revision 3798
Excel tweak, see http://www.heidisql.com/forum.php?t=8226
ansgar's profile image ansgar posted 9 years ago Permalink
Done in r3798
ansgar's profile image ansgar posted 9 years ago Permalink
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?
djn's profile image djn posted 9 years ago Permalink
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?
ansgar's profile image ansgar posted 9 years ago Permalink
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?
ansgar's profile image ansgar posted 9 years ago Permalink
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).
Code modification/commit from ansgar.becker, 9 years ago, revision 3813
Use semicolon separator for Excel if system decimal separator is comma. See http://www.heidisql.com/forum.php?t=8226
ansgar's profile image ansgar posted 9 years ago Permalink
Done in r3813
kalvaro's profile image kalvaro posted 9 years ago Permalink
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.
ansgar's profile image ansgar posted 9 years ago Permalink
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.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.