Vba interrogation critères multiples base SIRENE Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'149
Appréciations reçues : 187
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 14 février 2020, 14:35

En fait pour lire un Json quel qu'il soit, il faut avoir quelques notions de base.

1/ la base d'un json c'est un couple clé/valeur : ex "siren": "309203966"

2/ un fichier Json est un ensemble de clé/valeur selon une hiérarchie souple, c'est à dire qu'un "enregistrement" peut contenir ou plus - ou moins - ou autant de "colonnes" qu'un autre "enregistrement" (d'ou le 'on error resume next' ... très laid, ok, mais ici indispensable)

3/ une valeur peut être numérique/texte/tableau/ou un autre sous-ensemble contenant lui-même des couples clé/valeurs

4/ Il est facile d'afficher la hiérarchie d'un json, dans Firefox par exemple, donc de lire simplement les clés et de repérer leurs niveaux (/!\ il peut arriver d'avoir des clés identiques mais à des niveaux différents)


Maintenant, explications du code proposé précédemment :

- on récupère le contenu-object du json avec la fonction Obj_Rcdst

- de cet object on peut lire directos les éléments de niveau 1 avec un VBA.CallByName, par exemple : Nb = VBA.CallByName(Brut, "nhits", VbGet) : ici la valeur est numérique on peut donc la stocker dans une variable numérique "Nb As Integer"

- pour les sous-ensemble contenant eux-même d'autres sous-ensembles, on peut les stocker dans une variable Object, ici Rcd => Set Rcd = VBA.CallByName(Brut, "records", VbGet)
Pour cet ensemble, il suffit de boucler sur chacun des items pour récupérer les valeurs de niveau n+1 => d'ou la boucle For Each. Ce qui permet d'appeler à nouveau des VBA.CallByName sur ce niveau

- et si à nouveau il y a d'autres sous-ensembles dans un sous-ensemble, on peut à nouveau boucler à l'intérieur même d'une boucle pour lire les items des items

- pour un tableau du genre "geolocetablissement": [47.733034,-3.364529], on va les lire en splitant simplement ce tableau du genre :
Split(VBA.CallByName(Elem, "geolocetablissement", VbGet))(0) => pour lire ici 47.733034

Avec ça on peut lire à peu près n'importe quel json, mais Steelson pourra sûrement ajouter d'autres info à connaitre

Après pour commencer, et encore une fois, je déconseille plutôt les injections de javascript à la volée ...

Bonnes suites
Pierre
2 membres du forum aiment ce message.
E
ERIC S
Jeune membre
Jeune membre
Messages : 33
Appréciations reçues : 3
Inscrit le : 7 février 2020
Version d'Excel : 365
Contact :

Message par ERIC S » 14 février 2020, 17:41

re

je rejoins un fichier
SIRENE (2).xlsm
(38.83 Kio) Téléchargé 5 fois
j'avais bien compris les bases du fichier json, le vba.callbyname au premier niveau (Set Rcd = VBA.CallByName(Brut, "records", VbGet))
mais c'est après, comment passer du niveau "brut" à celui de "records" puis "record", pour arriver à "fields" ?
Le code ci-dessous (module APIV2) ne fonctionne pas
        URL = BASE_SIRENE & .Range("B4").Value
        Set Brut = Obj_Rcdst1(URL)
        Nb = VBA.CallByName(Brut, "total_count", VbGet)
        .Range("B5").Value = Nb
        ReDim T(1 To Nb, 1 To 10)
        Set Rcd = VBA.CallByName(Brut, "records", VbGet)
        idx = 1
            Set Fld = VBA.CallByName(Rcd, "record", VbGet)
            Set Fld1 = VBA.CallByName(Flf, "fields", VbGet)
            For Each Elem In Fld1
                T(idx, 1) = VBA.CallByName(Fld, "siret", VbGet)
                T(idx, 2) = VBA.CallByName(Fld, "siren", VbGet)
                idx = idx + 1
            Next Elem
    
        .Range("B7").Resize(UBound(T, 1), UBound(T, 2)) = T
 
"Mieux utiliser Excel pour mieux piloter son activité" : faites un tour sur www.XLpourTPME.fr
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'149
Appréciations reçues : 187
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 15 février 2020, 10:44

Mouais...

Bon, la méthode est toujours la même :
* un sous-ensemble dans un object
* si le sous-ensemble contient des "enregistrements" identiques => une boucle For each
* pour les valeurs numériques/texte/tableau => retour= VBA.CallByName(...
* si le sous-ensemble contient un autre sous-ensemble => Object + un autre niveau imbriqué de For each, etc ...


Maintenant, soyons pragmatique :
1/ la méthode que je propose dans le fichier démo du 13 février interroge la base SIRENE V3 via api/records/1.0/search/?dataset=sirene_v3. Elle fonctionne, on peut compléter ET la requête Et le jeu de valeurs retourné.

2/ la méthode indiqué dans votre fichier du 14 février interroge la même base SIRENE V3 via api/v2/catalog/datasets/sirene_v3. Vous n'arrivez pas à vous en sortir.

Il me semble que s'il y a des choix à faire, il y a ici une bonne piste ...
Maintenant chacun fait ce qu'il veut.

Pierre

PS : juste pour info, un bon lien =>
https://data.opendatasoft.com/explore/d ... blissement
1 membre du forum aime ce message.
E
ERIC S
Jeune membre
Jeune membre
Messages : 33
Appréciations reçues : 3
Inscrit le : 7 février 2020
Version d'Excel : 365
Contact :

Message par ERIC S » 15 février 2020, 11:33

Merci de votre patience

je connaissais le lien puisque c'est à partir de ce lui-ci que je me suis posé la question des requêtes (les téléchargements même de quelques milliers d'enregistrements sont longs)

mon but n'est pas seulement d'avoir une solution, là, et merci, j'en ai une partielle (le ~ n'étant pas la panacée car il restreint moins les résultats…) mais comme je ne veux pas mourir idiot, j'aimerais comprendre ce qui cloche dans le code que j'ai mis dans le post précédent

