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 :
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...
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
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
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
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
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()
oh non, tu restes le maître en ce domaine !Avec ça on peut lire à peu près n'importe quel json, mais Steelson pourra sûrement ajouter d'autres info à connaitre
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
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.
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
Merci Pierre pour ton éclairage et ta solution.
D'ailleurs voici un nouvel exemple de json gigogne ! voir dans les horaires d'ouverture (timetable).
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 & ")")
oui ce serait bien de voir PowerQuery traiter ce type de json. A noter que firefox le laisse en l'état de texte.Pour finir peut être que Jean-Eric peut nous proposer une méthode pour lire ce json avec son "PQ"?