How do I import a CSV file with accented characters into MySQL

Question

I have a file.csv with 300,000 rows. Many of the rows are the names of foreign cities with accented characters. When I try to do an import in mysql, I get warnings on the fields, and it truncates the field at the point of the special character.

    LOAD DATA LOCAL INFILE '/var/tmp/geoip/location.csv' INTO TABLE Geolocation2 FIELDS TERMINATED BY ',' enclosed by '"' LINES TERMINATED BY '\n' (id, country, region, city, postalCode, latitude, longitude, metrocode, areacode );

score 2 · Answer 1 · edited May 23 '17 at 12:23

2

I had this problem with CSV files created by MS Excel. If you are using Excel, or need to convert CSV files to UTF-8 for importing into MySQL the answer here may be of help.

edited May 23 '17 at 12:23

Community

1
1

answered Apr 30 '15 at 01:13

Glenn Lawrence

2,844
1
32
38

score 2 · Answer 2 · answered Jul 06 '15 at 15:32

2

Open and SaveAs all your SQL Query and Data Files with UTF-8 encoding

This will solve BULK INSERT problems use option WITH (DATAFILETYPE = 'widenative')

It will also solve INSERT INTO Problems whether the data is in the same file as the CREATE TABLE instruction or chained :r "X:\Path\InsertIntoMyTable.sql"

answered Jul 06 '15 at 15:32

Naemoor

39
5

This was the answer for me. Thanks! – user460114 Dec 04 '17 at 18:53

score 1 · Accepted Answer · answered Jul 14 '11 at 03:36

1

You need to set the connection, database, table and column encodings to the same character set as the data was saved in the CSV file.

http://dev.mysql.com/doc/refman/5.0/en/charset.html

answered Jul 14 '11 at 03:36

Dan Grossman

51,866
10
112
101

How do i do that? I dont know the format of the data in the CSV. – Barry Chapman Jul 14 '11 at 03:42
Guess. Interpret it as some encoding and see if the text is not mangled. It's probably UTF-8. – Dan Grossman Jul 14 '11 at 04:23

score 0 · Answer 4 · answered Jul 26 '13 at 16:23

This sequence works to me.

create database {$databasename} DEFAULT CHARACTER SET latin1;
ALTER DATABASE {$databasename} DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci;
charset latin1;
load data infile '{$file.csv}' into table {$tablename} character set latin1 fields terminated by '|' enclosed by '"' lines terminated by '\n';

score -1 · Answer 5 · answered Aug 31 '15 at 04:36

-1

Tell MySQL what the codepage of the source file is when you import it. e.g. to import a file with codepage Windows-1252, use MySQL codepage latin1 (which is the same thing) like this:

LOAD DATA LOCAL INFILE '/path/to/file.csv'
  INTO TABLE imported_table
  CHARACTER SET 'latin1'
  COLUMNS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  ESCAPED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

answered Aug 31 '15 at 04:36

webaware

2,795
2
30
37

Setting the charset to `utf8mb4` on import worked for me. `LOAD DATA LOCAL INFILE 'my.csv' INTO TABLE geo_data CHARACTER SET 'utf8mb4' FIELDS TERMINATED BY ...`. – turrican_34 Sep 15 '20 at 12:27

How do I import a CSV file with accented characters into MySQL

5 Answers5