Home > MySQL > CSV et MySQL : SELECT INTO OUTFILE et LOAD DATA INFILE

CSV et MySQL : SELECT INTO OUTFILE et LOAD DATA INFILE

J’ai eu à me pencher sur les imports-exports sous MySQL. Mon but était de disposer de fichiers utilisables dans un format “humain” (comprendre: que les gens du marketing pouvaient exploiter avec leur cher Excel) sans pour autant passer par des scripts de conversion hasardeux et lourds pour le serveur. Il a donc fallu que je cherche les meilleures solutions pour pouvoir générer et importer des fichiers CSV dans MySQL,mon SGBDR favori. J’ai dû me servir du couple SELECT INTO OUTFILE pour les exports, et LOAD DATA INFILE pour les imports. Petit rappel syntaxique.

Exports CSV avec MySQL : SELECT INTO OUTFILE

Le principe de SELECT INTO OUTFILE est simple: réaliser un export de données en écrivant un resultset (résultats d’exécution d’une requête) directement dans un fichier CSV sur le serveur. Pour cela, l’utilisateur avec lequel vous vous connectez à MySQL doit avoir le priilège “FILE”. Autre remarque, vous ne pourrez en aucun cas écraser un fichier déjà existant sur le serveur (ceci pour la simple et bonne raison qu’il serait assez dommageable d’écraser, par exemple, votre fichier /etc/passwd).

Voici donc la syntaxe, finalement assez simple, de la fonction SELECT INTO OUTFILE :

  1. SELECT champ
  2. FROM TABLE
  3. WHERE champ = ‘valeur cherchée’
  4. INTO OUTFILE ‘/var/dump.csv’
  5. FIELDS
  6.     TERMINATED BY ‘;’
  7.     OPTIONALLY ENCLOSED BY ‘"’

Dans le fichier exporté, les champs ne sont pas délimités, sauf si vous utilisez FIELDS ENCLOSED BY. Le OPTIONALLY spécifie que seules les chaînes de caractères doivent être encadrées.
Le délimiteur par défaut est l’espace. Pour en utiliser un autre (virgule, point-virgule, tabulation, …) il faut utiliser la directive FIELDS TERMINATED BY. Evidemment, on peut utiliser des caractères spéciaux comme la tabulation ‘t’ ou le retour à la ligne ‘n’ (éventuellement CR+LF sous Windows, donc ‘rn’). Il existe aussi LINES TERMINATED BY pour contrôler le caractère de fin de ligne.

Du coup, en lançant sur la base de mon blog cette requête :

  1. mysql> SELECT ID,post_title,comment_count
  2. FROM `wp_posts`
  3. WHERE `post_status` = ‘publish’
  4.  ORDER BY `post_date` DESC LIMIT 3
  5. INTO OUTFILE ‘/tmp/blog_posts.dump’
  6. FIELDS
  7. TERMINATED BY ‘;’
  8. OPTIONALLY ENCLOSED BY ‘"’;

j’ai pu récupérer un fichier plat qui donnait :

  1. 179;"plugin : yURL ReTwitt";1
  2. 22;"5 plugins indispensables pour coder en PHP avec l’IDE Eclipse";0
  3. 138;"plugin : wp_list_sub_pages()";0

Soit, un joli fichier CSV bien propre, directement exploitable (pourquoi pas par Excel).

Imports de CSV dans MySQL : LOAD DATA INFILE

Le LOAD DATA INFILE, qui permet de faire l’exact inverse du INTO OUTFILE, est tout aussi simple à utiliser. Dans les bonnes conditions, c’est vraiment l’un des outils d’import MySQL les plus puissants.
Déjà, bonne nouvelle, la syntaxe des commandes qui permet à LOAD DATA INFILE de repérer les débuts et fin de champs (et de ligne) est la même que pour SELECT INTO OUTFILE. On retrouve donc sans surprise les FIELDS TERMINATED BY et autres joyeusetés.
Une gestion des doublons est aussi possible grâce aux mots-clefs IGNORE et REPLACE, qui parlent d’eux-même. Déclenchés en cas de doublon dans une clé (primaire ou unique), REPLACE effacera l’ancienne ligne pour la remplacer par la nouvelle. Attention donc, vous perdrez donc la pérennité de vos ID puisque ceux-ci changeront lors de l’import de données. IGNORE permettra simplement de conserver l’ancienne ligne, les nouvelles données n’étant pas écrites: vous conservez vos ID mais perdez le bénéfice de l’import sur cette ligne.
Vous pouvez aussi spécifier la liste des champs dans lesquelles les données doivent être stockées avec la syntaxe classique “(champ1, champ2, champ3)” (sans guillemets) en fin de commande.
Pour prendre un exemple, avec un fichier de la forme :

  1. 1;120;"texte1";
  2. 2;240;"texte2";

En imaginant qu’on ne veut garder que l’id (champ 1) et le texte (champ 3), et les insérer dans les champs correspondants de la table SQL “data”, on peut utiliser le paramètre @dummy pour demander au serveur d’ignorer l’un des champs du CSV. Cela nous donne une requête de la forme :

  1. LOAD DATA INFILE ‘/tmp/data.csv’
  2. INTO TABLE `data`
  3. FIELDS
  4. TERMINATED BY ‘;’
  5. OPTIONALLY ENCLOSED BY ‘"’
  6. (id, @dummy, texte)

Et si par malheur votre fichier commence par 2 lignes d’entête, pas de souci, vous pouvez présicer IGNORE 2 LINES dans la requête pour que soient ignorées les 2 premières lignes.

Et voila. Deux commandes, finalement pas bien complexes une fois qu’on les a prises en main, qui permettent de gérer efficacement les imports/exports de fichiers CSV sous MySQL. Pour faire suite à cet article, nous verrons bientôt comment optimiser la vitesse de vos INSERT dans MySQL.

Ce post vous a été utile ? Re-Twittez le ! ReTwittez ce post

MySQL , , , , , ,

  1. Pierrick
    | #1

    Bonjour,

    J’ai testé la méthode d’importation en utilisant votre exemple.
    Hors je ne comprend pas comment vous précisez d’insérer les champs 1 et 3.
    Chez moi il insère les champs 1 et 2.

    Il manque peut-être des paramètres ?

  2. | #2

    Bonjour Pierrick, en effet un petit oubli s’est glissé dans la requête (utilisation de @dummy). C’est corrigé, merci !

  3. boby
    | #3

    Bonjour,

    La manip INTO OUTFILE est claire et fonctionne très bien…mais comment faire pour inclure dans le fichier sortant le nom des champs en première ligne?
    Merci

  1. No trackbacks yet.