user2870092 user2870092 - 5 months ago 18
MySQL Question

csv import from unicode to utf8 phpmyadmin

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

Answer

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)