Importing bulk CSV data in UTF-8 into MySQL

mysql utf-8 csv load-data-infile
Tom picture Tom · Jan 25, 2010 · Viewed 12.5k times · Source

I'm trying to import about 10K rows of UTF-8 encoded data into a new MySQL table.

I can do so successfully with LOAD DATA INFILE via MySQL Workbench but it the UTF-8 characters get mangled. I've tested the database otherwise via PHP and it accepts stores UTF-8 charaters fine. The problem seems to be with LOAD DATA INFILE, and I've come across a few threads about this.

Does anyone know a workaround, or possibly another similarly easy method to import CSV data?

Thank you.

RESOLVED:

For others who see this and have the same problem, just add character set as a parameter when running LOAD DATA INFILE. Simpler than I realised :)

Answer

Miguel Ventura picture Miguel Ventura · Jan 25, 2010

According to MySQL documentation for LOAD DATA, you can specify a CHARACTER SET parameter in your statement to provide the input file character set.

Have you tried providing 'utf8' as the input character set?

Related questions

What's the difference between utf8_general_ci and utf8_unicode_ci?

Between utf8_general_ci and utf8_unicode_ci, are there any differences in terms of performance?

Read More
"Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC?

This is how my connection is set: Connection conn = DriverManager.getConnection(url + dbName + "?useUnicode=true&characterEncoding=utf-8", userName, password); And I'm getting the following error when tyring to add a row to a table: Incorrect string value: '\xF0\…

Read More
SET NAMES utf8 in MySQL?

I often see something similar to this below in PHP scripts using MySQL query("SET NAMES utf8"); I have never had to do this for any project yet so I have a couple basic questions about it. Is this something …

Read More