Exportation de données : De la requête SQL à la feuille de calcul

Comment exporter des données d'une base de données SQL vers Excel ou une autre feuille de calcul ? Dans cet article, j'examinerai différentes méthodes et je vous donnerai quelques conseils en cas de problème.

SQL est la méthode la plus utilisée pour extraire des données d'une base de données. Cependant, ce n'est pas le seul gadget dans la boîte à outils d'un analyste. Une fois les informations extraites, vous souhaiterez souvent les importer dans une feuille de calcul, que ce soit pour effectuer une analyse plus poussée ou simplement pour les mettre en valeur afin de les présenter à la direction. Le langage SQL extrait les données sous forme de texte brut, mais comment les importer dans une feuille de calcul ?

En tant qu'analyste de données, vous devrez posséder à la fois des compétences en matière de feuilles de calcul et de SQL. Les feuilles de calcul sont largement utilisées et enseignées dans les écoles, je suppose donc que vous savez les utiliser.

Si vous n'êtes pas encore un expert en SQL, vous pouvez lire La meilleure façon d'apprendre le SQL, qui vous donnera quelques idées sur la façon de commencer. Je vous recommande également la formation SQL pour l'analyse de données de LearnSQL.fr. Spécialement conçue pour les analystes de données, cette formation se compose de quatre cours qui vous permettent de passer du niveau débutant à la maîtrise de l'analyse de données à l'aide de SQL. La formation dure environ 48 heures et vous apprendra à résoudre le type de problèmes que vous rencontrerez dans votre travail d'analyse.

Si vous connaissez déjà un peu le langage SQL mais que vous avez besoin d'un peu d'aide, voici une antisèche gratuite sur le langage SQL pour l'analyse des données .

Rapports SQL avec Excel

Dans cet article, je me concentrerai principalement sur la manière d'exporter des données SQL vers des feuilles de calcul, mais j'aimerais d'abord prendre quelques minutes pour examiner les raisons qui vous poussent à le faire. Examinons quelques scénarios :

Vous travaillez dans le marketing

Vous avez utilisé SQL pour extraire des données démographiques et apprendre quels types de produits ont été achetés par des clients de différentes régions et de différents groupes d'âge au cours des deux dernières années. Vous devez maintenant faire une présentation avec des graphiques et des tableaux pour justifier la campagne que vous envisagez pour l'année prochaine.

Vous pouvez utiliser une feuille de calcul pour créer ces graphiques et tableaux et leur donner un aspect professionnel. Les feuilles de calcul disposent d'une série d'outils utiles aux analystes de données, notamment :

• Graphiques et diagrammes.
• Tableaux croisés dynamiques.
• Cartes de puissance.
• Tri et filtrage op
• Les fonctions qui permettent d'effectuer des calculs complexes.

Vous travaillez dans une banque

Vous soupçonnez une fraude en cours. Vous avez utilisé SQL pour extraire les détails des transactions suspectes, mais vous devez expérimenter le filtrage et le tri des transactions et les comparer à d'autres événements survenus au cours de la période. Vous utiliseriez probablement un tableur pour ce faire, car il simplifie l'examen des données de différentes manières.

Vous devez soumettre un rapport à votre responsable

Chaque mois, vous devez créer un rapport indiquant le total des ventes réalisées auprès de chaque client. Votre requête SQL produit ce rapport :

Cependant, vous aimeriez le mettre en forme sous la forme d'un rapport attrayant pour votre responsable, comme celui-ci :

Il y aura toujours une demande pour vos compétences si vous pouvez travailler avec des données de feuilles de calcul Excel, importer des données à partir de SQL et produire des rapports attrayants et significatifs.

Exporter des données SQL vers des feuilles de calcul

Passons maintenant à l'objet de cet article : comment exporter des données d'une requête SQL vers une feuille de calcul. Il existe de nombreux fournisseurs de bases de données, chacun ayant un environnement de travail légèrement différent, et de nombreux types de feuilles de calcul. Il est donc difficile d'écrire un guide complet sur l'exportation de requêtes SQL, mais je vais vous montrer comment utiliser trois bases de données populaires....

• Google BigQuery,
• Microsoft SQL Server, et
• MySQL

