Comment exporter les résultats d'une requête MySQL vers Excel ?

Microsoft 365 & Office Excel & VBA
1

Mon besoin est de stocker l’ensemble des résultats de la requête

SELECT * FROM document
WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429)

dans un fichier Excel.

2

La méthode classique pour y parvenir est d’exporter vers CSV puis de charger le CSV dans Excel.

En résumé :

Pour un fichier CSV compatible Excel côté serveur à partir d’une requête SELECT, exécutez ceci :
`SELECT … FROM someTable WHERE etc
INTO OUTFILE ‘someTableExport.csv’ CHARACTER SET utf8mb4
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ ESCAPED BY ‘’
LINES TERMINATED BY ‘\r\n’;


-

Pour un fichier CSV compatible Excel **côté serveur**, utilisez `mysqldump` comme suit :

mysqldump -h serverHostName -u mysqlUserName -p --tab=“someTableExport.csv” --fields-optionally-enclosed-by=0x22 --fields-escaped-by=‘’ --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables table1 table2 table3


-

Pour un fichier CSV compatible Excel **côté client** en utilisant `mysqlsh` (MySQL Shell) comme suit :

mysqlsh --user=“mysqlUserName” --host=“serverHostName” --port=3306 --schema=“databaseName”

Une fois connecté, exécutez ceci :

util.exportTable(“tableName”, “file:///C:/Users/You/Desktop/test.csv”, { dialect: “csv”, fieldsEscapedBy: “”})


## D'abord, une mise en garde concernant Excel :

## Les formats CSV d'Excel vs. ceux de MySQL :

N'oubliez pas qu'Excel a ses propres idées peu documentées sur la manière dont les fichiers CSV doivent être formatés, et celles-ci contrastent avec [les propres idées de MySQL sur les fichiers CSV](https://dev.mysql.com/doc/refman/8.0/en/load-data.html) ; bien qu'Excel soit largement conforme [au RFC 4180](https://www.rfc-editor.org/rfc/rfc4180), vous devez quand même ajuster MySQL et ses outils associés pour générer des fichiers CSV qu'Excel n'interprétera pas mal :

Excel
MySQL (par défaut)
MySQL (une fois configuré)

SQL `NULL`
Valeur de longueur zéro
Littéral `\N`
Littéral `NULL`

Valeurs texte ne contenant pas de virgules, guillemets ou sauts de ligne
Non encadrées
Non encadrées
Encadrées par `"`

Valeurs texte contenant des virgules, guillemets ou sauts de ligne
Encadrées par `"`
Non encadrées
Encadrées par `"`

Valeurs non textuelles
Non encadrées
Non encadrées
Non encadrées

Sauts de ligne et tabulations dans les valeurs texte
Littéraux
Échappés comme `[\r]\n`
Littéraux

Guillemets doubles dans les valeurs texte
Doublés `""`
Échappés comme `\"`
Doublés `""`

Séparateur de champ
`,`
`\t` (Tabulation)
`,`

Séparateur d'enregistrement
`\r\n`
`\n`
`\r\n`

Virgules dans les valeurs texte non encadrées
(Résulte en données de tableau cassées)
Non échappées
Sera toujours encadré si la valeur contient une virgule

Support UTF-8

- **Excel 2007-2013 :** - Nécessite un BOM UTF-8 en tête
- **Excel 2016+ :** - Gère l'UTF-8 sans BOM avec quelques ajustements
Utilisez `utf8mb4`.
Ne spécifiez pas [les anciens encodages `utf8` ou `utf8mb3` défectueux](https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql).
Utilisez `utf8mb4`

Comme indiqué dans le tableau ci-dessus, MySQL *peut* générer des fichiers CSV compatibles Excel, sauf que les `NULL` SQL seront toujours interprétés par Excel comme du texte littéral, bien qu'il soit trivial d'utiliser PowerQuery ou même simplement Rechercher-et-Remplacer dans Excel pour les remplacer par des cellules vides.

## Excel et les marqueurs de texte CSV *spéciaux*

