Import Results In Wrong Chars

[JAG-Fumbler]

I am trying to import some data from a .CSV in utf8 format but getting results i am not understanding.

The source uses the char 'ß' and in the table you see after the import 'ß'.

I can change the data manually in SQLyog and get the same char i am typing ('ß' => 'ß')

I am using SQLyog Enterprise 6.04 on a Windows XP SP2 PC with Excel 2003 SP2 and Notepad++ for the conversion.

By now i tried several conversions but without any progress an i have no idea what else i can do. I do not get any error or warning message displayed if i had one, is that a bug ?

May you can help me.

[peterlaursen]

too many words and too little test case!

Please attach a sample CSV file to reproduce with!

” … and Notepad++ for the conversion” Are you sure that this program handles UTF8 correctly?

1) Please avoid that program for the test case. Simply export as CSV from Excel, zip and attach here! Do not convert anything!

2) What kind of 'conversion' are you doing? and how??

3) If you want to convert the CSV-file from ANSI encodong to UTF8 you may use the 'ordinary' notepad (save as .. select UTF8 for the encoding). Also such file you can attach.

So three files please (and please zip):

1) A CSV file directly from Excel

2) File converted to UTF8 with the 'common' Notepad

3) File converted to UTF8 with Notepad++

(and 'convert' means 'save as …' and nothing else!)

[JAG-Fumbler]

Here you are.

You should know that i am connected to a MySQL database version 4.0.24-standard with a character set latin1 (i got this with show—>variables…) – may that helps.

Sorry if i had written in a unpolite form, may you can excuse me because of my bad english knowledge. I will try to find a more convenient way of communication.

Thank you very much!

[peterlaursen]

no no .. nothing like 'unpolite'.

But it is just much easir when people give a test case so that we can see ourselves what is happening!

[peterlaursen]

OK .. server version explains. MySQL 4.0x does not support unicode data with LOAD DATA INFILE. You cannot use utf8 encoded files with that server version.

With MySQL 4.1x and higher there is an option to specify the charset with LOAD data. We will support that from version 5.1 (surrently we asume that the file is utf8 encoded for those server version that support it, and when SQLyog generates a .csv from such MySQL version it is utf8 encoded)

[JAG-Fumbler]

peterlaursen wrote on Aug 7 2007, 10:05 AM:

OK .. server version explains. MySQL 4.0x does not support unicode data with LOAD DATA INFILE. You cannot use utf8 encoded files with that server version.

With MySQL 4.1x and higher there is an option to specify the charset with LOAD data. We will support that from version 5.1 (surrently we asume that the file is utf8 encoded for those server version that support it, and when SQLyog generates a .csv from such MySQL version it is utf8 encoded)

Now i migrate the databese to the stable version 5.0. but still can not use SQLyog to import data correctly.

just try the following query:

Code:

LOAD DATA LOCAL INFILE 'C:\data.csv'
CHARACTER SET utf8
INTO TABLE TB_AodText
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn';

results in:

Code:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8
INTO TABLE TB_AodText
FIELDS TERMINATED BY 't'
LIN' at line 2
(0 ms taken)

if i delete the line

Code:

CHARACTER SET utf8

i get a import but with the wrong character set

I also tried to use the mysql tool 'mysqlimport.exe'

Code:

mysqlimport.exe –local –default-character-set=utf8 –lines-terminated-by=”rn” –fields-terminated-by=”t” [DATABASENAME] -u [USERNAME] C:[TABLENAME].csv

and this works fine for me and i got the result i was expecting

a complete desciption for MySQL version 5.0 you can find here 12.2.5. LOAD DATA INFILE Syntax

is there a chance to fix that in SQLyog?

[peterlaursen]

The reason you get an error is that you are reversing the order or CHARACTER SET and INTO!

Table specification should come before character set.

We will support character set with csv import in the 5.1x code tree.

[JAG-Fumbler]

peterlaursen wrote on Aug 13 2007, 10:37 AM:

The reason you get an error is that you are reversing the order or CHARACTER SET and INTO!

Table specification should come before character set.

We will support character set with csv import in the 5.1x code tree.

<_< Ok, just for the archive. The following was tried on a MySQL 5.0.x stable version with SQLyog 6.04 Enterprise query:

Code:

LOAD DATA LOCAL INFILE 'D:\Profil\Jens\Desktop\TB_GroupText.csv'
INTO TABLE TB_GroupText
CHARACTER SET utf8
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn';

This results in:

Code:

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET utf8
FIELDS TERMINATED BY 't'
LINES TERMINATED BY 'rn'' at line 3
(0 ms taken)

For the LOAD DATA INFILE the MySQL manual allows the use of CHARACTER SET utf8 but the current version of SQLyog do not. In my point of view this is a bug of SQLyog. I am sorry, but i can not install a BETA version of MySQL because of this gap. It would be extremly helful to fix that as soon as possible.

[peterlaursen]

MySQL 4.0 does NOT allow for utf8 charset specification in LOAD DATA INFILE.

MySQL 4.0 simply does not recognize anything utf8-related!

But MySQL 5.0 does!

If you type the SQL in the editor yourself and you get an error returned it is not a SQLyog error.

It is a 'user error' or a 'server error'. SQLyog simply sends what you type .. what you also can check in the HISTORY tab.

[peterlaursen]

See attached. It works perfectly for me.

(MySQL 5.0.45 (latest stable COMMUNITY server), SQLyog 6.05 and 6.06 beta1)

The error you get is a server error . It is NOT AT ALL related to the client!

What is your exact MySQL version?

[peterlaursen]

And the file I imported was this one (zipped .csv)

[JAG-Fumbler]

I have version 5.0.19 standard of MySQL in production. 😮

And you are right, its not fixed in in this version. I have to patch to version 5.0.38. This is the part i have to say: Thank you very much! 🙄

MySQL BUG#15126 fixed

I have to ask my provider why he installed this old version…

[Author]

Posts