Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

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

ayi

Mon besoin est de stocker l’ensemble des résultats de la requête
<pre><code class="lang-auto">SELECT * FROM document
WHERE documentid IN (SELECT * FROM TaskResult WHERE taskResult = 2429)

</code></pre>
dans un fichier Excel.

ayi_2

La méthode classique pour y parvenir est d’exporter vers CSV puis de charger le CSV dans Excel.
<h2><a name="p-21790-en-rsum-1" class="anchor" href="#p-21790-en-rsum-1" aria-label="Heading link"></a>En résumé :</h2>
<ul>
<li></li>
</ul>
Pour un fichier CSV compatible Excel côté serveur à partir d’une requête <code>SELECT</code>, 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’;

<pre><code class="lang-auto">

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

</code></pre>
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

<pre><code class="lang-auto">

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

</code></pre>
mysqlsh --user=“mysqlUserName” --host=“serverHostName” --port=3306 --schema=“databaseName”
<h1><a name="p-21790-une-fois-connect-excutez-ceci-2" class="anchor" href="#p-21790-une-fois-connect-excutez-ceci-2" aria-label="Heading link"></a>Une fois connecté, exécutez ceci :</h1>
util.exportTable(“tableName”, “file:///C:/Users/You/Desktop/test.csv”, { dialect: “csv”, fieldsEscapedBy: “”})
<pre><code class="lang-auto">

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 ; bien qu'Excel soit largement conforme au RFC 4180, 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.
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

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.
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 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';

</code></pre>
<h2><a name="p-21790-option-2-exporter-un-csv-compatible-excel-en-utilisant-mysqldump-3" class="anchor" href="#p-21790-option-2-exporter-un-csv-compatible-excel-en-utilisant-mysqldump-3" aria-label="Heading link"></a>Option 2 : Exporter un CSV compatible Excel en utilisant <code>mysqldump</code> :</h2>
<ul>
<li>
Pour stocker le dump dans un fichier CSV en utilisant l’option de ligne de commande <code>--tab</code>, <a href="http://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_tab">comme indiqué dans la documentation de <code>mysqldump</code></a>.
</li>
<li>
Malheureusement, l’option <code>--tab=</code> de <code>mysqldump</code> ne fonctionnera pas pour les serveurs MySQL distants : c’est parce que <code>--tab="fileName.csv"</code> ne peut représenter qu’un chemin sur le serveur.
</li>
<li>
Bien que vous puissiez utiliser la redirection <code>stdout</code> pour générer un fichier local (c’est-à-dire <code>mysqldump --etc > output.csv</code>), vous ne pouvez pas utiliser les options <code>--fields-terminated-by</code> et autres options de format avec <code>stdout</code>, ce qui le rend inutile pour une sortie compatible Excel. Donc si vous êtes distant et ne pouvez pas vous connecter en <code>ssh</code>, vous devrez utiliser MySQL Shell (<code>mysqlsh</code>) à la place (voir ci-dessous).
</li>
<li>
Notez que <code>mysqldump</code> ne supporte pas les requêtes <code>SELECT</code> pour l’export de données : il supporte des filtres simples de type <code>WHERE</code> avec l’option <code>--where=<expr></code> mais cela ne supporte pas des choses comme le filtrage avec un <code>INNER JOIN</code> (bien qu’en contournement vous puissiez faire un <code>SELECT</code> dans une nouvelle table, puis exécuter <code>mysqldump</code> sur cette nouvelle table. Notez que vous ne pouvez pas utiliser <code>TEMPORARY TABLE</code> avec <code>mysqldump</code> <a href="https://stackoverflow.com/questions/6567730/is-mysql-temporary-table-a-shared-resource">car les tables temporaires sont limitées à la connexion</a>).
</li>
</ul>
Dans le cas de l’auteur de la question, en raison des limitations inhérentes au fonctionnement de l’option de ligne de commande <code>--where=</code>, il voudra exporter les deux tables (<code>document</code> et <code>TaskResult</code>) et appliquer sa logique de filtre dans Excel PowerQuery ou un outil similaire. Effectuez l’export comme suit :
<pre><code class="lang-auto">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

</code></pre>
<ul>
<li></li>
</ul>
La commande ci-dessus devrait fonctionner sans modification dans le <code>cmd.exe</code> de Windows, le <code>zsh</code> de macOS, et <code>bash</code> sous Linux — à condition que <code>mysqldump</code> soit dans votre <code>PATH</code>.
<ul>
<li></li>
</ul>
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 (<code>0x22</code> est <code>"</code>, <code>0x2C</code> est <code>,</code>, et <code>0x0D0A</code> est <code>\r\n</code>).
<ul>
<li></li>
</ul>
Évitez d’utiliser l’option <code>--password=<value></code> (alias <code>-p<value></code>) sur la ligne de commande <code>mysqldump</code>, car cela signifie que votre mot de passe sera enregistré en texte clair dans le fichier d’historique de votre terminal ou console, <a href="https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html">ce qui constitue un risque de sécurité massif évident</a>.
<ul>
<li>
Donc si vous êtes dans une session de ligne de commande interactive et devez spécifier un mot de passe, <code>mysqldump</code> 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.
</li>
<li>
Si vous voulez exécuter <code>mysqldump</code> 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, <a href="https://dev.mysql.com/doc/refman/8.0/en/password-security-user.html">mais vous devriez quand même envisager des approches alternatives</a> avant de recourir à la manipulation de mots de passe de manière non sécurisée.
</li>
<li></li>
</ul>
Si vous ne spécifiez pas de chemin absolu mais utilisez un nom de fichier court (non qualifié) comme <code>INTO OUTFILE 'output.csv'</code> ou <code>INTO OUTFILE './output.csv'</code>, il stockera le fichier de sortie dans le répertoire spécifié par <code>SHOW VARIABLES LIKE 'datadir';</code>.
<h2><a name="p-21790-option-3-exporter-un-csv-compatible-excel-en-utilisant-mysql-workbench-4" class="anchor" href="#p-21790-option-3-exporter-un-csv-compatible-excel-en-utilisant-mysql-workbench-4" aria-label="Heading link"></a>Option 3 : Exporter un CSV compatible Excel en utilisant MySQL Workbench :</h2>
Malheureusement, vous ne pouvez pas (sauf si vous n’avez aucun guillemet double dans vos données) : en date de fin 2022, <a href="https://bugs.mysql.com/bug.php?id=94735">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</a>, 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.
<h2><a name="p-21790-option-4-exporter-un-csv-compatible-excel-en-utilisant-mysql-shell-alias-mysqlsh-5" class="anchor" href="#p-21790-option-4-exporter-un-csv-compatible-excel-en-utilisant-mysql-shell-alias-mysqlsh-5" aria-label="Heading link"></a>Option 4 : Exporter un CSV compatible Excel en utilisant MySQL Shell (alias <code>mysqlsh</code>) :</h2>
<ul>
<li>
C’est probablement l’option la plus simple, mais vous <a href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html">pourriez devoir installer MySQL Shell</a> car il n’est pas inclus par défaut dans la plupart des installations MySQL.
</li>
<li>
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 <code>mysqldump</code> exécuté localement sur le serveur MySQL lui-même (mais vous pouvez utiliser une session <code>ssh</code>, bien sûr).
</li>
<li>
Le nouveau protocole « MySQL X » (<code>mysqlx://user@host/schema</code>) n’est pas supporté par MySQL 5.7, mais <code>mysqlsh</code> supporte les connexions non-X avec des paramètres de ligne de commande classiques.
</li>
<li>
<a href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html">Installez MySQL Shell</a> s’il n’est pas déjà installé.
</li>
<li>
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 <code>\connect</code> pour vous connecter.
</li>
<li></li>
</ul>
Pour MySQL 5.7, utilisez <code>\connect mysql://username@hostname</code>
<ul>
<li></li>
</ul>
Pour MySQL 8.0+, il existe plusieurs façons de se connecter, y compris le protocole « MySQL X » ainsi que les connexions « Classic ». <a href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-commands.html#:%7E:text=Connect%2C%20Reconnect%2C%20and%20Disconnect%20Commands">Consultez la documentation pour plus d’informations</a>.
<ul>
<li></li>
</ul>
Si votre nom d’utilisateur contient des caractères <code>@</code> littéraux, vous devez les encoder en pourcentage (par ex. si vous utilisez Azure MySQL, votre nom d’utilisateur complet sera comme <code>username%40servername@servername.mysql.database.azure.com</code>).
<ul>
<li></li>
</ul>
Immédiatement après avoir soumis la commande <code>\connect</code>, on vous demandera votre mot de passe de manière interactive.
<ul>
<li>
Si vous pouvez démarrer <code>mysqlsh</code> avec des arguments, vous pouvez exécuter <code>mysqlsh --user="userName" --host="hostName" --port=3306 --schema="dbName"</code> directement sans utiliser la commande <code>\connect</code>.
</li>
<li>
Une fois connecté, <a href="https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-table-export.html">exécutez la commande <code>util.exportTable(tableName, outputUri, options)</code></a> avec les arguments suivants :
</li>
<li>
<code>tableName</code> : le nom de votre table.
</li>
<li>
Malheureusement, il ne semble pas y avoir de moyen d’appliquer un filtre <code>WHERE</code> ou d’exporter les résultats d’une requête <code>SELECT</code> (bien que, comme avec <code>mysqldump</code>, vous puissiez toujours enregistrer vos résultats de requête dans une nouvelle <code>TABLE</code>, puis exporter cette table, puis <code>DROP TABLE</code> quand vous avez terminé. N’oubliez pas que <code>TEMPORARY TABLE</code> ne fonctionnera pas ici car les tables créées dans une session ne sont pas visibles depuis une autre session — et <code>mysqlsh</code> aura sa propre session.
</li>
<li>
<code>outputUri</code> : Pour enregistrer le fichier localement, utilisez un URI <code>file:///</code>.
</li>
<li>
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. <code>file:///C:/Users/Me/Desktop/export.csv</code>.
</li>
<li>
<code>options</code> : Pour assurer la compatibilité avec Excel, spécifiez <code>{ dialect: "csv", fieldsEscapedBy: ""}</code>.
</li>
<li>
L’option <code>dialect: "csv"</code> définit des valeurs par défaut compatibles Excel pour tous les paramètres <code>OUTFILE</code> sauf un, vous devez donc également spécifier <code>fieldsEscapedBy: ""</code>, sinon les <code>NULL</code> SQL seront rendus comme <code>\N</code> (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.
</li>
</ul>