- [Si l'une de vos valeurs texte commence par `=`, vous devrez les ajuster manuellement car même entre guillemets, Excel interprétera le texte suivant `=` comme une formule au lieu de texte littéral](https://stackoverflow.com/questions/34595812/is-a-csv-with-equal-sign-valid).

- Faites également attention au [texte commençant par `sep=`](https://superuser.com/questions/773644/what-is-the-sep-metadata-you-can-add-to-csvs).

## Excel et l'encodage UTF-8 :

Étonnamment, ce n'est qu'après 31 ans d'existence (Excel 2016) qu'Excel a ajouté le support natif de l'encodage UTF-8 dans les fichiers *sans nécessiter de BOM*, mais il continue par défaut à importer et exporter les fichiers CSV en utilisant l'encodage non-Unicode par défaut de votre système (par ex. `Windows-1252`).

- Lors de l'importation de CSV dans Excel, assurez-vous de sélectionner la page de codes `65001` pour un traitement UTF-8 correct, car Excel utilise toujours par défaut des pages de codes non-Unicode pour une raison quelconque.

-

Notez que l'*ouverture* d'un fichier CSV dans Excel n'affichera pas l'*Assistant d'importation de texte*. (À partir d'Excel 2021) vous devez copier-coller du texte CSV dans Excel et utiliser le menu contextuel pour utiliser l'assistant historique (*figé depuis 1994*), ou utiliser Données > À partir de Texte/CSV dans le ruban pour utiliser l'assistant d'importation CSV plus récent (mais moins flexible, à mon avis) basé sur PowerQuery :

-

Excel 2007-2013
Excel 2016+

## Vos options :

Option
`SELECT INTO OUTFILE`
`mysqldump --tab`
`mysqldump > file.csv`
`mysqlsh`
MySQL Workbench

CSV côté serveur
**Vrai**
**Vrai**
**Vrai**
**Vrai**
Cassé

CSV distant (côté client)
Faux
Faux
Faux
**Vrai**
Cassé

Support de version MySQL Server
Toutes versions
Toutes versions
Toutes versions
Uniquement 5.7 et ultérieur
Toutes versions

## Option 1 : Exporter un CSV compatible Excel en utilisant `INTO OUTFILE` :

- Vous pouvez effectuer un export CSV **côté serveur** en utilisant [la clause `INTO OUTFILE` d'une requête `SELECT`](https://dev.mysql.com/doc/refman/8.0/en/select-into.html).

- Puisqu'il s'agit de SQL « normal » exécuté par le serveur MySQL, cela fonctionne quel que soit l'outil client MySQL que vous utilisez, vous n'avez donc pas besoin d'installer MySQL Workbench.

- ...mais puisqu'il s'agit d'un export côté serveur, vous devez avoir la permission d'écrire sur le système de fichiers du serveur, ce que vous n'avez peut-être pas, auquel cas envisagez d'utiliser des outils d'export spécialisés comme `mysqldump` (voir ci-dessous).

- La clause `OUTFILE` de MySQL [possède un certain nombre de sous-clauses **optionnelles**](https://dev.mysql.com/doc/refman/8.0/en/load-data.html#:%7E:text=INSERT%20Statements%E2%80%9D.-,Field%20and%20Line%20Handling,-For%20both%20the) *qui doivent être spécifiées* pour un certain niveau de compatibilité avec le lecteur CSV d'Excel :

- `FIELDS...`

- `TERMINATED BY` (par défaut : `'\t'`, pour Excel utilisez `','`)

- `[OPTIONALLY] ENCLOSED BY` (par défaut : `''`, devrait être `'"'` **avec** le mot-clé `OPTIONALLY`)

- `ESCAPED BY` (par défaut : `'\\'`, pour Excel utilisez `''`)

- `LINES...`

- `TERMINATED BY` (par défaut : `'\n'`, pour Excel utilisez `'\r\n'`)

- `STARTING BY` (par défaut : `''`, pour Excel vous pouvez omettre ceci ou utiliser la valeur par défaut de MySQL).

- **N'utilisez pas** `ENCLOSED BY` (sans le mot-clé `OPTIONALLY` précédent) car cela encadrerait toutes les valeurs, quel que soit le type (c'est-à-dire qu'il encadrerait les valeurs `int`, ce qui amènerait Excel (par défaut) à les interpréter comme du texte (chaînes) au lieu de nombres).

- Notez qu'il n'y a pas d'option pour demander à MySQL de produire les `NULL` SQL comme des champs vides, et donc Excel les interprétera comme des chaînes de texte non encadrées (c'est-à-dire comme `"NULL"`), vous voudrez donc faire un Rechercher-et-Remplacer dans Excel après avoir importé le fichier.

- Si votre nom de fichier `INTO OUTFILE <fileName>` (par ex. `'someTableExport.csv'` ci-dessus) n'est pas un chemin absolu, il sera enregistré dans le répertoire `datadir` de votre base de données. Exécutez `SHOW VARIABLES LIKE 'datadir';` pour obtenir le chemin. Notez que vous n'avez pas nécessairement la permission de lecture/écriture pour les nouveaux fichiers dans ce répertoire.

Votre requête (`SELECT * FROM document WHERE documentid...`) ressemblerait donc à quelque chose comme ceci :
`SELECT
    *
FROM
    document
WHERE
    documentid IN ( SELECT documentid FROM TaskResult WHERE taskResult = 2429 )
INTO
    OUTFILE 'someTableExport.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY ''
    LINES TERMINATED BY '\r\n';

Option 2 : Exporter un CSV compatible Excel en utilisant mysqldump :

  • Pour stocker le dump dans un fichier CSV en utilisant l’option de ligne de commande --tab, comme indiqué dans la documentation de mysqldump.

  • Malheureusement, l’option --tab= de mysqldump ne fonctionnera pas pour les serveurs MySQL distants : c’est parce que --tab="fileName.csv" ne peut représenter qu’un chemin sur le serveur.

  • Bien que vous puissiez utiliser la redirection stdout pour générer un fichier local (c’est-à-dire mysqldump --etc > output.csv), vous ne pouvez pas utiliser les options --fields-terminated-by et autres options de format avec stdout, ce qui le rend inutile pour une sortie compatible Excel. Donc si vous êtes distant et ne pouvez pas vous connecter en ssh, vous devrez utiliser MySQL Shell (mysqlsh) à la place (voir ci-dessous).

  • Notez que mysqldump ne supporte pas les requêtes SELECT pour l’export de données : il supporte des filtres simples de type WHERE avec l’option --where=<expr> mais cela ne supporte pas des choses comme le filtrage avec un INNER JOIN (bien qu’en contournement vous puissiez faire un SELECT dans une nouvelle table, puis exécuter mysqldump sur cette nouvelle table. Notez que vous ne pouvez pas utiliser TEMPORARY TABLE avec mysqldump car les tables temporaires sont limitées à la connexion).

Dans le cas de l’auteur de la question, en raison des limitations inhérentes au fonctionnement de l’option de ligne de commande --where=, il voudra exporter les deux tables (document et TaskResult) et appliquer sa logique de filtre dans Excel PowerQuery ou un outil similaire. Effectuez l’export comme suit :

mysqldump -h serverHostName -u mysqlUserName -p --tab="someTableExport.csv" --fields-optionally-enclosed-by=0x22 --fields-escaped-by='' --fields-terminated-by=0x2C --lines-terminated-by=0x0D0A --databases databaseName --tables document TaskResult

La commande ci-dessus devrait fonctionner sans modification dans le cmd.exe de Windows, le zsh de macOS, et bash sous Linux — à condition que mysqldump soit dans votre PATH.

L’utilisation de caractères encodés en hexadécimal permet d’éviter les tracas liés à la manière de passer des guillemets doubles et des sauts de ligne comme littéraux dans votre shell et terminal (0x22 est ", 0x2C est ,, et 0x0D0A est \r\n).

Évitez d’utiliser l’option --password=<value> (alias -p<value>) sur la ligne de commande mysqldump, car cela signifie que votre mot de passe sera enregistré en texte clair dans le fichier d’historique de votre terminal ou console, ce qui constitue un risque de sécurité massif évident.

  • Donc si vous êtes dans une session de ligne de commande interactive et devez spécifier un mot de passe, mysqldump vous demandera le mot de passe immédiatement lorsque le programme s’exécute afin qu’il ne soit pas enregistré dans votre fichier d’historique.

  • Si vous voulez exécuter mysqldump dans un contexte non interactif (par ex. depuis une application web, un daemon ou un autre processus), il n’y a (normalement) pas de fichier d’historique à craindre, mais vous devriez quand même envisager des approches alternatives avant de recourir à la manipulation de mots de passe de manière non sécurisée.

Si vous ne spécifiez pas de chemin absolu mais utilisez un nom de fichier court (non qualifié) comme INTO OUTFILE 'output.csv' ou INTO OUTFILE './output.csv', il stockera le fichier de sortie dans le répertoire spécifié par SHOW VARIABLES LIKE 'datadir';.

Option 3 : Exporter un CSV compatible Excel en utilisant MySQL Workbench :

Malheureusement, vous ne pouvez pas (sauf si vous n’avez aucun guillemet double dans vos données) : en date de fin 2022, MySQL Workbench a un bogue ouvert concernant sa propre fonctionnalité d’export CSV : les fichiers de sortie n’échappent jamais les caractères guillemets doubles dans le texte, ce qui fait que pratiquement tous les logiciels de traitement CSV signaleront un fichier CSV malformé ou importeront les données dans les mauvaises colonnes — ce qui le rend complètement inadapté à une utilisation avec Excel.

Option 4 : Exporter un CSV compatible Excel en utilisant MySQL Shell (alias mysqlsh) :

  • C’est probablement l’option la plus simple, mais vous pourriez devoir installer MySQL Shell car il n’est pas inclus par défaut dans la plupart des installations MySQL.

  • MySQL Shell supporte la connexion à MySQL Server 5.7 et ultérieur (mais pas aux versions plus anciennes). Si vous utilisez encore MySQL Server 5.6 ou antérieur (vous devriez vraiment mettre à jour vers 5.7 ou plus récent de toute façon), vous devrez vous contenter de mysqldump exécuté localement sur le serveur MySQL lui-même (mais vous pouvez utiliser une session ssh, bien sûr).

  • Le nouveau protocole « MySQL X » (mysqlx://user@host/schema) n’est pas supporté par MySQL 5.7, mais mysqlsh supporte les connexions non-X avec des paramètres de ligne de commande classiques.

  • Installez MySQL Shell s’il n’est pas déjà installé.

  • Si vous démarrez MySQL Shell sans arguments de ligne de commande (par ex. parce que vous utilisez le raccourci du menu Démarrer sous Windows), utilisez la commande \connect pour vous connecter.

Pour MySQL 5.7, utilisez \connect mysql://username@hostname

Pour MySQL 8.0+, il existe plusieurs façons de se connecter, y compris le protocole « MySQL X » ainsi que les connexions « Classic ». Consultez la documentation pour plus d’informations.

Si votre nom d’utilisateur contient des caractères @ littéraux, vous devez les encoder en pourcentage (par ex. si vous utilisez Azure MySQL, votre nom d’utilisateur complet sera comme username%[email protected]).

Immédiatement après avoir soumis la commande \connect, on vous demandera votre mot de passe de manière interactive.

  • Si vous pouvez démarrer mysqlsh avec des arguments, vous pouvez exécuter mysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName" directement sans utiliser la commande \connect.

  • Une fois connecté, exécutez la commande util.exportTable(tableName, outputUri, options) avec les arguments suivants :

  • tableName : le nom de votre table.

  • Malheureusement, il ne semble pas y avoir de moyen d’appliquer un filtre WHERE ou d’exporter les résultats d’une requête SELECT (bien que, comme avec mysqldump, vous puissiez toujours enregistrer vos résultats de requête dans une nouvelle TABLE, puis exporter cette table, puis DROP TABLE quand vous avez terminé. N’oubliez pas que TEMPORARY TABLE ne fonctionnera pas ici car les tables créées dans une session ne sont pas visibles depuis une autre session — et mysqlsh aura sa propre session.

  • outputUri : Pour enregistrer le fichier localement, utilisez un URI file:///.

  • Sous Windows, vous pouvez utiliser une barre oblique comme séparateur de nom de répertoire au lieu d’une barre oblique inversée. Par ex. file:///C:/Users/Me/Desktop/export.csv.

  • options : Pour assurer la compatibilité avec Excel, spécifiez { dialect: "csv", fieldsEscapedBy: ""}.

  • L’option dialect: "csv" définit des valeurs par défaut compatibles Excel pour tous les paramètres OUTFILE sauf un, vous devez donc également spécifier fieldsEscapedBy: "", sinon les NULL SQL seront rendus comme \N (littéralement) tandis que les guillemets doubles et les sauts de ligne à l’intérieur des valeurs texte seront échappés par des barres obliques inversées, ce qu’Excel ne supporte pas.