JSON et Excel : Comment importer et exploiter vos données JSON ?

Comprendre le format JSON et son importance

Qu'est-ce que le JSON ?

JSON (JavaScript Object Notation) est un format d'échange de données léger et facile à lire, basé sur un sous-ensemble du langage JavaScript. Il est utilisé pour représenter des données structurées sous forme de paires clé-valeur et de listes. Sa simplicité et sa compatibilité avec de nombreux langages de programmation en font un choix privilégié pour les API et les applications web.

Pourquoi utiliser JSON avec Excel ?

Excel est un outil puissant pour l'analyse et la manipulation de données. Combiner la flexibilité de JSON avec les capacités d'Excel permet de :

• Importer des données depuis des API : De nombreuses API renvoient des données au format JSON. Excel peut les importer directement.
• Traiter des données complexes : JSON permet de représenter des structures de données imbriquées, qu'Excel peut ensuite manipuler.
• Automatiser des tâches : Avec VBA, il est possible d'automatiser l'importation et le traitement de données JSON.

Méthodes pour importer JSON dans Excel

Méthode 1 : Utilisation de Power Query (Get & Transform Data)

Power Query est un outil intégré à Excel (depuis Excel 2010) qui permet de récupérer, transformer et charger des données depuis diverses sources, y compris JSON. C'est la méthode recommandée pour la plupart des utilisateurs.

Étapes à suivre :

1. Ouvrez Excel et créez un nouveau classeur.
2. Allez dans l'onglet "Données" et cliquez sur "Obtenir des données" > "À partir d'un fichier" > "À partir de texte/CSV". (Le nom exact peut varier selon votre version d'Excel).
3. Sélectionnez votre fichier JSON. Si vous obtenez des données JSON via une API, vous pouvez choisir "À partir d'autres sources" > "À partir du Web" et coller l'URL de l'API.
4. L'éditeur Power Query s'ouvre. Excel détecte automatiquement que c'est un fichier texte. Vous devez le transformer en JSON. Cliquez avec le bouton droit sur la première colonne, puis choisissez "Transformer" > "JSON".
5. Naviguez dans la structure JSON. Power Query affiche la structure JSON sous forme de tables et d'enregistrements. Vous pouvez cliquer sur les liens "Enregistrement" ou "Liste" pour explorer les données.
6. Convertissez les listes en tables. Si une colonne contient des listes, cliquez sur l'icône en forme de deux flèches pointant dans des directions opposées en haut de la colonne. Cela convertit la liste en une table.
7. Développez les colonnes. Cliquez sur l'icône en forme de deux flèches pointant dans des directions opposées en haut de la colonne contenant des enregistrements. Cela permet de sélectionner les champs à inclure dans votre table Excel.
8. Chargez les données dans Excel. Une fois que vous avez transformé les données comme vous le souhaitez, cliquez sur "Fermer et charger" pour importer les données dans une feuille de calcul Excel.

Exemple pratique :

Supposons que vous ayez un fichier JSON nommé data.json avec le contenu suivant :

[
 {
 "nom": "Alice",
 "age": 30,
 "ville": "Paris"
 },
 {
 "nom": "Bob",
 "age": 25,
 "ville": "Lyon"
 }
]

Après avoir importé ce fichier JSON dans Power Query et suivi les étapes ci-dessus, vous obtiendrez une table Excel avec les colonnes "nom", "age" et "ville", contenant les données correspondantes.

Capture d'écran (description textuelle) : Une capture d'écran de l'éditeur Power Query montrant la structure JSON transformée en table, avec les colonnes "nom", "age" et "ville" affichées.

Méthode 2 : Utilisation de VBA (Visual Basic for Applications)

VBA offre une plus grande flexibilité pour le traitement de données JSON, mais nécessite des compétences en programmation. Cette méthode est utile pour automatiser des tâches complexes et personnaliser l'importation de données JSON.

Étapes à suivre :

1. Ouvrez l'éditeur VBA. Dans Excel, appuyez sur Alt + F11.
2. Insérez un nouveau module. Dans l'éditeur VBA, allez dans "Insertion" > "Module".
3. Ajoutez le code VBA pour parser le JSON. Vous aurez besoin d'une bibliothèque JSON pour VBA. Une option populaire est la bibliothèque VBA-JSON, disponible sur GitHub (cherchez "VBA-JSON" sur GitHub).
4. Importez la bibliothèque VBA-JSON. Téléchargez le fichier JsonConverter.bas de la bibliothèque VBA-JSON et importez-le dans votre module VBA (Fichier > Importer un fichier).
5. Écrivez le code VBA pour lire le fichier JSON et l'écrire dans Excel.