... avec trois feuilles de calcul différentes :

• Excel,
• Libre Calc de la suite Libre Office, et
• Google Sheets.

Je suppose également que vous utilisez un PC Windows. Les utilisateurs de Linux et de Mac devront utiliser les équivalents du clic droit et les raccourcis clavier Crtl+.

Je vais examiner plusieurs méthodes et voir comment elles peuvent être utilisées avec les logiciels que j'ai mentionnés. Vous apprendrez suffisamment de choses pour exporter et importer des données n'importe où, puisque la plupart des bases de données et des feuilles de calcul sont très similaires.

Je montrerai des exemples en utilisant les données publiques de BigQuery, l'échantillon de base de données AdventureWorks de Microsoft et l'échantillon de base de données Employee de MySQL.

Je commencerai par la méthode la plus simple.

Méthode 1 : Copier et coller

Il s'agit de la méthode la plus rapide pour exporter une requête SQL vers une feuille de calcul, et je l'utilise 99 % du temps. Évidemment, vous devez d'abord exécuter une requête SQL pour fournir les données.

Vous ne devriez pas utiliser cette méthode pour de très grands ensembles de données, car elle doit stocker les données du presse-papiers en mémoire. Il est possible que votre ordinateur se bloque si l'ensemble de données est trop important. Plus loin dans cet article, j'examinerai certains des problèmes que vous pouvez rencontrer avec cette méthode et la manière de les résoudre.

Dans l'image ci-dessous, j'ai écrit une requête pour extraire tous les clients dont l'anniversaire est en janvier. Le service des relations avec la clientèle peut utiliser cette requête pour leur envoyer des courriels d'anniversaire. J'utilise SQL Server Management Studio pour exécuter la requête, et voici le résultat :

Vous pouvez copier des données de deux manières. Tout d'abord, vous mettez les données en surbrillance. Ensuite, vous pouvez soit cliquer sur Édition → Copier dans le menu, soit utiliser le raccourci clavier en appuyant sur CTRL+C.

Cette opération permet de copier dans le presse-papiers les lignes que vous avez mises en évidence, ainsi que les titres, sous la forme de données séparées par des tabulations.

Si vous souhaitez copier toutes les lignes dans votre feuille de calcul, vous pouvez également cliquer sur le carré blanc situé en haut, comme indiqué par la flèche rouge dans l'image ci-dessous. Toutes les lignes seront mises en évidence ; vous pouvez alors copier les données dans le presse-papiers.

En cliquant avec le bouton droit de la souris sur le même carré vide, vous obtenez un menu qui vous permet de choisir de copier avec ou sans les en-têtes.

Copier des données à partir de MySQL

Dans la capture d'écran ci-dessous, vous pouvez voir ma requête pour extraire tous les employés qui auront plus de 65 ans au début de l'année 2024 ; l'entreprise peut utiliser cette information pour envisager le départ à la retraite de certains employés. J'utilise MySQL Workbench pour exécuter la requête.

Là encore, il existe plusieurs façons de copier dans le presse-papiers : Édition → Copier dans le menu ou CTRL+C. Cette méthode permet de copier les données sans en-tête et délimitées par des tabulations.

Si vous cliquez sur la cellule vide en haut indiquée dans l'image ci-dessous, toutes les lignes seront sélectionnées. Si vous copiez ensuite les données dans le presse-papiers, elles incluront les en-têtes.

Copie de données à partir de BigQuery

Dans la capture d'écran ci-dessous, j'ai écrit une requête pour extraire une liste de banques enregistrées auprès de la FDIC. Il s'agit d'une base de données dans les données publiques de BigQuery. J'utilise BigQuery Studio pour exécuter la requête.

Bien que vous puissiez théoriquement mettre des lignes en surbrillance et utiliser CTRL+C ou la fonction presse-papiers de votre navigateur, cela ne fonctionne pas bien en pratique - les colonnes ont tendance à se désynchroniser. La seule bonne façon de copier dans le presse-papiers est d'utiliser la fonction Enregistrer les résultats mise en évidence dans l'image ci-dessous :

Un menu s'affiche :

