Comment Importer et Exploiter des Données JSON dans Excel ?

Comprendre l'Interaction Excel et JSON

Qu'est-ce que JSON ?

JSON (JavaScript Object Notation) est un format d'échange de données léger et facile à lire par les humains et à analyser par les machines. Il est basé sur un sous-ensemble du langage JavaScript et utilise une structure de paires clé-valeur pour représenter les données. JSON est couramment utilisé pour transmettre des données entre un serveur et une application web ou mobile.

Pourquoi Utiliser JSON avec Excel ?

Bien qu'Excel soit puissant pour manipuler des données tabulaires, il ne prend pas nativement en charge l'import direct de fichiers JSON complexes. Cependant, de nombreuses sources de données, comme les APIs web, fournissent des informations au format JSON. En intégrant JSON dans Excel, vous pouvez :

• Connecter Excel à des sources de données web : Récupérer des données en temps réel depuis des APIs.
• Automatiser l'import de données : Éviter la saisie manuelle et les erreurs associées.
• Analyser des données complexes : Traiter des structures de données hiérarchiques.

Méthodes d'Importation de JSON dans Excel

Il existe plusieurs méthodes pour importer des données JSON dans Excel, chacune ayant ses avantages et ses inconvénients. Nous allons explorer les plus courantes.

1. Utilisation de Power Query (Get & Transform Data)

Power Query, intégré à Excel sous le nom de "Get & Transform Data" (Données > Obtenir et transformer des données), est la méthode la plus flexible et recommandée pour importer des données JSON. Il permet de se connecter à des sources de données web, de parser le JSON et de transformer les données en un format tabulaire.

Étapes :

1. Ouvrez Excel et créez un nouveau classeur.
2. Allez dans l'onglet "Données" (Data).
3. Cliquez sur "Obtenir des données" (Get Data) > "À partir d'autres sources" (From Other Sources) > "À partir du Web" (From Web).
4. Entrez l'URL de l'API ou le chemin du fichier JSON dans la boîte de dialogue et cliquez sur "OK". Par exemple: https://jsonplaceholder.typicode.com/todos/1
5. Power Query se connecte à la source de données et affiche un aperçu du JSON. Vous verrez probablement un enregistrement ou une liste.
6. Cliquez sur "Convertir en table" (To Table) si nécessaire pour transformer la liste ou l'enregistrement JSON en une table.
7. Cliquez sur l'icône d'extension (deux flèches opposées) dans l'en-tête de la colonne pour développer les champs JSON. Choisissez les colonnes à importer.
8. Répétez l'étape 7 pour développer les champs imbriqués si nécessaire.
9. Une fois que vous avez développé toutes les colonnes souhaitées, cliquez sur "Fermer et charger" (Close & Load) pour importer les données dans une feuille Excel.

Exemple pratique :

Supposons que vous ayez un fichier JSON avec la structure suivante :

{
  "userId": 1,
  "id": 1,
  "title": "delectus aut autem",
  "completed": false
}

Après avoir importé le JSON via Power Query et cliqué sur "Convertir en table" (si nécessaire), vous devrez cliquer sur l'icône d'extension de la colonne "Column1" (ou le nom par défaut de la colonne contenant le JSON) et sélectionner les champs "userId", "id", "title" et "completed" pour les importer dans Excel.

Avantages :

• Méthode la plus flexible et puissante.
• Permet de transformer et de filtrer les données avant l'importation.
• Peut se connecter à des sources de données web dynamiques.
• Automatisation de l'import grâce à des requêtes Power Query.

Inconvénients :

• Peut être complexe pour les structures JSON très imbriquées.
• Nécessite une certaine familiarité avec Power Query.

2. Utilisation de VBA (Visual Basic for Applications)

VBA, le langage de programmation intégré à Excel, offre une autre méthode pour importer et parser des données JSON. Bien que plus complexe que Power Query, VBA permet un contrôle total sur le processus d'importation.

Étapes :

1. Ouvrez l'éditeur VBA (Alt + F11).
2. Insérez un nouveau module (Insertion > Module).
3. Écrivez un code VBA pour lire le fichier JSON, parser le JSON et écrire les données dans une feuille Excel.

Exemple de code VBA :

