Microsoft 365 Importer un JSON avec une table dans Excel

[Kristoff]

Bonjour, J'ai besoin d'importer ce JSON dans Excel. J'ai déjà réussi à importer tous les éléments surlignés en jaune, mais je n'arrive pas à récupérer les éléments surlignés en orange:

Voici le JSON complet : https://pastebin.com/zt5P5NCV
Je veux importer les 2 premiers 'uri' (surligné en orange) de chaque objet (Si on importer l'ensemble des 'uri' cela me va aussi, mais j'imagine que c'est plus complexe et je n'en ai pas forcément besoin).

J'ai réussi à importer ceci :

mais après je suis bloqué pour importer les 'uri' car les options proposés par Excel ne le permettent pas:

 

[Dudu2]

Bonjour,
Essayer ça https://github.com/VBA-tools/VBA-JSON
En particulier le https://github.com/VBA-tools/VBA-JSON/blob/master/JsonConverter.bas

 

[Kristoff]

This is to parse JSON in VBA, so I guess it requires some VBA code then to generate the table I want. The problem is that I have never done VBA

 

[chris]

Bonjour

J'ai cela

let
    Source = Json.Document(File.Contents("T:\TEMP\Json.txt")),
    items = Source[items],
    #"Converti en table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"id", "folderId", "type", "name", "login"}, {"id", "folderId", "type", "name", "login"}),
    #"login développé" = Table.ExpandRecordColumn(#"Column1 développé", "login", {"uris"}, {"uris"}),
    #"uris développé" = Table.ExpandListColumn(#"login développé", "uris"),
    #"uris développé1" = Table.ExpandRecordColumn(#"uris développé", "uris", {"uri"}, {"uri"})
in
    #"uris développé1"

Après on peut indexer et filtrer

EDIT code M modifié

 

[Kristoff]

Merci Chris. Comment est-ce que je peux utiliser le code que tu as partagé? Je n'ai jamais utilisé cela

 

[chris]

Bonjour

Modifie chemin et nom du fichier puis

Tu ouvres PowerQuery : Données, Obtenir de données, A partir d'autres sources, Requête vide

Accueil, Editeur avancé, coller dans la fenêtre (en supprimant ce qu'il y a dedans) et valider

 

[Kristoff]

Merci, je viens de tester via le menu décrit, cela marche ben
Est-ce qu'il est possible de lancer cela plus simple, par exemple ce serait top si en copiant le code dans un fichier 'Moulinette.vb' et en double-cliquant dessus on aurait un fichier Excel ou csv qui est généré. Est-ce qu'une possibilité de ce genre existe?

 

[chris]

Bonjour

Pas très clair "on aurait un fichier Excel ou csv qui est généré" : tu n'as pas expliqué concrètement ce que tu voulais obtenir depuis le fichier Json

On peut mettre le chemin du fichier dans une cellule nommée Chemin et utiliser

Source= Json.Document(File.Contents(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content][Column1]{0}))

au lieu de la ligne Source de l'exemple précédent

La cellule nommée peut être rempli par VBA avec une boite de dialogue permettant de choisir le fichier json

 

[Kristoff]

> Pas très clair "on aurait un fichier Excel ou csv qui est généré" : tu n'as pas expliqué concrètement ce que tu voulais obtenir depuis le fichier Json

Bonne question , voici ce que je veux faire: j'ai un progiciel que j'utilise régulièrement mais malheureusement celui-ci est trop basique pour nos besoins (pas assez de champs pour la classification des données). Je ne peux pas changer le progiciel, aussi je souhaite créer un fichier Excel à côté pour classer ces données. Les données étant entrées dans le progiciel avant tout, il me faudra régulièrement exporter les données du logiciel (le .json que j'ai partagé).

Cela signifie qu'au final, il me faudra régulièrement transformer ce .json vers un fichier Excel. Aussi si je pouvais l'automatiser ce serait top. Après réflexion dans ce fichier excel, j'aimerais avoir:

Un onglet avec les répertoires:

let
    Source = Json.Document(File.Contents("C:\Users\chris\Downloads\Json\Simple.json")),
    folders = Source[folders],
    #"Converted to Table" = Table.FromList(folders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"Column1.id", "Column1.name"})
in
    #"Expanded Column1"

Un onglet avec les éléments:

let
    Source = Json.Document(File.Contents("C:\Users\chris\Downloads\Json\Simple.json")),
    items = Source[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "folderId", "type", "name", "login"}, {"Column1.id", "Column1.folderId", "Column1.type", "Column1.name", "Column1.login"}),
    #"Expanded Column1.login" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.login", {"user"}, {"Column1.login.user"})
