Sysop.Fr
Cat├ęgories

apache

awk

bash

charmap

date

echo

encoding

find

grep

hexdump

irc

iso3166

ls

mysql

printf

python

read

sed

seq

smpp

sort

tar

ucp

Articles

MySQL et les fichiers CSV

Pour authoriser l'utilisation de la commande LOAD DATA LOCAL INFILE... nous devons utiliser l'option --local-infile lors du lancement du client mysql

1. Importer un fichier CSV dans une table MySQL

Import d'un fichier utf8 unix (avec des fins de ligne en \n) avec une transformation du champs date.

root@ubuntu:~# cat file.csv
02-JAN-2012 05:15:02;Desmond;Miles
23-MAR-2012 15:23:52;Altaïr;Ibn La-Ahad
28-JUN-2012 09:35:11;Ezio;Auditore
01-OCT-2012 17:50:19;Connor;Kenway

root@ubuntu:~# mysql --socket=/var/run/mysqld/mysqld.sock --user=db_user --password --local-infile

mysql> USE `test`;
Database changed


mysql> CREATE TABLE IF NOT EXISTS `assassins` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `insertdate` DATETIME,
    `fisrtname` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    `lastname` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci, PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.12 sec)


mysql> LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE `assassins` CHARACTER SET utf8 FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' (@insertdate, fisrtname, lastname) SET insertdate=STR_TO_DATE(@insertdate, '%d-%b-%Y %k:%i:%s');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0


mysql> SELECT * FROM `assassins`;
+----+---------------------+-----------+-------------+
| id | insertdate          | fisrtname | lastname    |
+----+---------------------+-----------+-------------+
|  1 | 2012-01-02 05:15:02 | Desmond   | Miles       |
|  2 | 2012-03-23 15:23:52 | Altaïr    | Ibn La-Ahad |
|  3 | 2012-06-28 09:35:11 | Ezio      | Auditore    |
|  4 | 2012-10-01 17:50:19 | Connor    | Kenway      |
+----+---------------------+-----------+-------------+
4 rows in set (0.00 sec)
            

2. Exporter une table MySQL vers un fichier CSV