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 ça fonctionne

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 :

https://offline.turfinfo.api.pmu.fr/rest/client/7/programme/29052019/R1/C1/citations?paris=SIMPLE_GAGNANT&specialisation=OFFLINE&groupe=true

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

54turf-calten.xlsm (26.22 Ko)

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.

https://offline.turfinfo.api.pmu.fr/rest/client/7/programme/31052019?meteo=true&specialisation=OFFLINE

Et celui-ci aussi

https://offline.turfinfo.api.pmu.fr/rest/client/7/programme/31052019/R4/C1/masse-enjeu-v2?specialisation=OFFLINE

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 :

https://offline.turfinfo.api.pmu.fr/rest/client/7/programme/31052019?meteo=true&specialisation=OFFLINE

La structure se présente ainsi :

190601071742438711

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 ...

Rechercher des sujets similaires à "importer json"