Importer en json dans Excel
La fonction oRecordSet renvoie un 'objet' dans DataSet (Objet au sens de la POO mais n'ayant pas tout à fait le même sens que l'Objet au sens json)
Cet objet a un ou plusieurs éléments
Et chaque élément est composé de plusieurs membres.
Chaque membre est un couple 'clé+valeur'
Comme dans un parc automobile(objet), il y a plusieurs voitures(éléments) et chaque voiture a un moteur, un volant, ...(membres)
Chaque membre a une valeur : Volant=en cuir, ou peinture=métallisée, ...(couple clé+valeur)
Ici on parse un json, l'objet DataSet représente le contenu entier du fichier. Dans ce fichier il y a un ou plusieurs éléments, par exemple des participants. Et chaque participant a un nom, un numéro, un statut, ... avec chacun une valeur.
L'intérêt du format json est de proposer une structure bien plus riche que des colonnes excel, on peut y trouver par exemple des 'Objets'(avec éléments et membres) eux-même imbriqués dans des éléments.
D’où la complexité à décoder(parser) du json en VBA (en PHP c'est plus simple puisque le javascript/json y est implémenté en natif)
En l'espèce, tes fichiers json ont des structures différentes selon le type d'info contenues. On doit donc prévoir une 'lecture' spécifique pour chaque json puisqu'ils sont hiérarchisés différemment.
Le plus dur c'est de mettre en pratique. Par exemple là, j'essai de choper le libelleCourt avec encore une racine de moins dans l'adresse
{
cached: false,
timezoneOffset: 3600000,
dateReunion: 1453071600000,
numOfficiel: 1,
numOfficielReunionPrecedente: null,
numOfficielReunionSuivante: 2,
numExterne: 1,
nature: "DIURNE",
-hippodrome: {
code: "VIN",
libelleCourt: "VINCENNES",
libelleLong: "HIPPODROME DE PARIS-VINCENNES"
Avec ce code mais j'y suis pas encore
site = "https://www.pmu.fr/services/turfInfo/client/1/programme/" & LaDate & "/R3/"
Set DataSet = oRecordSet(site)
With Sheets("Data")
'.Range("AK2:AA15").ClearContents
i = 2
For Each Hippo In DataSet.hippodrome
T(15) = DataSet.libelleCourt
Next Hippo
Bah, c'est toujours la même chose
Sub Ourasi()
Dim DataSet As Object, Elem As Object
Dim site As String, i As Long, j As Long
Dim LaDate As String, T(14) As Variant
If Sheets("Feuil1").Range("E4") Then
LaDate = Format(Sheets("Feuil1").Range("E4"), "ddmmyyyy")
T(0) = Format(Sheets("Feuil1").Range("E4"), "dd/mm/yyyy")
On Error Resume Next
site = "https://www.pmu.fr/services/turfInfo/client/1/programme/" & LaDate & "/R3/"
Set DataSet = oRecordSet(site)
With Sheets("Feuil2")
.Range("A2:AA15").ClearContents
i = 2
For Each Elem In DataSet.courses
.Cells(i, 1).Value = T(0)
.Cells(i, 2).Value = DataSet.hippodrome.libelleCourt
.Cells(i, 3).Value = Elem.libelle
.Cells(i, 4).Value = Elem.conditionAge
' etc ...
i = i + 1
Next
End With
Set DataSet = Nothing
End If
End Sub
Ok merci pour tout
Re Pierre
Une dernière chose, je suis confronté a un problème, ici dans le js, il y a le même nom ! Comment tu ferais pour avoir le Rapport, dividende, simple gagnant et ensuite il y a trois fois le même nom pour dividende placé.
Merci encore de prendre du temps pour moi
Dans ce cas tu as un niveau supplémentaire d'imbrication avec un array inclus dans un élément objet, il suffit donc de faire une boucle supplémentaire ('for each' dans le 'for each')
C'est dans ce cas qu'on peut visualiser la souplesse du json par rapport aux "simples" colonnes d'excel.
Sub MistralGagnant()
Dim DataSet As Object, Elem As Object, Elem1 As Object
Dim site As String, i As Long
On Error Resume Next
site = "https://www.pmu.fr/services/turfInfo/client/7/programme/18012016/R1/C1/rapports-definitifs"
Set DataSet = oRecordSet(site)
With Sheets("Feuil3")
.Range("A2:AA15").ClearContents
i = 2
For Each Elem In DataSet
.Cells(i, 1).Value = Elem.typePari
.Cells(i, 2).Value = Elem.miseBase
For Each Elem1 In Elem.rapports
.Cells(i, 3).Value = Elem1.libelle
.Cells(i, 4).Value = Elem1.dividende
.Cells(i, 5).Value = Elem1.dividendePourUnEuro
.Cells(i, 6).Value = Elem1.combinaison
.Cells(i, 7).Value = Elem1.nombreGagnants
.Cells(i, 8).Value = Elem1.dividendePourUneMiseDeBase
.Cells(i, 9).Value = Elem1.dividendeUnite
i = i + 1
Next
i = i + 1
Next
End With
Set DataSet = Nothing
End Sub
Ok merci bcp
Bonjour,
Je reprends la discussion car ayant les même lien sur le pmu, je suis tombé sur votre code et c'est exactement ce qu'il me faut.
Je précise que j'ai que quelques notions de vba, j'ai bien compris l’environnement objet dans le json comme expliqué plus haut, par contre je sèche complétement sur la fonction et je pense que mon problème vient de la. Lors de exécution pas à pas, après avoir exécuté cette ligne de code " Set ScriptControl = CreateObject("MSScriptControl.ScriptControl") " il revient dans la macro et du coup je pense que la page web n'a pas été chargée dans la dataset.
peut être qu'il me manque une référence dans Outils / Références ?
Merci de votre aide.
Sub IdealDuGazeau()
Dim DataSet As Object, Elem As Object
Dim site As String, i As Long, j As Long
Dim LaDate As String, T(14) As Variant ' ici T()=variable tableau
If Sheets("Data").Range("A1") Then ' s'il y a quelque chose dans A1 on continue
LaDate = Format(Sheets("Data").Range("A1"), "ddmmyyyy") ' la date dans LaDate dans format "16022017"
T(0) = Format(Sheets("Data").Range("A1"), "dd/mm/yyyy") ' la date dans T(0) dans format "16/02/2017"
On Error Resume Next
site = "https://www.pmu.fr/services/turfInfo/client/1/programme/" & LaDate & "/R1/C5" 'on construit l'adresse web
Set DataSet = oRecordSet(site)
' on stocke dans T()
T(1) = DataSet.libelle
T(2) = DataSet.numReunion
T(3) = DataSet.montantPrix
T(4) = DataSet.parcours
T(5) = DataSet.distance
T(6) = DataSet.corde
T(7) = DataSet.discipline
T(8) = DataSet.categorieParticularite
T(9) = DataSet.nombreDeclaresPartants
T(10) = DataSet.conditionAge
T(11) = DataSet.conditionSexe
T(12) = DataSet.numOrdre
T(13) = DataSet.numCourseDedoublee
T(14) = (DataSet.heureDepart + DataSet.timezoneOffset) / 1000 / 24 / 60 / 60
site = "https://www.pmu.fr/services/turfInfo/client/1/programme/" & LaDate & "/R1/C5/participants"
Set DataSet = oRecordSet(site)
With Sheets("Data")
.Range("A2:AA15").ClearContents
i = 2
For Each Elem In DataSet.participants
.Cells(i, 1).Value = T(0)
.Cells(i, 2).Value = Elem.nom
.Cells(i, 3).Value = Elem.numPmu
.Cells(i, 4).Value = Elem.age
.Cells(i, 5).Value = Elem.sexe
.Cells(i, 6).Value = Elem.race
.Cells(i, 7).Value = Elem.statut
.Cells(i, 8).Value = Elem.placeCorde
.Cells(i, 9).Value = Elem.oeilleres
.Cells(i, 10).Value = Elem.proprietaire
.Cells(i, 11).Value = Elem.entraineur
.Cells(i, 12).Value = Elem.driver
.Cells(i, 13).Value = Elem.tauxReclamation / 100
.Cells(i, 14).Value = Elem.indicateurInedit
.Cells(i, 15).Value = Elem.gainsParticipant.gainsCarriere / 100
.Cells(i, 16).Value = Elem.handicapValeur
.Cells(i, 17).Value = Elem.ordreArrivee
.Cells(i, 18).Value = Elem.engagement
.Cells(i, 19).Value = Elem.handicapPoids / 10
.Cells(i, 20).Value = Elem.poidsConditionMonte / 10
.Cells(i, 21).Value = Elem.dernierRapportDirect.rapport
.Cells(i, 22).Value = Elem.dernierRapportReference.favoris
For j = 1 To 14
.Cells(i, 22 + j).Value = T(j) ' on recrache T() dans les colonnes
Next j
i = i + 1
Next Elem
End With
Set DataSet = Nothing
End If
End Sub
Function oRecordSet(site As String) As Object
Dim ScriptControl As Object, Html As Object, Obj As Object
Set ScriptControl = CreateObject("MSScriptControl.ScriptControl")
ScriptControl.Language = "JScript"
Set Html = CreateObject("MSXML2.XMLHTTP")
With Html
.Open "GET", site, False
.send
Set Obj = ScriptControl.Eval("(" & .responsetext & ")")
End With
Set oRecordSet = Obj
Set Obj = Nothing
End Function
le probleme a été résolu en installant la version 2016 d'office
calten a écrit :Bonjour,
Je suis inscrit depuis aujourd'hui et je me permet de vous contacté pour avoir une solution
J'ai lu et essayé d'adapté votre code pour des besoins personnels, mais cela ne fonctionne pas
C'est dans ce sujet là
https://forum.excel-pratique.com/viewtopic.php?f=2&t=89337&p=518596&hilit=turf#p518596
Je souhaiterais récupéré dans le fichier json à cette adresse :
les informations suivantes:
typePari "SIMPLE_GAGNANT"
dateProgramme 1559080800000
timezoneOffset 7200000
numeroReunion 1
numeroCourse 1
numPmu 1
nom "PARIGOTE"
statut "PARTANT"
enjeu 743903
ratio 1.85
J'ai essayé en changeant les noms mais cela ne fonctionne pas.
Je vous remercie encore si vous pouvez m'aider.
Cordialement
Pour répondre en "public" à Calten, le tout est une affaire de "clés" :
Sub Calten()
Dim DataSet As Object, Cita As Object, Elem As Object, lg As Integer
With Sheets("Feuil1")
Set DataSet = Rcdst_Jsn(.Range("A1").Value)
Set Cita = VBA.CallByName(DataSet.listeCitations, "0", VbGet)
.Range("A3").Value = Cita.typePari
.Range("B3").Value = Cita.dateProgramme
.Range("C3").Value = Cita.timezoneOffset
.Range("D3").Value = Cita.numeroReunion
.Range("E3").Value = Cita.numeroCourse
lg = 6
For Each Elem In Cita.participants
.Range("A" & lg).Value = Elem.numPmu
.Range("B" & lg).Value = Elem.nom
.Range("C" & lg).Value = Elem.statut
.Range("D" & lg).Value = VBA.CallByName(Elem.citations, "0", VbGet).enjeu
.Range("E" & lg).Value = VBA.CallByName(Elem.citations, "0", VbGet).ratio
lg = lg + 1
Next Elem
End With
Set DataSet = Nothing
Set Cita = Nothing
End Sub
Pierre
Bonjour à tout le forum,
Merci Pierre pour votre réponse, y-a t'il un moyen d'avoir la même présentation que dans les exemples précédents à savoir on saisie la course dans une cellule Excel, puis la réunion dans une autre afin d'avoir à loisir chaque course que l'on souhaite comme pour les exemples précédents avec les données dans chaque colonne avec ce lien .https://offline.turfinfo.api.pmu.fr/rest/client/7/programme/31052019/R4/C1/participants?specialisation=OFFLINE... J'ai trouvé un json qui stocke toutes les informations qui me serait utile ensuite à la fin de la réunion.
Et celui-ci aussi
C'est parce que même en essayant avec le code plus haut et en changeant les noms je n'ai rien qui s'affiche, je peux saisir le code en entier ce qu'il me faut c'est le point de départ, car pour chaque lien plus haut les données sont exploitables mais différemment. Merci encore pour votre aide.
Bonne journée à tous
A l'évidence, quelques explications sont utiles. Changer quelques mots clé n'est pas suffisant.
Pour commencer il faut bien savoir ce qu'est le json. Pour cela, il y a des sites très bien qui en explique les fondamentaux.
Un exemple ici (mais il y en a plein d'autres) => https://la-cascade.io/json-pour-les-debutants/
Donc avec ces explications, il est clair qu'il y a autant de structures de fichiers json qu'il y a de json (ou presque). Chaque json (téléchargé ou lu directement sur internet) doit donc avoir un décodage qui lui est propre.
La plupart du temps un site donné pour un besoin donné met à disposition un json qui aura toujours la même structure. Si dans l'adresse, il y a la date ou une réf qui change, la structure restera la même. Mais pour des données différentes, il se peut que les structures soient différentes (c'est le cas de offline.turfinfo)
Ok, donc la base du json c'est un couple clé/valeur, par exemple : "code_dept": "56" ou bien "code_dept": 56 (texte dans le 1er cas, numérique dans le 2ème)
En sachant que la valeur peut être de plusieurs types : texte/numérique/Array ou "parent" d'une sous-structure.
Donc chaque mot-clé peut être sur un niveau précis d'un structure/sous-structure.
Pour connaitre la structure, les mots-clé et les niveaux il y a des outils pour ça. Je poste ici le mien : "Démo GetKey_Json.xlsm" mais il y en a d'autres (cf Steelson par exemple)
Donc maintenant que je connais la structure du json, comment on fait avec excel?
1/ Récupérer le json via internet :
Dans le fichier Turf_Calten.xlsm, il y a la fonction Rcdst_Jsn qui retourne le contenu dans un "Object" (fonction classique qu'on trouve un peu partout basé sur "MS.ScriptControl" et sur "MSXML2.XMLHTTP") qu'on peut réutiliser telle quelle.
Ici l'url du site peut provenir du contenu d'une cellule ou d'une concaténation de plusieurs cellues ou valeur (chacun fait comme il veut)
2/ Ensuite on "découpe" l'Object obtenu en fonction des mots clés :
* si c'est du texte ou du numérique c'est simplement du genre : Object.motclé
* si c'est une Array du type : "geo_point_2d": [
47.52204264643347,
2.577570172192727
], on récupère la 1ere info avec un : VBA.CallByName(Object.geo_point_2d, "0", VbGet),
la 2ème sera: VBA.CallByName(Object.geo_point_2d, "1", VbGet)
* si c'est une array avec mot-clé, on peut utiliser les mots-clé, par exemple : Object.typePari
* si c'est un parent d'une sous-structure, on place le bloc dans un sous-object et on redécoupe à un niveau inférieur.
* si c'est une array avec enregistrements multiples, on boucle sur chaque élément :
For Each Element in Object.motclé ...
A noter : parfois les mots clé utilisés sont des mots réservés du Vba exemple "name" => dans ce cas on récupère le contenu avec un VBA.CallByName(Object, "name", VbGet)
3/ une fois le contenu souhaité récupéré, il suffit de le coller sur la feuille. Sans oublier éventuellement d'incrémenter le n° le ligne à chaque nouvel enregistrement.
Donc dans le cas exposé : 3 nouveaux json / 3 structures différentes / 3 traitements différents à écrire.
Avec ces quelques notes et les exemples de code déjà donnés, ça devrait être possible par tout un chacun.
Bon codage
Pierre
Je suis très impressionné par la réponse et je vous remercie, 3 structures différentes donc 3 codes différents à écrire c'est ce que je pensais aussi, cela va être compliqué mais cela ne me rebute pas je suis d'un naturel patient j'ai cliqué sur "Démo GetKey_Json.xlsm" mais rien ne s'est produit.
Il y a des convertisseurs online de json vers Excel mais j'aimerais vraiment pouvoir le faire moi-même car la plupart ne donnent pas satisfaction.Lorsque j'aurais vraiment acquis le maniement des json ce sera plus facile pour moi.Dans l'immédiat je vais relire le sujet, merci beaucoup.
Oups, j'avais oublié de le poster ...
Merci beaucoup je vais testé le fichier.
Cordialement
Ce fichier est excellent, cela va beaucoup m'aider à isolé les structures et surtout bien comprendre les json, merci beaucoup.
Bonjour
J'ai donc récupéré grâce au fichier Démo GetKey_Jsonde pierrep56 la structure du lien json correspondant à cette url :
La structure se présente ainsi :
Il y a donc des données numeric, parent,text,array, à quoi correspond les numéros dans la colonne juste après par exemple programme on lit 1 ?
Je pense donc que "programme" est la clé qui me permettra d'extraire les données vers Excel,
je bloque ici,
pierrep56 :
* si c'est un parent d'une sous-structure, on place le bloc dans un sous-object et on redécoupe à un niveau inférieur.
Je ne sais pas si la macro doit s'approcher de :
Sub Calten()
Dim DataSet As Object, Cita As Object, Elem As Object, lg As Integer
With Sheets("Feuil1")
Set DataSet = Rcdst_Jsn(.Range("A1").Value)
Set Cita = VBA.CallByName(DataSet.listeCitations, "0", VbGet)
.Range("A3").Value = Cita.typePari
.Range("B3").Value = Cita.dateProgramme
.Range("C3").Value = Cita.timezoneOffset
.Range("D3").Value = Cita.numeroReunion
.Range("E3").Value = Cita.numeroCourse
lg = 6
For Each Elem In Cita.participants
.Range("A" & lg).Value = Elem.numPmu
.Range("B" & lg).Value = Elem.nom
.Range("C" & lg).Value = Elem.statut
.Range("D" & lg).Value = VBA.CallByName(Elem.citations, "0", VbGet).enjeu
.Range("E" & lg).Value = VBA.CallByName(Elem.citations, "0", VbGet).ratio
lg = lg + 1
Next Elem
End With
Set DataSet = Nothing
Set Cita = Nothing
End Sub
Je pensais remplacer Cita par Programme mais cela ne fonctionne pas, je pense aussi que VBA.CallByName
va chercher dans DataSet.programme mais cela ne fonctionne pas non plus je dois surement mal faire.
Que veut dire lg ?
Merci pour votre aide,
Cordialement
Ok, on a donc de gauche à droite :
* la clé
* le niveau (un élément de niveau n est inclus dans l'élément n-1)
* le type
* ensuite une "représentation" de la hiérarchie des clés
Ensuite pour les noms des variables, on peut utiliser n'importe quel nom (si différent des mot-clé du VBA). Autant utiliser des noms qui parle au codeur
Précédemment lg
c'est le n° de ligne de la feuille excel, on peut l'appeler ligne
si c'est plus explicite.
Pour chaque url, le "décodage" doit être adapté à la structure du contenu. Un simple remplacement de mot-clé ne suffit pas.
Pour l'url indiquée la première clé niv1 est "programme"
. On peut donc placer le contenu de "programme" dans une variable Prg
par exemple.
Ce qui permet de lire la clé "dateProgrammeActif"
avec un Prg.dateProgrammeActif
Le "parent" suivant est "reunions"
, il faut donc boucler sur les éléments contenus dedans :
For Each Elem In Prg.reunions
Ce qui permets de récupérer par exemple les noms ("libelleCourt") des hippodromes :
Elem.hippodrome.libelleCourt
Ensuite l'élément parent "courses"
est inclus dans Prg.reunions
, on boucle donc sur les éléments contenus dedans :
For Each Elem2 In Elem.courses
et ainsi de suite ...
Ici dans le code exemple le Debug.Print
affiche le résultat dans la fenêtre exécution du VBA (Ctrl+G), il est simple de l'écrire dans des cellules d'une feuille quelconque (sans oublier d'incrémenter le n° le ligne au fur et à mesure des For Each), voir les exemples précédents.
Bon codage
Pierre
Sub Calten_2()
Dim DataSet As Object, Prg As Object, Elem As Object, Elem2 As Object, lg As Integer
With Sheets("Feuil1")
Set DataSet = Rcdst_Jsn(.Range("A1").Value)
Set Prg = DataSet.programme
Debug.Print Prg.dateProgrammeActif
Debug.Print Prg.timezoneOffset
'etc ...
For Each Elem In Prg.reunions
Debug.Print Elem.hippodrome.libelleCourt
'etc ...
For Each Elem2 In Elem.courses
Debug.Print Elem2.libelle
'etc ...
Next Elem2
'etc ...
Next Elem
End With
Set DataSet = Nothing
Set Prg = Nothing
End Sub
Bonjour,
Merci Pierrep56 pour votre aide et votre disponibilité, les choses deviennent plus claires grâce à vous. Merci également pour le code qui va m'aider à démarré mes premiers codes.
Bon dimanche
Bravo Pierre, le pape du json ...