MySQL et la ligne de commande


Importer, exporter des données depuis ou vers un fichier texte

Montre comment exporter le résultat d’une requête vers un fichier texte délimité et inversement comment charger dans une table le contenu d’un fichier texte.

Nous avons souvent besoin d’échanger des données entre une base de données et des outils bureautiques comme des tableurs. Le format d’échange sans doute le plus utilisé dans ce cas est le fichier texte délimité (ou format .csv). Il est donc intéressant de connaître les instructions MySQL correspondantes.


 Exporter le résultat d’une requête vers un fichier texte

MySQL ajoute quelques options à la commande SELECT de SQL   pour pouvoir exporter les résultat dans un fichier texte.

La syntaxe est la suivante :

SELECT col1, col2...
INTO OUTFILE 'fichier'
[ FIELDS
   [ TERMINATED BY '\t' ]
   [ [OPTIONNALY] ENCLOSED BY '' ]
   [ ESCAPED BY '\\' ]
]
[ LINES
   [ STARTING BY '' ]
   [ TERMINATED BY '\n' ]
]
FROM nom_table;

Les valeurs par défaut, lorsqu’une des options n’est pas précisée est :

Paramètrevaleur par défautRemarques
FIELDS TERMINATED BY ’\t’ tabulation comme séparateur de champ
ENCLOSED BY ’’ aucun caractère pour entourer les champs
ESCAPED BY ’\’ caractère d’échapement
LINES TERMINATED BY ’\n’ saut de ligne comme séparateur d’enregistrement
STARTING BY ’’ aucun caractère

Quelques remarques :

  • Comme nous utilisons une commande SELECT, nous ne sommes pas obligé de d’exporter le contenu exact d’une table mais tous résultat pouvant être généré par une requête comme par exemple, une jointure entre 2 tables ;
  • Le fichier d’export sera créé obligatoirement sur le serveur ; si vous désirez créer un export vers un client, utilisez plutôt le programme sqldump ou mysql -e "SELECT..." > fichier.
  • Pour des raisons de sécurité, vous ne pourrez pas créer un fichier d’export si un autre fichier portant le même nom existe déjà.
  • Vous devrez avoir les droit FILE sur le serveur.

Exporter avec les paramètres par défaut

Considérons la table suivante :

La commande suivante permet d’exporter le contenu de la table dans un fichier texte.

SELECT * FROM T_COMPOSITEURS_CMP
INTO OUTFILE '/tmp/compositeurs.txt';

ce qui donne le fichier suivant :

Exporter dans un fichier de type .csv

Souvent, nous utilisons des fichiers de type CSV. Ces derniers utilisent plutôt la , ou le ; comme séparateur de champ. Il suffit de le spécifier en modifiant la commande précédente :

SELECT * FROM T_COMPOSITEURS_CMP
INTO OUTFILE '/tmp/compositeurs.csv'
FIELDS TERMINATED BY ';';

ce qui donne le fichier suivant :

 Importer le contenu d’un fichier texte dans une table

La syntaxe générale

LOAD DATA [LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE 'fichier.txt'
 [REPLACE | IGNORE ]
 INTO TABLE nom_table
 [ FIELDS
   [ TERMINATED BY '\' ]
   [ [ OPTIONALLY ] ENCLOSED BY '' ]
   [ ESCAPED BY '\\' ]
 ]
 [ LINES TERMINATED BY '\n' ]
 [ IGNORE nb LINES ]
 [ ( col1, col2, ... ) ]

Les paramètres par défaut sont les mêmes que pour la commande SELECT décrite pour l’export.

LOCAL : Indique que le fichier se trouve sur une machine cliente et non sur le serveur

REPLACE : Indique que si une ligne du fichier à importer possède la même clé qu’un enregistrement existant, ce dernier sera mis à jour avec les nouvelles données.

IGNORE : Indique que si une ligne du fichier à importer possède la même clé qu’un enregistrement existant, la ligne sera ignorée et ne sera pas importée.

Chargement à partir d’un fichier ayant le format par défaut

C’est la syntaxe la plus simple : on prend en compte toutes les lignes et les champs sont séparés par des tabulations.

LOAD DATA INFILE 'fichier.txt' INTO TABLE nom_table;

Attention : Par défaut, le fichier à importer doit se trouver sur le serveur et mysql doit avoir les droits sur le fichier sinon vous aurez le message suivant :

Vous devrez donc modifier les droits comme suit :

sudo chown mysql:mysql compositeurs01.csv

Cas d’un fichier csv

Le plus souvent, le séparateur de champ n’est pas la tabulation mais peut être , ou ;. Il se peut également que la première ligne contienne les nom des champs et il ne faut donc pas l’importer.

Nous allons prendre comme exemple le fichier csv suivant :

Nous désirons importer ces données dans la table dont la structure est la suivante :

Quelques remarques :

  • la première ligne ne correspond pas à un enregistrement, il faudra donc spécifier qu’on l’ignore pour ne pas l’importer
  • le séparateur de champs n’est pas celui par défaut mais ;. Il faudra donc le spécifier
  • la première colonne correspondant à la clé n’existe pas dans le fichier texte car nous désirons que cette clé soit initialisée par le SGBD   (champ auto_increment). Nous devront donc spécifier le nom des champs à importer.

Ce qui va donner la commande suivante :

LOAD DATA INFILE "/tmp/compositeurs01.csv"
INTO TABLE T_COMPOSITEURS_CMP
FIELDS TERMINATED BY ";"
IGNORE 1 LINES
( CMP_NOM, CMP_PRENOM, CMP_NEE, CMP_DECEDE );

Après exécution de cette commande, nous pouvons vérifier le contenu de la table :

 Conclusion

Cet article vous a montré comment exporter des données entre une table et un fichier texte ainsi que l’inverse. La procédure est très pratique lorsque vous travaillez directement sur le serveur mais dans le cas ou vous procédez depuis un poste client vous devrez sans doute passer par l’utilitaire sqldump ou de passer par une interface WEB de type PhpMyAdmin.

Article n° 45

Crée par: chris

Créé le: 8 septembre 2015

Modifié le: 8 septembre 2015

Nombre de visites: 5659

Popularité: 26 %

Popularité absolue: 6

Mots clés de cet article


SPIP

2003-2019 LePpf
Plan du site | | Contact | RSS 2.0

Visiteurs connectés : 10

Nombre moyen de visites quotidiennes sur le site: 228