Sub ImportJson()
  Dim JsonFile As String, JsonText As String
  Dim JsonObject As Object, JsonItem As Object
  Dim FSO As Object, TextStream As Object
  Dim i As Long

  ' Chemin du fichier JSON
  JsonFile = "C:\chemin\vers\votre\fichier.json"

  ' Création d'un objet FileSystemObject
  Set FSO = CreateObject("Scripting.FileSystemObject")
  Set TextStream = FSO.OpenTextFile(JsonFile, 1)
  JsonText = TextStream.ReadAll
  TextStream.Close

  ' Création d'un objet JSON (nécessite d'activer la référence "Microsoft Scripting Runtime")
  Set JsonObject = JsonConverter.ParseJson(JsonText)

  ' Écriture des données dans la feuille Excel
  i = 1
  For Each JsonItem In JsonObject
    Cells(i, 1).Value = JsonItem("userId")
    Cells(i, 2).Value = JsonItem("id")
    Cells(i, 3).Value = JsonItem("title")
    Cells(i, 4).Value = JsonItem("completed")
    i = i + 1
  Next JsonItem

  Set FSO = Nothing
  Set TextStream = Nothing
  Set JsonObject = Nothing
  Set JsonItem = Nothing
End Sub

Important : Ce code nécessite l'installation et l'activation d'une bibliothèque JSON pour VBA. La plus courante est "VBA-JSON". Vous pouvez l'installer via le gestionnaire de références VBA (Outils > Références) en cochant la case "Microsoft Scripting Runtime" (pour la manipulation de fichiers) et en important le module VBA-JSON (disponible gratuitement en ligne) pour le parsing JSON.

Avantages :

• Contrôle total sur le processus d'importation.
• Possibilité de gérer des structures JSON complexes avec une logique personnalisée.
• Automatisation de l'import et de la transformation des données.

Inconvénients :

• Nécessite des compétences en programmation VBA.
• Plus complexe à mettre en œuvre que Power Query.
• Dépendance à des bibliothèques externes.

3. Utilisation d'Add-ins Excel (Compléments)

Il existe plusieurs add-ins Excel disponibles qui facilitent l'importation et la manipulation de données JSON. Ces add-ins fournissent souvent une interface utilisateur conviviale pour se connecter à des sources de données JSON et transformer les données.

Exemples d'add-ins :

• JSON Power Query (si non intégré à votre version d'Excel): Similaire à Power Query mais peut être disponible sous forme d'add-in.
• Autres add-ins spécifiques JSON : Recherchez dans le Microsoft AppSource des add-ins dédiés à la gestion de JSON.

Avantages :

• Interface utilisateur conviviale.
• Facilité d'utilisation pour les utilisateurs non techniques.
• Fonctionnalités spécifiques pour la gestion de JSON.

Inconvénients :

• Dépendance à des add-ins tiers.
• Coût potentiel (certains add-ins sont payants).
• Fonctionnalités limitées par rapport à Power Query ou VBA.

Manipulation des Données JSON Importées dans Excel

Une fois que vous avez importé des données JSON dans Excel, vous pouvez utiliser les fonctionnalités standard d'Excel pour manipuler et analyser les données.

Formules Excel

Vous pouvez utiliser des formules Excel pour effectuer des calculs, filtrer des données et formater les résultats. Par exemple, vous pouvez utiliser la formule SOMME pour calculer la somme d'une colonne de nombres, la formule SI pour effectuer des tests logiques, ou la formule RECHERCHEV pour rechercher des valeurs dans une table.

Tableaux Croisés Dynamiques

Les tableaux croisés dynamiques sont un outil puissant pour synthétiser et analyser des données. Vous pouvez créer un tableau croisé dynamique à partir de vos données JSON importées pour regrouper, filtrer et calculer des statistiques.

Graphiques

Excel offre une variété de types de graphiques pour visualiser vos données JSON importées. Vous pouvez créer des graphiques à barres, des graphiques linéaires, des graphiques circulaires, etc., pour présenter vos données de manière claire et concise.

Bonnes Pratiques et Erreurs à Éviter

• Validez votre JSON : Avant d'importer un fichier JSON, assurez-vous qu'il est valide en utilisant un validateur JSON en ligne. Un JSON invalide peut causer des erreurs lors de l'importation.
• Gérez les erreurs : Prévoyez des mécanismes de gestion des erreurs dans votre code VBA ou vos requêtes Power Query pour gérer les situations où les données JSON sont incorrectes ou incomplètes.
• Optimisez vos requêtes Power Query : Pour les grandes quantités de données JSON, optimisez vos requêtes Power Query pour améliorer les performances. Évitez de développer des colonnes inutiles et utilisez des filtres pour réduire la quantité de données à importer.
• Sécurisez vos données : Soyez conscient des risques de sécurité associés à l'importation de données JSON à partir de sources externes. Validez les données et utilisez des connexions sécurisées (HTTPS) si possible.

Conclusion

L'intégration de JSON dans Excel ouvre de nouvelles perspectives pour l'analyse et la manipulation de données. Que vous utilisiez Power Query, VBA ou des add-ins, il est essentiel de comprendre les différentes méthodes d'importation et les bonnes pratiques pour exploiter pleinement le potentiel de JSON dans Excel. En suivant les conseils et les exemples présentés dans cet article, vous serez en mesure d'importer, de parser et d'analyser efficacement des données JSON dans Excel, améliorant ainsi vos compétences en analyse de données et votre productivité.