Je vous pose donc une dernière question, pourquoi ce code ne fonctionne-t'il pas, compte tenu de de la structure de la réponse reçue et que j'ai mise en feuille brutV2 de Sirene(2)

1/ Brut est un objet, il contient une valeur total_count que l'on récupère par vbacall…
2/il contient aussi un sous ensemble records
Set Rcd = VBA.CallByName(Brut, "records", VbGet) récupère ce sous ensemble à partir de brut
3/ ce sous ensemble contient un autre sous-ensemble record
je pensais que Set Fld = VBA.CallByName(Rcd, "record", VbGet) récupérait record dans records
4/ ce sous ensemble record contient un tableau fields
je pensais que Set Fld1 = VBA.CallByName(Flf, "fields", VbGet) récupérait fields dans record
5/ la boucle for next devait alors récupérer les enregistrements dans fields

or il semble que déjà 3 ne fonctionne pas et comme en vba pas de debug sur les objets (j'ai juste l'info Nothing), j'ai dû rater un truc…
C'est donc la seule question qui me reste sur ce sujet, pour me coucher moins bête que je me suis levé.
"Mieux utiliser Excel pour mieux piloter son activité" : faites un tour sur www.XLpourTPME.fr
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'149
Appréciations reçues : 187
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 15 février 2020, 13:28