Si vous choisissez Copier dans le presse-papiers, les données seront copiées sous forme de fichiers délimités par des tabulations avec des en-têtes. Remarque : cette méthode ne permet de copier que 1 Mo de données.

Coller dans Excel

Une simple opération de collage - en utilisant l'icône du presse-papiers dans le menu Accueil ou en tapant CTRL+V - permet d'introduire les données dans Excel, à partir de la position du curseur. Vous devrez généralement procéder à un certain formatage pour que les données soient agréables à lire : élargissement des colonnes, ajout d'en-têtes significatifs, etc.

Si les données ne sont pas présentées comme vous le souhaitiez, vous trouverez à la fin de cet article une section consacrée à la résolution des problèmes courants liés à l'exportation de données SQL vers des feuilles de calcul.

Coller dans Libre Calc

Dans Libre Calc, vous lancez l'opération de collage en tapant CTRL+V ou en choisissant Édition → Coller dans le menu Accueil. Le programme vous permet ensuite de définir certaines options pour contrôler la façon dont les données sont importées. Un menu contextuel apparaît :

Si vous cliquez simplement sur OK, le programme utilisera les valeurs par défaut pour importer les données. Cela fonctionne généralement bien, mais vous devrez peut-être définir certaines options si ce n'est pas le cas.

La modification la plus courante consiste à définir le type de données d'une colonne dont l'importation est incorrecte. Pour ce faire, cliquez sur la colonne dans la fenêtre d'aperçu des données, ce qui a pour effet de la sélectionner. Vous pouvez ensuite définir le type de données en cliquant sur la case Type de colonne située juste au-dessus de la section d'aperçu et en sélectionnant un type de données :

Parfois, pour des raisons connues des gremlins cachés dans tous les ordinateurs, cette boîte de dialogue ne s'affiche pas et les données sont collées par défaut. En général, cela ne pose pas de problème. Mais si vous devez régler l'une des options, vous pouvez forcer l'ouverture de la boîte de dialogue en choisissant Édition → Collage spécial → Collage spécial dans le menu Accueil :

Un autre menu s'affiche :

Choisissez Utiliser la boîte de dialogue d'importation de texte. La même boîte de dialogue que celle que nous avons examinée précédemment s'affiche alors.

Coller dans Google Sheets

Dans Google Sheets, il vous suffit de placer votre curseur et de choisir Édition → Coller ou d'appuyer sur CTRL+V. Aucune option ne vous permet de contrôler la manière dont les données sont collées ; Google Sheets utilise simplement ses propres valeurs par défaut.

Méthode 2 : utiliser des fichiers CSV pour transférer des données

Les fichiers CSV (valeurs séparées par des virgules) sont un moyen courant de transférer des données d'une application à une autre.

Vous les utiliserez de préférence au copier-coller dans les cas suivants :

• Les données sont trop volumineuses pour tenir dans le presse-papiers. En règle générale, vous ne voudrez pas utiliser le copier-coller pour plus de dix mille lignes.
• Votre feuille de calcul se trouve sur une machine différente de celle que vous utilisez pour extraire les données
• Vous souhaitez faire circuler les données pour permettre à quelqu'un d'autre de les importer dans sa propre feuille de calcul.

Les colonnes du résultat de votre requête seront séparées par des virgules dans le fichier CSV.

Extraction d'un fichier CSV à partir de SQL Server

Dans la fenêtre de résultat de la requête, cliquez avec le bouton droit de la souris sur la cellule vide située en haut à gauche des données. Le menu suivant s'affiche.

Choisissez Enregistrer les résultats sous. Une boîte de dialogue standard d'enregistrement de fichier s'affiche alors, vous permettant de donner un nom au fichier CSV et de naviguer jusqu'à l'endroit où vous souhaitez le stocker.

Extraction d'un fichier CSV à partir de MySQL

Juste au-dessus de la fenêtre des résultats de la requête, une fonction permet d'exporter les résultats vers un fichier externe. Elle est mise en évidence dans l'image ci-dessous.

Si vous cliquez dessus, vous obtiendrez la boîte de dialogue Enregistrer le fichier, qui vous permet de nommer le fichier et de naviguer jusqu'à l'endroit où vous souhaitez le stocker.

Extraction d'un fichier CSV à partir de BigQuery

