csv import from unicode to utf8 phpmyadmin

Question

I want to import some Chinese Characters into the MySQL database. But some of the content is omitted, and some is showing non-sense. With the pictures shown below:

What's in the database:

enter image description here

What's in the excel file:

enter image description here

Thank you!

Hi, here is the addtional information:

text document

巴掌  bāzhang (a slap of the) palm     打  beat    human activities
巴掌  bāzhang (a slap of the) palm     搧  spank   human activities
巴掌  bāzhang (a slap of the) palm     揍  hit human activities
把   bá  tools and objects with a handle 扫帚  broom   tools
把   bá  tools and objects with a handle 锁   lock    man-made

show create table

CREATE TABLE `table 1` (
 `CL_in_Character` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
 `CL_in_Pinyin` varchar(14) CHARACTER SET utf8 DEFAULT NULL,
 `Definition_in_Dictionary` varchar(74) CHARACTER SET utf8 DEFAULT NULL,
 `Associated_nouns_in_Chinese` varchar(16) CHARACTER SET utf8 DEFAULT NULL,
 `Associated_nouns_in_English` varchar(38) CHARACTER SET utf8 DEFAULT NULL,
 `Associated_noun_categories` varchar(38) CHARACTER SET utf8 DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Show the definition of your table. Execute `SHOW CREATE TABLE ` and post results in your question. Also post a few lines of your file in textual form (not an image). — peterm, Jan 20 '14 at 22:51
Is your file being read and inserted as utf8? Is phpMyAdmin displaying in utf8? If you insert one Chinese character into MySQL, can it be displayed correctly? — Tan Hong Tat, Jan 21 '14 at 01:57
if i upload using open document spreadsheet, one of the column of chinese char can be displayed, but one of the column of the char is omitted. yes the file is inserted as utf8. — Soya, Jan 21 '14 at 02:02

score 2 · Accepted Answer · answered Jan 21 '14 at 03:47

I can't tell about open document format and phpmyadmin, but you can successfully import your data properly formatted as CSV with

LOAD DATA INFILE '/path/to/your/file.csv' INTO TABLE `table 1`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

By properly formatted as CSV I mean

CL_in_Character,CL_in_Pinyin,Definition_in_Dictionary,Associated_nouns_in_Chinese,Associated_nouns_in_English,Associated_noun_categories
"巴掌","bāzhang","(a slap of the) palm","打","beat","human activities"
"巴掌","bāzhang","(a slap of the) palm","搧","spank","human activities"
"巴掌","bāzhang","(a slap of the) palm","揍","hit","human activities"
"把","bá","tools and objects with a handle","扫帚","broom","tools"
"把","bá","tools and objects with a handle","锁","lock","man-made"

Let's try it

mysql> CREATE TABLE `table 1` (
    ->  `CL_in_Character` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
    ->  `CL_in_Pinyin` varchar(14) CHARACTER SET utf8 DEFAULT NULL,
    ->  `Definition_in_Dictionary` varchar(74) CHARACTER SET utf8 DEFAULT NULL,
    ->  `Associated_nouns_in_Chinese` varchar(16) CHARACTER SET utf8 DEFAULT NULL,
    ->  `Associated_nouns_in_English` varchar(38) CHARACTER SET utf8 DEFAULT NULL,
    ->  `Associated_noun_categories` varchar(38) CHARACTER SET utf8 DEFAULT NULL
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql> LOAD DATA INFILE '/tmp/utf.csv' INTO TABLE `table 1`
    -> CHARACTER SET 'utf8'
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from `table 1`;
+-----------------+--------------+---------------------------------+-----------------------------+-----------------------------+----------------------------+
| CL_in_Character | CL_in_Pinyin | Definition_in_Dictionary        | Associated_nouns_in_Chinese | Associated_nouns_in_English | Associated_noun_categories |
+-----------------+--------------+---------------------------------+-----------------------------+-----------------------------+----------------------------+
| 巴掌            | bāzhang      | (a slap of the) palm            | 打                          | beat                        | human activities           |
| 巴掌            | bāzhang      | (a slap of the) palm            | 搧                          | spank                       | human activities           |
| 巴掌            | bāzhang      | (a slap of the) palm            | 揍                          | hit                         | human activities           |
| 把              | bá           | tools and objects with a handle | 扫帚                        | broom                       | tools                      |
| 把              | bá           | tools and objects with a handle | 锁                          | lock                        | man-made                   |
+-----------------+--------------+---------------------------------+-----------------------------+-----------------------------+----------------------------+
5 rows in set (0.00 sec)

It did help me. Just one thing: LINES TERMINATED BY '\n', should be used with '\r\n' linefeed (in case of using odf) — Jeffz, Aug 04 '14 at 11:12

score 0 · Answer 2 · answered Oct 09 '14 at 08:20

0

I've just had the same problem as you and reached this question. Then I've solved it by changing the option in phpmyadmin from

Lines terminated with: auto

to

Lines terminated with: \n

answered Oct 09 '14 at 08:20

haibuihoang

171
10

score 0 · Answer 3 · answered Jun 30 '16 at 09:24

0

I solved this by copying the whole excel data, and paste as value, then it will recognize the character.

answered Jun 30 '16 at 09:24

Soya

115
1
3
13

csv import from unicode to utf8 phpmyadmin

3 Answers3

Linked