Il suffit d'appliquer ce que j'ai expliqué précédemment ... :
Sub demo_pour__Eric_S()
Dim Url As String
Dim Brut As Object, Rcd_S As Object, Rcd As Object, elem As Object, Fld As Object

    Url = "https://data.opendatasoft.com/api/v2/catalog/datasets/sirene_v3%40public/records?start=0&rows=10&timezone=UTC&where=siret=41359863200011&select=siren,%20siret"
    Set Brut = Obj_Rcdst(Url)
    Set Rcd_S = VBA.CallByName(Brut, "records", VbGet)
    For Each elem In Rcd_S
        Set Rcd = VBA.CallByName(elem, "record", VbGet)
        Set Fld = VBA.CallByName(Rcd, "fields", VbGet)
        MsgBox "Siret=" & Fld.siret & vbCrLf & "Siren=" & Fld.siren
    Next elem
       
    Set elem = Nothing
    Set Fld = Nothing
    Set Rcd = Nothing
    Set Rcd_S = Nothing
    Set Brut = Nothing
End Sub


Function Obj_Rcdst(Url As String) As Object
Dim ScrC As Object

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", Url, False
        .send
        Set ScrC = CreateObject("MSScriptControl.ScriptControl")
        ScrC.Language = "JScript"
        Set Obj_Rcdst = ScrC.Eval("(" & .responseText & ")")
    End With
End Function
Pierre
1 membre du forum aime ce message.
E
ERIC S
Jeune membre
Jeune membre
Messages : 33
Appréciations reçues : 3
Inscrit le : 7 février 2020
Version d'Excel : 365
Contact :

Message par ERIC S » 17 février 2020, 12:33

RE-bonjour le forum

