MySQL CSV Data Import

How to import a CSV file with UTF-8 data

The given data contains UTF-8 encoded stuff with german umlauts (ä,ö,ü,…)

When I tried to import the CSV with phpmyadmin I always ended up with such funny output like Mühlhausen/Thüringen.

Sysadmin rule of thumb: if you can do it in the terminal, do it!

Here we go: move the CSV file into the database directory of the destination database. In my case that's /var/lib/mysql/geodb - We have to do that because LOAD DATA INFILE expects the file to be in the directory of the destination database. Don't ask me why it wouldn't work outside that path.

Then start your mysql terminal program.

USE geodb;
LOAD DATA INFILE 'PLZ.txt' INTO TABLE geo 
CHARACTER SET utf8 FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

And, surprise: everything is there as expected. Mission accomplished.

mysql/import.txt · Last modified: 2014-05 by tb
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0 ipv6 ready