[Solved - sort of] 'Export Excel Data to New Table' not working on German Excel 2010

[Werner Karl]

When I try to export Excel data with mysql for excel, the export always fails on my German Excel 2010 for cells containing decimal numbers.

MySQL Error 1136: Column count doesn't match value count at row 1

The reason is that MySQL for Excel always uses the comma as the decimal delimiter, no matter what I configure Excel or Windows to use.

One Cell at row 1 contains a decimal number 17.25 represented as 17,25 in German locale. Thus MySQL sees one column more than field list in 'INSERT INTO' specifies.

I have tried:
- Setting Excel's decimal delimiter to '.' (dot). The number's representation on screen changes to 17.25 but the export fails with the very same result.
- Setting Windows' decimal delimiter to '.' (dot). The number's representation on screen changes to 17.25 but the export fails with the very same result.
- Setting the export's datatype to 'Double'with the very same result. (For exactness reasons I initially tried to export as 'Decimal(12, 2)'.

This seems like bug in MySQL for Excel to me. It seems to ignore Excel's and Windows' delimiter definitions.

The data preview, by the way, always shows the decimal numbers with the wrong delimiters, i.e. comma as decimal delimiter although Excel shows the dot as delimiter.

Any idea anyone?



Edited 1 time(s). Last edit at 06/17/2013 07:18AM by Werner Karl.