decimal separator conversion

Hi,

My version of excel uses "," (comma) as decimal separator, but mysql uses "." (dot)

With the old version of HeidiSQL i could copy as CSV (tab separated) and paste directly into excel, conversion from dot to comma was handled automatically. This no longer happens, so I have to search-replace before pasting into my spreadsheets.

Is this a bug or feature? Are there workarounds? Couldnt find anything in the settings.

Thanks for your help!

Don't think there is a workaround. I think HeidiSQL should use the local settings for decimal and thousands seperator. Only I think we had that before and that was the reason for a different bug, can't recall it right now.

Don't think there is a workaround. I think HeidiSQL should use the local settings for decimal and thousands seperator. Only I think we had that before and that was the reason for a different bug, can't recall it right now.

I *really hope* someone can fix this, because I paste query results directly into excel all the time! If not, I think I'll have to downgrade HeidiSQL to an older version.

What should always work is to save the grid data to a .csv file and open that in Excel. Probably this is a workaround for you.

If Heidi uses local settings, these can be changed in your control panel - go Start -> Control Panel -> Regional & Language Options (may be in slightly different place for different start/control panel layouts, in vista etc). The 'Customize' button on this window lets you specify which chars to use for thousands/decimal/negative etc.

What should always work is to save the grid data to a .csv file and open that in Excel. Probably this is a workaround for you.

It's a workaround, but the problem is that I do this so often (20x a day) so saving-importing each time would be very inconvenient.

The strange thing is that HeidiSQL shows the results in the grid using the *correct* separator, comma - which is my regional setting). Could someone please let me know why this *changes* when copying, and why it has to be so?

Thanks!

Can confirm that. And then again, I cannot get the copied data at all comfortable into Excel 2007 because it ignores any field separator, showing a complete row in a single cell. What would help me the right decimal separator in that situation?

I recall there was a problem when copying a comma-separated float value into Excel - the comma was misinterpreted as field separator, so I guess we changed that intentioanally into the US layout with the dot.

Can you describe exactly how we'd have to copy fields and rows so they can be successfully pasted into Excel? I'm willing to enhance this stuff but need some advice on how Excel works I think.

See also some related stuff from Microsoft cited in issue #709.

This is the bug I fixed by using the dot for CSV copying. The changes from Aug 08.

Can confirm that. And then again, I cannot get the copied data at all comfortable into Excel 2007 because it ignores any field separator, showing a complete row in a single cell. What would help me the right decimal separator in that situation?

I've set field separator to \t (tab) and removed field enclosing (quotes). This makes it easy to paste into excel.

Otherwise I use import functions to open csv files, it seems newer versions of Excel do not convert automatically on open(?)

Perhaps it should be possible to choose between locale-specific and mysql default decimal separator?

Ah yes, tab (\t) works generally when pasting into Excel.

And then I found another workaround for this problem: Before pasting, tell Excel to format all cells in a "Text" manner, so it doesn't convert floats to date/time values. Obviously an ugly workaround.

In order to keep the import functionality we'd have to stick to the dot. In order to be able to paste into Excel we'd have to use the local settings. We cannot do both. That means we'd have to ask the user for decisions before exporting CSV data. Or add another preference option which is probably too hidden, a dialog would be more user friendly.

A dialog could help erase the export settings out of the preferences dialog, so that sounds nice.

If you really feel that's worth it, could you please file a bug report ? Thanks!

Before pasting, tell Excel to format all cells in a "Text" manner, so it doesn't convert floats to date/time values. Obviously an ugly workaround.

That would create more problems than it would solve I am afraid...!

Would it not be possible to use locale settings for both import and export and then solve all problems? When I export to CSV from *Excel*, I get commas as decimal separators, and semicolons as field separators, and so obviously Excel assumes regional settings in all operations. HeidiSQL should do the same, if possible(?)

Would it not be possible to do the same in HeidiSQL - export to whatever format the locale expects (in my case comma as decimal separator). When importing to mysql, parse with regional settings (comma), then "switch locale" and INSERT with the settings mysql expects (dot).

Then, if a user have CSVs in non-locale formats, keep an option in the settings to override regonal preferences, along with the "\t" options etc.

Keep up the good work! :)

