Agence web » Actualités du digital » Comment exporter un fichier CSV à partir de la ligne de commande MySQL

Comment exporter un fichier CSV à partir de la ligne de commande MySQL

Les fichiers de valeurs séparées par des virgules (CSV) sont un moyen de transférer des données entre les applications. Les bases de données comme MySQL et les tableurs comme Excel prennent en charge l'importation et l'exportation via CSV, vous pouvez donc utiliser des fichiers CSV pour échanger des données entre les deux.

Les fichiers CSV sont en texte brut, ils sont donc naturellement légers et faciles à exporter depuis MySQL.

Depuis le serveur de base de données

Si vous avez accès au serveur sur lequel MySQL fonctionne, vous pouvez exporter une sélection avec le INTO OUTFILE commander.

SELECT id, column1, column2 FROM table
INTO OUTFILE '/tmp/mysqlfiles/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'

Cela produira un fichier CSV vers /tmp/mysqlfiles/table.csvou là où vous l'avez configuré. Vous devez vous assurer que l'utilisateur exécutant MySQL (généralement mysql ou root) a la propriété et un accès en écriture au répertoire.

Vous devrez également vous assurer que le secure_file_priv paramètre permet à MySQL d'accéder à ce répertoire. Par défaut, cela bloque l'accès en lecture et en écriture à partir des requêtes SQL. C'est une bonne chose; si votre code est vulnérable à l'injection SQL, tout attaquant potentiel n'aurait accès qu'à MySQL, et non au reste du système de fichiers.

Vous pouvez ajouter à la liste blanche des répertoires spécifiques en modifiant votre fichier de configuration MySQL (généralement situé à l'adresse /etc/my.cnf) inclure:

(mysqld)
secure-file-priv = "/tmp/mysqlfiles"

Ce qui permettra à MySQL de lire et d'écrire sur /tmp/mysqlfiles/ (que vous devrez créer avec mkdir). Une fois que MySQL peut exporter des fichiers, vous devriez pouvoir exécuter la requête et générer des fichiers CSV.

Avec le ENCLOSED BY , les virgules seront correctement échappées, par exemple:

"3","Escape, this","also, this"

Que vous pouvez prendre et importer directement dans n'importe quel tableur ou autre logiciel.

N'oubliez pas que le fichier CSV exporté ne comprend pas les en-têtes de colonne, mais les colonnes seront dans le même ordre que le SELECT déclaration. De plus, les valeurs nulles seront exportées en tant que N, qui est un comportement attendu, mais si vous souhaitez le modifier, vous pouvez modifier la sélection en encapsulant ifnull(field, "") autour de vos champs dans votre SELECT déclaration.

Depuis la ligne de commande MySQL

Si vous ne disposez que d'un accès en ligne de commande à l'instance MySQL, et non pas au serveur lui-même (comme lorsqu'il n'est pas géré par vous, dans le cas d'Amazon RDS), le problème est un peu plus délicat. Bien que vous puissiez utiliser FIELDS TERMINATED BY ',' sur le serveur pour générer une liste séparée par des virgules, la CLI MySQL se séparera avec des onglets par défaut.

Entrez simplement une requête à partir de la ligne de commande et dirigez-la vers un fichier:

mysql -u root -e "select * from database;" > output.tsv

Étant donné que la sortie MySQL est séparée par des tabulations, cela s'appelle un fichier TSV, pour les «valeurs séparées par des tabulations», et peut fonctionner à la place de votre fichier CSV dans certains programmes comme les importations de feuilles de calcul. Mais ce n'est pas un fichier CSV, et le convertir en un seul est compliqué.

Vous pouvez simplement remplacer chaque onglet par une virgule, ce qui fonctionnerait mais entraînerait son échec s'il y a des virgules dans les données d'entrée. Si vous êtes certain à 100% qu’il n’y a pas de virgule dans votre fichier TSV (consultez grep), vous pouvez remplacer les onglets par sed:

sed "s/t/,/g" output.tsv > output.csv

Mais si vous avez des virgules dans vos données, vous devrez utiliser une expression régulière beaucoup plus longue:

sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv

Cela échappera correctement aux champs avec des guillemets, ce qui résoudra le problème des virgules.

Remarque: le caractère de tabulation t n'est pas standard. Sur macOS et BSD, il n'est pas disponible, ce qui entraîne un gâchis de chaque «t» minuscule causant sed pour insérer des virgules erronées. Pour résoudre ce problème, vous devez utiliser un caractère de tabulation littéral à la place de t:

sed "s/  /,/g" output.tsv > output.csv

Si vos données d'entrée contiennent des onglets, vous n'avez pas de chance et vous devrez générer vous-même un fichier CSV avec un langage de script.

Faites-le manuellement avec un vrai langage de programmation

MySQL (et la plupart des bases de données) sont conçus pour interagir avec, donc vous avez probablement déjà une sorte de langage de programmation connecté à MySQL. La plupart des langues peuvent également écrire sur le disque, vous pouvez donc créer vos propres scripts de sortie CSV en lisant directement les champs de la base de données, en les échappant correctement et en écrivant un fichier délimité par des virgules.

Un exemple en Python.

★★★★★