Exemple de code VBA :

Sub ImportJson()
 Dim JsonText As String
 Dim JsonObject As Object
 Dim i As Long
 Dim LastRow As Long

 'Chemin du fichier JSON
 Dim FilePath As String
 FilePath = "C:\chemin\vers\votre\fichier.json" 'Modifiez ce chemin

 'Lecture du fichier JSON
 Open FilePath For Input As #1
 JsonText = Input(LOF(1), 1)
 Close #1

 'Parsing du JSON
 Set JsonObject = JsonConverter.ParseJson(JsonText)

 'Détermination de la dernière ligne utilisée
 LastRow = ThisWorkbook.Sheets("Feuil1").Cells(Rows.Count, 1).End(xlUp).Row + 1

 'Écriture des données dans Excel
 i = 0
 For Each Item In JsonObject
 ThisWorkbook.Sheets("Feuil1").Cells(LastRow + i, 1).Value = Item("nom")
 ThisWorkbook.Sheets("Feuil1").Cells(LastRow + i, 2).Value = Item("age")
 ThisWorkbook.Sheets("Feuil1").Cells(LastRow + i, 3).Value = Item("ville")
 i = i + 1
 Next Item

End Sub

Explication du code :

• FilePath : Spécifie le chemin du fichier JSON.
• JsonText : Lit le contenu du fichier JSON dans une variable string.
• JsonObject : Parse le JSON en utilisant la bibliothèque JsonConverter.
• La boucle For Each itère sur chaque élément du tableau JSON et écrit les valeurs "nom", "age" et "ville" dans les colonnes correspondantes de la feuille Excel "Feuil1".

Important : Adaptez le chemin du fichier JSON (FilePath) et le nom de la feuille Excel (Feuil1) à votre situation.

Capture d'écran (description textuelle) : Une capture d'écran de l'éditeur VBA montrant le code VBA pour importer et parser le JSON, avec la bibliothèque VBA-JSON importée.

Méthode 3 : Utilisation d'un convertisseur en ligne

Si vous n'avez besoin d'importer des données JSON qu'occasionnellement, vous pouvez utiliser un convertisseur JSON vers CSV en ligne. Ces outils convertissent le JSON en un fichier CSV, que vous pouvez ensuite importer dans Excel.

Avantages :

• Simple et rapide.
• Ne nécessite pas de compétences techniques.

Inconvénients :

• Moins flexible que Power Query ou VBA.
• Risque de sécurité si vous traitez des données sensibles.

Exemple :

1. Recherchez "convertisseur JSON vers CSV" sur Google.
2. Choisissez un convertisseur en ligne fiable.
3. Collez votre code JSON dans le convertisseur.
4. Téléchargez le fichier CSV converti.
5. Importez le fichier CSV dans Excel (Données > Obtenir des données > À partir d'un fichier texte/CSV).

Conseils et astuces pour travailler avec JSON dans Excel

• Validez votre JSON : Avant d'importer votre JSON, assurez-vous qu'il est valide. Vous pouvez utiliser des outils en ligne comme JSONLint pour vérifier la syntaxe.
• Structurez votre JSON : Une structure JSON claire et cohérente facilite l'importation et le traitement des données.
• Utilisez Power Query pour les transformations : Power Query offre de nombreuses options pour transformer et nettoyer vos données JSON avant de les importer dans Excel.
• Automatisez avec VBA : Si vous devez importer et traiter des données JSON régulièrement, automatisez le processus avec VBA.
• Gérez les erreurs : Prévoyez la gestion des erreurs dans votre code VBA pour éviter les plantages en cas de données JSON invalides.

Erreurs courantes et comment les résoudre

• Erreur de syntaxe JSON : Vérifiez la syntaxe de votre JSON avec un validateur en ligne.
• Problèmes d'encodage : Assurez-vous que l'encodage de votre fichier JSON est compatible avec Excel (UTF-8 est recommandé).
• Erreurs dans le code VBA : Déboguez votre code VBA pour identifier et corriger les erreurs.
• Dépassement de la limite de lignes Excel : Si votre JSON contient trop de données, Excel peut ne pas pouvoir tout importer. Dans ce cas, envisagez de diviser votre JSON en plusieurs fichiers ou d'utiliser une base de données.

Conclusion

L'intégration de données JSON dans Excel est un atout précieux pour l'analyse et la manipulation de données. Que vous utilisiez Power Query pour une approche conviviale ou VBA pour une automatisation avancée, les possibilités sont vastes. En comprenant les principes fondamentaux du JSON et en maîtrisant les techniques d'importation, vous pouvez exploiter pleinement le potentiel de vos données JSON dans Excel.