Characters showing weird in HeidiSql but fine in PhpMyAdmin

User, date Message
Written by Kembreg
3 years ago
Category: Import/Export
19 posts since Tue, 22 Jun 10 		I saved a file from Excel as Unicode Txt.
I then used HeidiSql to import it to MySql.

The result showing in HeidiSql is box-letter-box-letter.
It looks fine, however, in PhpMyAdmin.

Here are 2 images showing what I see:

www.linguashop.com/heidisql/

Any help anyone could offer would be much appreciated.

David
Written by ansgar
3 years ago
4026 posts since Fri, 07 Apr 06 		I can only guess you didn't select the right charset when importing the file in HeidiSQL? No clue. You could post your

SHOW CREATE TABLE phrases

result here and attach the CSV file somewhere. Ah, and please update your heidisql.exe - it's more than 100 revisions behind the latest build.
Written by Kembreg
3 years ago
19 posts since Tue, 22 Jun 10 		The csv file is here:
www.linguashop.com/heidisql

Show Table here:
CREATE TABLE `phrases` (
`Pindex` int(5) DEFAULT NULL,
...
...
ENGINE=MyISAM DEFAULT CHARSET=utf8
Written by Kembreg
3 years ago
19 posts since Tue, 22 Jun 10 		By the way:

I downloaded HeihiSql yesterday from your site.

http://www.heidisql.com/download.php by clicking "Installer".

That means that link is 100 revisions old.
Written by ansgar
3 years ago
4026 posts since Fri, 07 Apr 06 		Yes, the official releases often tend to get old very soon.

The file has UCS-2 Little Endian format, and I did not succeed importing that file using the ucs2 character set option:

LOAD DATA LOCAL INFILE 'D:\\temp\\phrases.txt' INTO TABLE `xyz`.`phrases`
CHARACTER SET ucs2
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(`Pindex`,`num`,`text`);


0 rows affected, where it should have inserted 929 rows.

Converting the file with Notepad++ to UTF-8 did the trick, and rows look fine:

LOAD DATA LOCAL INFILE 'D:\\temp\\phrases.txt' INTO TABLE `xyz`.`phrases`
CHARACTER SET utf8
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(`Pindex`,`num`,`text`);
 

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