VBA interrogation critères multiples base SIRENE

Bonjour

nouveau sur ce forum, je cherche à poursuivre le fil :Extraire des données dans un fichier fermé

https://forum.excel-pratique.com/viewtopic.php?f=2&t=123141&start=30

dans lequel Pierrep56 propose d'interroger la base SIRENE à partie d'Excel, via vba

Pour élargir les champs de recherche, je voudrais pouvoir interroger en utilisant plusieurs paramètres, par exemple nom d'établissement + commune.

Je bloque car je voudrais dans ma requête utiliser ? ou * pour rechercher par exemple nom d'établissement "mairie" dans les communes contenant "longu"

Déjà sur l'api de sirene V3 je n'y arrive pas (0 résultats si longu, alors que 4 enregistrements si longue)

en vba cela correspond à ma variable :

monfiltrage = "enseigne1etablissement%3Amairie+and+libellecommuneetablissement%3Alongue"

est-ce possible ?

je joins mon fichier "brouillon" dans lequel pour l'instant la variable est figée dans le code mais qui devrait évoluer en multicritère que je déposerai ici avec plaisir quand (si) ce sera ok

Merci

Bonjour pierrep56

je suis tombé là dessus, mais je ne suis pas du tout un spécialiste des API

file:///C:/Users/eseig/Desktop/Raccourcis%20Affaires/SIRENE/INSEE%20Documentation%20API%20Sirene%20Services-V3.5.pdf

Bonjour,

Comme il est précisé dans cette doc, le jocker est un tilde (~)

Comme ça:

monfiltrage = "enseigne1etablissement%3Amairie+and+libellecommuneetablissement%3Alongu~"

Ici page 32 :

Comme il est précisé dans cette doc, le jocker est un tilde (~)

Bien vu!

Merci à vous je vais tester quand je rentrerai.

J'étais effectivement arrivé sur ces pages mais à partir de la 31 on parle de ~ * et ?. Comme l'étoile me semblait mieux répondre à mes besoins ...

P68 variable enseigne1etablissement ces jokers sont annoncés comme valides , je dois sûrement mal utiliser

À suivre

Bonsoir

suite de mes recherches, en dialoguant avec opendatasoft, il apparait que la doc dont je parlais est une doc API insee et non API opendatasoft

sur le site opendata l'onglet API utilise la V1 et est très limité, notamment sur les jokers

une API V2 existe, j'ai avancé (requête) mais il me reste un pb pour la récupération car le descriptif de fichier a changé

je récupère le nb d'enregistrements en modifiant

.AddCode "function getNhits(){return jsn.nhits;}" en .AddCode "function getNhits(){return jsn.total_count;}"

par contre pour les champs, .AddCode "function getFields(i){return jsn.records.fields;}" ne marche pas .

en regardant le json, il me semble voir un étage de plus sur la V2 : "records", "record" et "fields"

je joins mon fichier qui regroupe requêtes APIV1 et APIV2

Si vous avez une idée pour les champs...

44testapiv1.xlsm (43.98 Ko)

Holà l'ami, nul besoin de tripatouiller du javascript, on peut faire très simplement avec des "VBA.CallByName"

Voir démo : l'établissement en C2, le début de la ville en F2 la chaine de recherche composée en A4, et roule ... le tout grâce au tilde indiqué par Oxydum

Pierre

170sirene.xlsm (26.80 Ko)

Bonjour

je vais regarder cela. J'avoue qu'avec ce sujet je suis largement sorti de ma zone de confort…

je vous tiens au courant bien sûr

De retour et toujours plein de questions hélas,

Dans l'appel vers l'APIV1 (pierrep56) je comprends le principe (script et vba.callbyname)

Dans le fichier joint, feuille SIRENE = proposition précédente de Pierrep56, liée au module vba APIV1.

dans le vba, j'ai juste ajouté une ligne dans la partie script pour visualiser le json(texte) sur la feuille brut

maintenant je veux adapter le code pour l'APIV2. J'ai commencé avec SIRENEV2, brutV2 et le module vba APIV2

je visualise le Json en feuille brut V2 et il me faut non plus récupérer les champs de "records" , mais aller jusqu'au "fields".

Le vba.callbyname peut'il être utilisé ? peut-on récupérer, à partir de l'objet initial : "Set Brut = Obj_Rcdst2(URL)", un objet RECORDS, un objet RECORD …

je ne cherche pas un code optimisé mais un code pour comprendre comment manipuler ces objets, j'ai indiqué les étapes que je veux visualiser sur SIRENEV2, afin de décrypter le vba permettant d'y arriver.

Merci encore

54sirene-1.xlsm (51.42 Ko)

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

re

je rejoins un fichier

77sirene-2.xlsm (38.83 Ko)

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

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

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

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

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()

240sirene-3.xlsm (61.21 Ko)

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 ... https://forum.excel-pratique.com/viewtopic.php?p=830189#p830189

capture d ecran 291

J'en ai extrait le json qui m'intéressait pas un split et une suppression des backslashes

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.

214sirene-3.xlsm (78.23 Ko)

Bon, maintenant, PQ est orienté utilisateur, le résultat est là, mais expliquer la chose ?

Cdlt.

J'ai juste rencontré récemment une difficulté ... qui est qu'un json était lui-même un élément d'un autre json ... https://forum.excel-pratique.com/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

38recup-shenzar.zip (33.37 Ko)

Merci Pierre pour ton éclairage et ta solution.

D'ailleurs voici un nouvel exemple de json gigogne ! voir dans les horaires d'ouverture (timetable).

capture d ecran 328

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 & ")")

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 ecran 329
Rechercher des sujets similaires à "vba interrogation criteres multiples base sirene"