in
    #"Expanded Column1.login"

et un onglet avec les uris:

=vbl
let
    Source = Json.Document(File.Contents("C:\Users\chris\Downloads\Json\Simple.json")),
    items = Source[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "login"}, {"Column1.id", "Column1.login"}),
    #"Expanded Column1.login" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.login", {"uris"}, {"Column1.login.uris"}),
    #"Expanded Column1.login.uris" = Table.ExpandListColumn(#"Expanded Column1.login", "Column1.login.uris"),
    #"Expanded Column1.login.uris1" = Table.ExpandRecordColumn(#"Expanded Column1.login.uris", "Column1.login.uris", {"match", "uri"}, {"Column1.login.uris.match", "Column1.login.uris.uri"})
in
    #"Expanded Column1.login.uris1"

J'aimerais bien rapidement utiliser ces 3 codes automatiquement (par exemple, en double cliquant sur un fichier contenant les 3 codes ci-dessus, ou en lancant une ligne de commande, plutôt que d'ouvrir Excel à chaque fois et copier coller le 1er code, puis le 2e, puis le 3e) afin de générer ce type de fichier:

 

[chris]

Re

Comme dit il suffit de paramétrer le fichier source dans la requête via une cellule nommée (alimentable par VBA) et de ventiler le résultat en 3 requêtes chacune chargée dans un onglet

En général utilise une requête qui charge le fichier et les étapes de traitement communes aux 3 besoins
Ensuite chacune des autres fait référence à la première pour affiner selon l’objectif

C'est donc un classeur modèle qu'il suffit d'actualiser une fois la source paramétrée.

 

[Kristoff]

J'ai donc mis mes 3 scripts avec le chemin du fichier se trouvant dans une cellule Chemin:

let
    Source= Json.Document(File.Contents(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content][Column1]{0})),
    items = Source[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "folderId", "type", "name", "login"}, {"Column1.id", "Column1.folderId", "Column1.type", "Column1.name", "Column1.login"}),
    #"Expanded Column1.login" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.login", {"username"}, {"Column1.login.username"})
in
    #"Expanded Column1.login"

let
    Source= Json.Document(File.Contents(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content][Column1]{0})),
    folders = Source[folders],
    #"Converted to Table" = Table.FromList(folders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"Column1.id", "Column1.name"})
in
    #"Expanded Column1"

let
    Source= Json.Document(File.Contents(Excel.CurrentWorkbook(){[Name="Chemin"]}[Content][Column1]{0})),
    items = Source[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "login"}, {"Column1.id", "Column1.login"}),
    #"Expanded Column1.login" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.login", {"uris"}, {"Column1.login.uris"}),
    #"Expanded Column1.login.uris" = Table.ExpandListColumn(#"Expanded Column1.login", "Column1.login.uris"),
    #"Expanded Column1.login.uris1" = Table.ExpandRecordColumn(#"Expanded Column1.login.uris", "Column1.login.uris", {"match", "uri"}, {"Column1.login.uris.match", "Column1.login.uris.uri"})
in
    #"Expanded Column1.login.uris1"

Par contre je ne comprends pas comment lancer cela pour créer mes 3 onglets. Désolé, c'est peut être évident, mais comme je n'ai jamais fait de VBA, je ne sais pas comment générer le fichier Excel à partir de ce script;

 

[chris]

Bonjour

NOOOooooon

VBA ne doit que remplir la cellule nommée Chemin puis actualiser

 

[Kristoff]

Merci ! cela fonctionne à merveille pour extraire les données de mon Json.
Comment je peux modifier le script d'extraction dans ce fichier .xlsm ? je n'ai pas trouver comment.

 

[chris]

Bonjour

Pour la nième fois : l'import est fait par PowerQuery donc il faut modifier les requêtes PowerQuery si la structure des fichiers json est différente

Sinon il suffit de choisir le fichier...

J'avoue que j'ai du mal à comprendre car dès le post #1 tu as mis des copies d'écran de PowerQuery...

 

[Kristoff]

NOOOooooon

Pour la nième fois : l'import est fait par PowerQuery

il suffit de choisir le fichier...

Trouvez-vous normal d'avoir ce genre de commentaire sur ce forum?

Cela m'attriste. Je suis juste une personne qui a pris de temps de regarder en long en large et en travers comment faire pour utiliser Excel, qu'il n'a malheureusement jamais eu l'occasion de vraiment utiliser., afin d'importer un JSON. En me renseignant sur internet, j'ai découvert l'écran PowerQuery, mais comme je n'arrivais à pas comprendre son utilisation j'ai posté mon message. J'ai visiblement eu tort