Dans BigQuery Studio, un bouton Enregistrer les résultats se trouve juste au-dessus de la fenêtre des résultats. Si vous cliquez sur ce bouton, vous obtiendrez le menu suivant :

Vous pouvez alors choisir l'une des options suivantes

• CSV Local File (Fichier local CSV ) pour l'enregistrer sur votre ordinateur. La boîte de dialogue habituelle Enregistrer le fichier s'ouvre alors.
• CSV(Google Drive). Cette option permet d'enregistrer le fichier sur votre Google Drive, en utilisant (à mon avis) un nom de fichier plutôt rébarbatif. Heureusement, un lien apparaît comme dans l'image ci-dessous. Si vous cliquez sur le fichier, vous pourrez faire des choses utiles avec lui - comme le renommer, le partager ou l'ouvrir avec Google Sheets.

Ouverture d'un fichier CSV dans Excel

Vous pouvez ouvrir un fichier CSV à partir du menu Fichier → Ouvrir. Lorsque la boîte de dialogue Ouvrir un fichier s'affiche, recherchez l'emplacement où vous avez enregistré le fichier. Dans certaines versions d'Excel, les fichiers CSV sont automatiquement répertoriés. Dans d'autres, vous devez choisir d'afficher Tous les fichiers, comme le montre l'image ci-dessous :

Vous pouvez ensuite sélectionner le fichier et l'ouvrir.

Vous devrez probablement le formater pour qu'il soit agréable à regarder. Utilisez toujours Enregistrer sous pour l'enregistrer en tant que classeur Excel, car les fichiers CSV ne peuvent pas inclure de formatage.

Ouvrir un fichier CSV dans Libre Calc

Dans Libre Calc, choisissez Fichier → Ouvrir et naviguez jusqu'à l'endroit où vous avez enregistré le fichier. Libre Calc affiche automatiquement tous les fichiers, vous ne devriez donc pas avoir de difficulté à le trouver.

Une fois que vous avez sélectionné le fichier et que vous l'avez ouvert, Libre Calc affichera la même boîte de dialogue d'importation de texte que celle que vous avez déjà vue lorsque vous avez collé les résultats d'une requête.

Ouverture d'un fichier CSV dans Google Sheets

Ouvrez une nouvelle feuille vierge, puis choisissez Fichier → Importer.

Vous avez alors le choix de l'endroit où vous souhaitez importer le fichier :

• Si vous avez enregistré le fichier sur votre Google Drive, choisissez Mon Drive et sélectionnez le fichier souhaité.
• Si le fichier se trouve sur votre ordinateur, choisissez Télécharger. Vous pourrez alors faire glisser votre fichier dans la fenêtre. Vous pouvez également choisir Parcourir pour accéder à la boîte de dialogue Ouvrir un fichier.

Les options suivantes s'affichent alors :

Normalement, vous pouvez simplement cliquer sur Importer des données et utiliser les paramètres par défaut. Mais vous souhaiterez peut-être modifier l'option Remplacer la feuille de calcul. En cliquant sur cette option, vous obtiendrez plusieurs choix utiles.

Si vous décochez l'option Convertir le texte en nombres, dates et formules, toutes les données seront importées sous forme de texte. Cela peut s'avérer utile, car il arrive que des dates et des champs de texte contenant des chiffres soient mal interprétés. Par exemple, une banque peut avoir un numéro de compte numérique à 16 chiffres. Google Sheets limitant les champs numériques à 15 chiffres significatifs, le numéro de compte peut être altéré par les arrondis.

Méthode 3 : fonctions d'exportation dans les logiciels SGBDR

La plupart des logiciels de gestion de bases de données relationnelles (SGBDR) permettent d'exporter les résultats des requêtes. Je vais en examiner brièvement quelques-uns.

Serveur SQL

SQL Server dispose de deux utilitaires utiles pour les exportations SQL vers Excel. Tous deux sont assez compliqués, je me contenterai donc de décrire ce qu'ils font et de donner des liens vers la documentation Microsoft correspondante.