Would it not be possible to use locale settings for both import and export and then solve all problems? When I export to CSV from *Excel*, I get commas as decimal separators, and semicolons as field separators, and so obviously Excel assumes regional settings in all operations. HeidiSQL should do the same, if possible(?)

CSV is a format for data exchange. The guy at Microsoft who thought that it would be a good idea to use regional settings when creating CSV files deserves being spanked in Redmond's main square. And all the other guys who agreed with him and allowed it to be actually implemented deserve eternal pain in the deepest cellar of Hell.

That's only my humble opinion, of course.

CSV is a format for data exchange. The guy at Microsoft who thought that it would be a good idea to use regional settings when creating CSV files deserves being spanked in Redmond's main square. And all the other guys who agreed with him and allowed it to be actually implemented deserve eternal pain in the deepest cellar of Hell..

I get your point, I partially agree (although text/csv is not a standardized format, so there will always be context-dependent import/export of that kind of data)

but the thing is that CSV (or really TSV) data is good for pasting into Excel, and Excel will have regional settings for decimal separators, so in order to have smooth paste operations (really important IMO) HeidiSQL needs to be able to confirm to the regional setup.

Sure, the only point is that we cannot do both: Keep compatibility to MySQL's LOAD DATA command and Excel . I would only accept a solution which doesn't break other features.

Would it not be possible to use locale settings for both import and export and then solve all problems? When I export to CSV from *Excel*, I get commas as decimal separators, and semicolons as field separators, and so obviously Excel assumes regional settings in all operations. HeidiSQL should do the same, if possible(?)

CSV is a format for data exchange. The guy at Microsoft who thought that it would be a good idea to use regional settings when creating CSV files deserves being spanked in Redmond's main square. And all the other guys who agreed with him and allowed it to be actually implemented deserve eternal pain in the deepest cellar of Hell.

That's only my humble opinion, of course.

Presumably in the same fiery cauldron ad the date handling team. :D

Sure, the only point is that we cannot do both: Keep compatibility to MySQL's LOAD DATA command and Excel . I would only accept a solution which doesn't break other features.

OK, I think a checkbox in the preferences (mysql/LOAD DATA format vs localized/Microsoft/Office-specific) would do.

Your work is much appreciated :)

(...just installed HeidiSQL 5...found locale CSV support)

THANKS!!

Nice to have won back a potentially lost user :)

Btw, the text import dialog now also has a new checkbox called "Input file contains local formatted numbers", which auto-fixes numbers so they are imported correctly.

Please, Can you let me know the HeidiSQl version.
Because I think I have different version now.

Just the latest builds have this - see Help > Check for updates.

Could you please change import csv procedure to accept any decimal separator, thousands separator and date format. Current behaviour which reads system settings is not enough, e.g., I have american settings, because of the stupid software, but I have text files supplied by the accounting software which have different separators. I would suggest to show current separators in the "Import text file" window and allow the user to change these. I would make changes myself, but I don't have Delphi.

That would mean:
1. I remove the checkbox "Input file contains local formatted..."
2. I'll add two input boxes in the "Control characters" fieldset, for thousands and decimal separator, which default to ISO settings
3. Well, 2. would blow the dialog up again, making it necessairy to use separate tabs for input, settings and destination (or whatever).

However, this is just a thought. If you expect this to be implemented you will need to file a report in the issue tracker, including these ideas.

point (2): could be fine to have 2 input boxes filled with defaults OnFormCreate(), regarding date format it could be a separate input box too. I think it is not necessary to show one dialog for the user, unless you had something different in mind.

What do you mean by "to show one dialog for the user"? You mean the tabs?

I misunderstood, sorry. I would put additional controls (decimal separator, thousands separator and date format) in place of the checkbox "input file contains local..."

Yes, that was my idea, only without the date format. The crux is that the whole dialog is filled with controls and will need tabs when I do that. So it's quite more work.. hmm. But I like the idea in general. Why did you hijack this old post btw?

Our company has support contracts for ERP systems and sometimes I need to import tab separated files into mysql to run some queries. Text files come in a variety of thousand separators (space, apostroph, comma) and date formats, though the most frequent formats are yyyy-mm-dd and yyyy.mm.dd. Currently I am using a BI tool to import text files into DB. BI tool allows to specify different formats for each column and I have to change each column settings when I get a differently formatted file.