comme annoncé voici le fichier réalisé pour consulter la base SIRENE V3 (Siret des entreprises - 30 millions d'enregistrements) à partir d'Excel, en mode multicritères.
- interrogation via API V1 d'opendatasoft
- interrogation via API V2 d'opendatasoft
Donc c'est faisable, reste à chacun de fouiller dans les champs pour aller plus loin (lien site dans fichier)

Merci aux plus que contributeurs de ce site, car je partais de très loin. Et re-désolé Tatiak d'avoir aussi lamentablement raté ta dernière proposition Sub demo_pour__Eric_S()
SIRENE (3).xlsm
(61.21 Kio) Téléchargé 5 fois
1 membre du forum aime ce message.
"Mieux utiliser Excel pour mieux piloter son activité" : faites un tour sur www.XLpourTPME.fr
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 14'039
Appréciations reçues : 830
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR
Téléchargements : Mes applications

Message par Steelson » 17 février 2020, 17:35

pierrep56 a écrit :
14 février 2020, 14:35
Avec ça on peut lire à peu près n'importe quel json, mais Steelson pourra sûrement ajouter d'autres info à connaitre
oh non, tu restes le maître en ce domaine !

J'ai juste rencontré récemment une difficulté ... qui est qu'un json était lui-même un élément d'un autre json ... viewtopic.php?p=830189#p830189
Capture d’écran (291).png
J'en ai extrait le json qui m'intéressait pas un split et une suppression des backslashes

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Avatar du membre
Jean-Eric
Fanatique d'Excel
Fanatique d'Excel
Messages : 16'429
Appréciations reçues : 674
Inscrit le : 27 août 2012
Version d'Excel : 365 Personnel

Message par Jean-Eric » 18 février 2020, 11:47

Bonjour à tous,
Le sujet est clos.
Un peu curieux de voir comment Power Query pouvait gérer ce type de question, j'ai effectué une (des) recherche(s).
A partir du lien (B3) et des critères (B4), Récupérer et transformer, A partir du Web, PQ récupère le tout, et sans grande difficulté, on obtient les tables (résultats) recherchées.
SIRENE (3).xlsm
(78.23 Kio) Pas encore téléchargé
Bon, maintenant, PQ est orienté utilisateur, le résultat est là, mais expliquer la chose ?
Cdlt.
Jean-Eric

Je ne réponds pas aux M.P. non sollicités.
Avatar du membre
pierrep56
Membre impliqué
Membre impliqué
Messages : 1'149
Appréciations reçues : 187
Inscrit le : 18 juin 2014
Version d'Excel : 2016

Message par pierrep56 » 18 février 2020, 12:17

Steelson a écrit :
17 février 2020, 17:35
J'ai juste rencontré récemment une difficulté ... qui est qu'un json était lui-même un élément d'un autre json ... viewtopic.php?p=830189#p830189
Bonjour à tous,

Alors pour le json de Shenzar, il y a 2 particularités :
1/ les slashes sont probablement dûs à la création du json par du code Php. Le \ étant un caractère d'échappement classique en Php. En fait un simple effacement de ces slashes ne fait rien de bon, un p'tit traitement un peu plus alambiqué est nécessaire (cf code proposé)

2/ une fois le json remis au format json "classique", il convient de récupérer les différentes clés qui sont codées du genre "dz-P-4-10-A" soit en regex : "[a-z][a-z]-[A-Z]-\d-\d\d-[A-Z]"
Et là j'ai supposé que ces clés pouvaient être variables d'un json à un autre, d'où le regex
On place donc ces clés dans un tableau ordinaire via un classique Set Matches = reg.Execute(S)

Ensuite c'est tout simple de boucler sur les clés de ce tableau pour récupérer les différentes valeurs :
    For i = 2 To UBound(Tcode) + 1
        Set elem = VBA.CallByName(Acm, Tcode(i - 2), VbGet)
        Tr(i, 1) = Tcode(i - 2)
        Tr(i, 2) = elem.binUtilization
        Tr(i, 3) = elem.emptyBinCount
        ' ... etc ...
Pour la démo, j'ai repris ce qui m'a semblé être le résultat de sa requête en le plaçant dans un simple fichier texte : rep.txt
Les 2 fichiers du zip (xlsm et txt) sont à placer dans un même dossier. Ici pour la démo le code lit simplement le rep.txt, mais il est simple de remplacer le premier contenu de S par un S = .responseText d'un objet "MSXML2.XMLHTTP"

Le code utilisant les regex, il convient de s'assurer de la présence de la réf "Microsoft VBScript Regular Expression 5.5" sur son ordi.

Pour finir peut être que Jean-Eric peut nous proposer une méthode pour lire ce json avec son "PQ"?

Pierre
Recup_shenzar.zip
(33.37 Kio) Téléchargé 3 fois
Avatar du membre
Steelson
Fanatique d'Excel
Fanatique d'Excel
Messages : 14'039
Appréciations reçues : 830
Inscrit le : 13 octobre 2014
Version d'Excel : 2013 FR
Téléchargements : Mes applications

Message par Steelson » 18 février 2020, 13:12

Merci Pierre pour ton éclairage et ta solution.

D'ailleurs voici un nouvel exemple de json gigogne ! voir dans les horaires d'ouverture (timetable).
https://public.opendatasoft.com/api/rec ... _7&q=52000
Capture d’écran (328).png
Ce que j'ai fait (sans le publier pour le moment) c'est que j'ai réinjecté un élément textuel qui contenait un json dans le moteur de script en itérant donc.
horaires = VBA.CallByName(elem3, "timetable", VbGet)
Set horairesObj = ScriptEngine.Eval("(" & horaires & ")")
json public.opendatasoft.xlsm
(26.66 Kio) Téléchargé 4 fois
pierrep56 a écrit :
18 février 2020, 12:17
Pour finir peut être que Jean-Eric peut nous proposer une méthode pour lire ce json avec son "PQ"?
oui ce serait bien de voir PowerQuery traiter ce type de json. A noter que firefox le laisse en l'état de texte.
Capture d’écran (329).png

O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸ O.o°• ♪♪♫ °º¤ø,¸¸,ø¤º°`°º¤ø,¸
PI = 3.14159 26535 89793 23846 26433 83279 50288 41971 69399 37510 58209 74944 59230 78164 06286 20899 86280

( ͡• ͜ʖ ͡• )
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message