• Programme de copie en bloc (BCP). Le programme de copie en bloc est conçu pour copier les données du serveur SQL dans différents formats. Vous pouvez l'utiliser pour créer des fichiers CSV. Et comme il est possible de programmer son exécution régulière, c'est un bon moyen d'automatiser le transfert de données SQL vers Excel ou toute autre feuille de calcul. Vous pouvez apprendre à utiliser BCP ici.
• Assistant d'importation et d'exportation de SQL Server. Il permet d'exporter les résultats d'une requête dans différents formats, y compris Excel. Vous pouvez apprendre à utiliser l'assistant pour exporter depuis la base de données SQL vers Excel ici. Comme Libre Calc et Google Sheets peuvent ouvrir des fichiers Excel, vous pouvez également utiliser cette méthode pour exporter vers ces programmes.

MySQL

Dans la section précédente, nous avons vu comment utiliser MySQL Workbench pour exporter les données dans un fichier CSV. L'option Exporter le jeu de résultats vers un fichier externe peut également être utilisée pour exporter d'autres types de fichiers.

Comme vous pouvez le voir, l'un des choix possibles est d'exporter vers une feuille de calcul Excel. Cela permet d'exporter les données sous la forme d'un fichier XML de feuille de calcul, qui est facilement et précisément importé dans Excel. Libre Office ouvrira également avec succès les fichiers exportés de cette manière, mais pas Google Sheets.

BigQuery

Le bouton Enregistrer le résultat dans BigQuery peut être utilisé pour exporter les données directement vers Google Sheets, comme vous pouvez le voir dans les options ci-dessous. Il s'agit de la méthode la plus simple et la plus efficace pour importer des données de BigQuery vers Google Sheets. Étant donné que Google Sheets vous permet d'exporter vers Excel et que Libre Calc peut ouvrir des feuilles de calcul Excel, vous pouvez également utiliser cette méthode pour importer vos données dans l'un ou l'autre de ces logiciels.

Méthode 4 : Utiliser d'autres outils d'analyse de données pour créer des feuilles de calcul

Il existe de nombreux outils d'analyse de données sur le marché et presque tous vous permettent d'utiliser SQL pour extraire des données d'une base de données. Plusieurs d'entre eux offrent également la possibilité d'enregistrer les résultats sous la forme d'une feuille de calcul. Étant donné que la plupart des outils peuvent programmer des rapports régulièrement, ils peuvent constituer un bon moyen de produire automatiquement des feuilles de calcul en cas de besoin. Voici quelques-uns des outils qui permettent d'extraire des données à l'aide de SQL et de les enregistrer sous forme de feuilles de calcul :

• Tableau
• Crystal Reports
• Power BI
• Zoho Analytics
• Jasper Reports

Problèmes courants rencontrés lors de l'exportation de données SQL vers des feuilles de calcul

La plupart du temps, vos données seront importées correctement dans des feuilles de calcul, mais vous rencontrerez parfois des problèmes. Voici quelques problèmes courants et leurs solutions.

Toutes les données apparaissent dans une seule colonne

Vous collez vos résultats dans Excel. Mais au lieu que chaque champ figure dans une colonne distincte, ils apparaissent tous dans une seule colonne. Cela se produit généralement lorsqu'Excel se "souvient" (à partir d'une opération de collage précédente) qu'il doit utiliser autre chose que des tabulations comme délimiteurs. Les données peuvent ressembler à ceci :

Pour résoudre ce problème, allez dans l'onglet Données du ruban. Assurez-vous que l'option Choisir le texte dans les colonnes est sélectionnée :

Dans la boîte de dialogue, choisissez Délimité, puis cliquez sur Suivant. Dans la boîte de dialogue suivante, cochez Tabs sous Delimiters, puis cliquez sur Next. Vous avez maintenant la possibilité de choisir les types de données pour chaque colonne.

Si vous devez modifier l'un d'entre eux, cliquez sur la colonne et sélectionnez son type dans les cases d'option situées en haut. Ceci est particulièrement utile si vous souhaitez que les dates apparaissent dans un format différent ou que les nombres soient traités comme du texte.

Si cela vous arrive régulièrement, que pouvez-vous faire ? La prochaine fois que vous voudrez coller des données, cliquez sur la flèche située sous le bouton Coller. Vous aurez alors la possibilité d'utiliser l'assistant d'importation de texte, c'est-à-dire les boîtes de dialogue que nous avons vues dans le paragraphe précédent. Cela vous permettra de choisir la tabulation comme délimiteur ; Excel se "souviendra" de ce choix pour les opérations de collage ultérieures.

Vos dates sont à l'envers

Cela peut se produire lors du collage ou de l'importation à partir d'un fichier CSV. Il s'agit parfois d'un simple problème de formatage : vous pouvez sélectionner la colonne et utiliser la commande Format pour donner aux dates l'aspect que vous souhaitez.

Mais il s'agit parfois d'un problème plus grave. Le format de date court américain, par exemple, est mm/jj/aaaa, alors que le format de date court britannique est jj/mm/aaaa. Si votre format de date par défaut dans la feuille de calcul est différent du format dans votre application de base de données, vous pouvez finir par interpréter une date qui devrait représenter le 4 janvier comme étant le 1er avril parce que les jours et les mois sont intervertis.

La meilleure façon d'éviter cela est de procéder à un formatage en SQL pour s'assurer que les dates sont dans le format attendu par le tableur.

Vos nombres sont en notation scientifique

Si vous voyez dans votre feuille de calcul des nombres qui ressemblent à 123456E+14, c'est qu'ils sont en notation scientifique. Cela peut être dû au fait que votre colonne est trop étroite ; vous pouvez résoudre ce problème en élargissant la colonne. Toutefois, il est probable que le problème vienne du fait que votre nombre comporte plus de 15 chiffres. Les trois tableurs que nous avons examinés ne peuvent pas gérer des nombres plus grands. Ils les arrondissent automatiquement et les affichent en notation scientifique.

Cela convient pour les applications scientifiques, mais ce n'est pas terrible en comptabilité - et cela ne convient absolument pas si le nombre représente un numéro de compte à 16 chiffres !

Vous pouvez faire en sorte que les nombres s'affichent correctement en les traitant comme du texte et non comme des nombres. Toutefois, vous ne pourrez pas effectuer d'opérations arithmétiques dans ce format.

Si vous avez utilisé le copier-coller pour importer les données, utilisez la fonction Coller spécial → Assistant d'importation de texte pour changer le type de données de cette colonne en texte.

Si les données proviennent d'un fichier CSV, le moyen le plus simple de les importer correctement est d'ouvrir le fichier dans le Bloc-notes (ou tout autre éditeur de texte de base), de sélectionner et de copier toutes les données, puis d'utiliser Collage spécial → Assistant d'importation de texte pour spécifier les types de données.

Si vos données sont trop volumineuses pour une seule opération de copier/coller, il se peut que vous deviez effectuer l'opération de copier/coller en plus petits morceaux.

Votre colonne d'identification a perdu des zéros non significatifs ou un chiffre

Il se peut qu'une colonne contienne un identifiant numérique et que les chiffres soient très grands. Les zéros non significatifs sont importants dans ce cas, car ils font partie de l'identifiant.

La solution est la même que pour les nombres qui apparaissent en notation scientifique : Traitez la colonne comme du texte et non comme des données numériques.

Développez vos compétences en matière de SQL et de tableur

Comme nous l'avons vu, SQL et les tableurs peuvent facilement être utilisés ensemble pour exploiter la puissance des deux applications. Le langage SQL est votre porte d'entrée dans le monde des données et il vaut la peine d'améliorer vos compétences dans ce domaine.

Si vous n'avez jamais appris SQL, notre cours SQL pour les débutants est un excellent point de départ. Et si vous voulez vraiment investir dans votre avenir avec SQL, l'offre Tout à vie vous donne accès à tous nos cours, y compris ceux que nous n'avons pas encore écrits ! Cela signifie que vous serez toujours en mesure de rester au courant des nouvelles fonctionnalités qui seront ajoutées à SQL à l'avenir.

Si vous pouvez utiliser SQL et des feuilles de calcul - et faire fonctionner les deux ensemble - vos compétences seront toujours demandées dans le monde d'aujourd'hui axé sur les données. Alors, faites le premier pas dès aujourd'hui ! Commencez à apprendre, commencez à expérimenter et prenez votre avenir en main !