Extraire des données dans un fichier fermé (recherchev ou équivalent)

Bonjour à toutes et à tous,

1) Je suis comptable pour plusieurs sociétés et je dois vérifier la liste des identifiants SIREN / SIRET, adresse, etc... de leurs fournisseurs, pour cela j'ai à disposition une extraction de la base fournisseurs de chacune de ces sociétés au format XLSX, ces fichiers sont composés d'autant de lignes que la société a de fournisseurs et de plusieurs colonnes qui correspondent aux informations relatives à ces fournisseurs.

2) Pour effectuer cette vérification j'ai récupéré sur le site de l'INSEE l'intégralité des entreprises et établissements enregistrés en France dans un fichier au format CSV (8Go) et j'ai scindé celui-ci à l'aide d'un petit soft (KFK) en 11 fichiers de 700Mo que j'ai retravaillé un par un pour ne garder que les informations qui m'intéressent et enfin je les ai enregistrés au format XLSX ce qui me donne 11 fichiers "source" de 58 Mo.

3) Je souhaiterai faire à partir de chacune des extractions citées dans le premier paragraphe une RechercheV ou quelque chose d'équivalent et de rapide qui à partir de la valeur indiquée dans une des colonnes irait chercher la correspondance dans les 11 fichiers "source" sans avoir besoin de les ouvrir.

4) Pour l'instant j'ai essayé avec RechercheV mais chacun des 11 fichiers "source" comporte environ 800.000 lignes, donc soit je les ouvre au fur et à mesure, un par un et je rentre ma formule dans le fichier extraction pour la recopier dans toutes les lignes pour lesquelles aucune valeur n'a été trouvée dans la recherche précédente et dans ce cas l'ouverture de chaque fichier prend au moins une minute, puis comme je mets un filtre pour ne garder que les lignes qui sont sans valeur, Excel lance un recalcule qui dure un certain temps également, soit je n'ouvre pas le fichier source mais là c'est blocage d'Excel…

5) En recherchant sur le net j'ai vu quelques sujets par rapport à la lecture d'informations dans des fichiers qui sont fermés mais je ne pense pas que ce soit tout à fait ce que je souhaite obtenir.

J'espère avoir été clair dans ma demande et je vous remercie par avance pour votre aide et votre lecture.

Bonjour, je vais tenté d'apporter ma pierre à l'édifice

Voilà ce que j'imagine :

Les 11 fichier sources comportent donc tous des noms de sociétés. Je pense qu'il faudrait commencer par trier tout ça par ordre alphabétique. Je m'explique : dans le fichier source n°1 mettre à l'intérieur toutes les sociétés dont la première lettre commence entre A et C uniquement et renommer le fichier "Source A-C"

Déjà, si cette étapes est impossible inutile de lire la suite ...

Ensuite quant on voudras faire un RechercheV on analysera la première lettre de la société souhaitée, si la lettre est par exemple B, on sais qu'il faudra effectuer le RechercheV dans le classeur Source A-C

Bien évidement tout cela est possible en utilisant du code VBA. Je pense qu'avec des formules se serais beaucoup trop compliqué, lourd et lent.

Sinon pour faire un rechercheV dans un classeur fermé voilà la formule magique :

'Faire une RECHERCHE Verticale dans un classeur fermé: 'Rechercher "DVP" dans la colonne A du classeur fermé et 'afficher la donnée correspondante de la colonne B. =RECHERCHEV("DVP";'C:\dossier\excel\[ClasseurBase.xls]Feuil1'!$A:$B;2;FAUX)

Mais n'est il pas possible d'effectuer un RechercheV sur un classeur .csv ???

Je te propose d'ouvrir un nouveau poste en parallèle de celui la ayant par exemple pour tire "Effectuer un RechercheV dans un fichier .CSV"

Bonjour GGautier,

Tout d'abord, merci d'avoir pris le temps de me lire et de me répondre.

En effet il est possible de trier les classeurs source par ordre alphabétique des sociétés, pour ma part je les avais triés par ordre croissant d'identifiant SIRET, mais le problème c'est qu'il peut y avoir des sociétés dont le nom commence par A dans n'importe quel fichier ou alors il faudrait faire un travail supplémentaire au préalable sur l'ensemble des fichiers.

Si ce travail est faisable via VBA, oui pourquoi pas, de toutes façons je suis ouvert à toute proposition qui me permettrait un gain de temps final.

En ce qui concerne ton second message, je ne pense pas qu'il soit possible de faire une recherchev sur un fichier CSV mais sans certitude, je vais voir si je trouve quelque chose sur ce sujet.

Merci pour ton aide.

Bonjour,

une solution possible,

mettre les données des 11 fichiers sur une base Access

et faire une requête ou tcd

Bonjour i20100,

Je ne maîtrise pas Access et nous n'avons pas cet outil au bureau…

Merci pour ta réponse.

Bonjour à tous.

Il me semble que l'idée de la requête est une bonne piste. On peut aussi requêter sur du xlsx.

Est-il possible d'avoir un exemple de quelques lignes (une dizaine suffit) d'un des fichiers de données pour pouvoir faire une proposition?

Pierre

re,

autre solution avec Power Query,

voir ici:

https://forum.excel-pratique.com/viewtopic.php?f=2&t=122976&p=751784&hilit=power+query#p751784

Power Query est gratuit, voir ici:

https://www.microsoft.com/fr-ca/download/details.aspx?id=39379

Bonsoir pierrep56,

Voici un des 11 fichiers que j'ai retravaillé avec quelques lignes seulement.

Pour information les données contenues à l'intérieur ne sont pas confidentielles puisque disponibles sur des sites tel que Data.gouv.fr

Merci par avance pour votre aide sur le sujet.

136sirene999-v3.xlsx (13.69 Ko)

i20100,

Je ne connais pas encore PowerQuery mais je vais suivre le lien proposé et voir ce qu'il est possible de faire avec cet outil.

Pour information j'avais essayé d'ouvrir un des gros fichiers texte avec PowerPivot et le PC m'avait bloqué en me signalant un manque de ressource mémoire...

Je regarde votre lien et je reviens vous donner le résultat aussitôt que possible.

Merci pour votre investissement en temps et pour vos propositions.

Re, je vous laisses investiguer du coté des autres propositions, je vous propose quand même une autre idée se basant sur mon premier poste :

188recherchev.xlsx (637.37 Ko)

Bonjour à tous,

Voici une proposition via requête.

On part de l'idée que tous les fichiers de données :

* sont dans un même dossier,

* contiennent tous un onglet nommé "sirene_v3.csv"

Dans ce même dossier on colle le fichier "Recup_SIREN.xlsm".

Le numéro SIREN recherché est saisi en A1.

Le clic sur le bouton :

* liste les fichiers xlsx

* recherche la ligne correspondante au n°

* inscrit le résultat à partir de la ligne 2

Bien sûr avec le mini fichier de données le résultat est très rapide.

Ce le sera probablement moins avec les 11 gros fichiers. A tester.

Je mets en PJ le fichier et les 2 fichiers de données-test.

Est-ce que ça convient?

Pierre

PS. Voici le code principal :

Sub Cherche_SIREN()
Dim i As Integer, Req As String, T As Variant

    ReDim T_Fichier(0)
    List_Fichiers ThisWorkbook.Path & "\"
    With ActiveSheet
        .Range("A2:AA4").ClearContents
        For i = 0 To UBound(T_Fichier)
            If Right(T_Fichier(i), 4) = "xlsx" Then
                Connect_Xls T_Fichier(i)
                Req = "SELECT * FROM [sirene_v3.csv$] WHERE `SIREN`=" & .Range("A1").Value
                T = Select_Db(Req)
                If UBound(T) > 1 Then
                    .Range("A2").Resize(UBound(T, 1), UBound(T, 2)) = T
                    Close_Cnx
                    Exit For
                End If
                Close_Cnx
            End If
        Next i
    End With
End Sub
77recup-siren.zip (54.39 Ko)

Bonjour pierrep56,

Votre fichier fonctionne mais si j'ai bien compris cela implique que je rentre manuellement les numéros de SIREN un par un dans la cellule A1 pour vérifier s'ils sont correct ?

Pour ma part je souhaite partir d'une extraction de la base fournisseurs d'une société dans lequel il y a toutes les informations les concernant, dont le numéro de SIRET qui a été enregistré au moment de la création de chaque fiche fournisseur.

Dans ce fichier j'ai plusieurs centaines de SIRET qui sont renseignés dans la colonne W et je voudrai qu'à partir de l'information contenue dans cette colonne, Excel recherche dans les 11 fichiers récupérés sur le site de l'INSEE et m'indique si cette valeur existe bien.

Sinon j'ai travaillé un peu avec Power Query depuis hier et j'arrive a des résultats qui sont pas mal mais il faut que je charge manuellement le premier fichier INSEE, que je fasse ma RechercheV, que je copie colle les valeurs de ma RechercheV, que je supprime la feuille source, que je tri ensuite sur les résultat non trouvé et que je recommence avec le fichier suivant... jusqu'à ce qu'Excel m'indique qu'il manque de mémoire et qu'il ferme tout sans que je n'ai rien sauvegardé évidemment...

Le fait de passer par Power Query est déjà beaucoup plus rapide que lorsque j'ouvrai les fichiers XLSX de manière classique mais j'aimerai pouvoir automatiser ces opérations en faisant une sauvegarde après chaque RechercheV par exemple pour gagner du temps.

J'ai mis en pièce jointe un exemple de l'extraction de la base fournisseurs dans lequel j'ai supprimé certaines informations, du coup le SIRET enregistré au départ et qui serait la valeur à rechercher ce trouve dans la colonne F et je voudrai que le résultat de cette recherche se apparaisse dans la colonne J.

Merci pour votre aide et vos propositions.

Re, je vous laisses investiguer du coté des autres propositions, je vous propose quand même une autre idée se basant sur mon premier poste :

Bonjour GGautier,

Dans mon cas de figure tous les SIRET à rechercher sont renseignés dans une colonne, donc j'applique votre formule sur autant de colonnes qu'il y a de fichiers mais c'est très long...

Merci pour votre aide et vos propositions.

Ok,

On peut bien sûr adapter le code pour lancer une recherche en série.

Voici un exemple avec le dernier fichier proposé.

La recherche se fait sur le SIRET de la colonne F

Et les info trouvées sont inscrites en colonnes J, K, L, M

Pour la démo j'ai limité la recherche aux 5 premières lignes.

Pour l'ensemble de la liste, il suffit de changer For i = 2 To 6 par For i = 2 To lg dans le début du code

Pour l'ensemble de la liste, comme la liste est longue, le traitement de l'ensemble de la série sera long ...

(à lancer à midi avant la pause déjeuner?)

Pour mes tests, j'ai ajouté une ligne avec un SIRET contenu dans la base démo Sirene999_v3.xlsx

Ce fichier joint est aussi à placer dans le dossier contenant les 11 fichiers de base d'info

Pierre

Sub Serie_SIRET()
Dim lg As Long, i As Long

    Application.ScreenUpdating = False
    ReDim T_Fichier(0)
    List_Fichiers ThisWorkbook.Path & "\"

    With Sheets("DAS2")
        lg = .Cells(Rows.Count, 1).End(xlUp).Row
         For i = 2 To 6  ' <= pour la démo/test
        'For i = 2 To lg ' <= pour l'ensemble des lignes
            If IsNumeric(.Cells(i, "F").Value) And Len(.Cells(i, "J").Value) = 0 Then
                Cherche_SIRET i, .Cells(i, "F").Value
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub
40recup-siren-b.xlsm (140.76 Ko)

Bonsoir pierrep56,

Votre code fonctionne en effet avec le petit fichier mais je l'ai essayé en activant la ligne For i = 2 To lg et Excel me bloque.

Sinon, j'ai presque trouvé la solution idéale avec PowerQuery, donc je vais chercher la feuille qui se trouve dans chaque fichier source via une macro et je fais une recherchev dessus, puis je la ferme et je vais chercher la deuxième feuille toujours via la même macro et ainsi de suite jusqu'au dernier fichier.

Par contre j'ai un soucis dans l'exécution de cette macro car à chaque fois je suis obligé de d'aller dans les requêtes de connexions et supprimer manuellement la connexion à la feuille précédemment ouverte sinon la macro s'arrête en mode débogage, est-ce qu'il y a moyen de supprimer cette connexion automatiquement via vba ?

Bonne soirée.

Bonsoir,

PowerQuery, je ne sais pas l'utiliser.

En revanche, je propose une autre solution.

Il est possible de consulter en opendata la base Sirene en ligne sur le site =>

https://data.opendatasoft.com/explore/dataset/sirene_v3%40public/

Ce site dispose d'une API, qui permet d'interroger la base à distance depuis excel.

Ma proposition de ce soir utilise donc cette possibilité : on lit ligne par ligne les info "à distance" pour compléter le tableau. Ici une vingtaine de ligne du fichier "DAS2 2018 Societe X.xlsx"

A NOTER : il me semble que le fichier "DAS2 2018 Societe X.xlsx" contient des lignes en doublon (574 lignes uniques au lieu de 1617).

Pour cette démo j'ai pris les premières lignes dédoublonnées.

Cette solution permet de lire une base INSEE toujours mise à jour.

La recherche prend toujours du temps (10 secondes environ pour les 20 lignes de la démo, soit 1/2 secondes par ligne), mais il est possible de copier/coller des groupes d'une cinquantaine de lignes et de lancer plusieurs fois la recherche (11/12 fois pour les 574 enregistrements uniques)

Bonne soirée

Pierre

Bonjour à tous,

Pendant le ptit-déj, je pensais à une amélioration possible.

Un écueil à cette longue liste, c'est le temps de traitement nécessaire.

Pour essayer de remédier à cet inconvénient, on peut essayer de scinder le traitement en plusieurs fois.

Avec le code ci-après, on traite bloc de lignes par bloc de lignes par clics successifs.

Ici pour la démo et pour mes tests le nombre de lignes est 3, mais on peut augmenter comme on veut en modifiant la variable nb au début du code (20 ou 40 ça me parrait jouable avec mes tests)

Ainsi on peut ajouter l'ensemble des lignes au fichier démo et faire la recherche d'info de façon successive jusqu'à la fin de la longue liste.

Sub Lire_sirene()
Dim lg As Long, nb As Long, i As Long, T As Variant
Dim DataSet As Object, Rcd As Object, Rc As Object, Fld As Object

    nb = 3
    With Sheets("DAS2")
        lg = .Cells(Rows.Count, "J").End(xlUp).Row + 1
        ReDim T(1 To nb, 1 To 4)
        For i = lg To lg + nb - 1
            If IsNumeric(.Cells(i, "F").Value) And Len(.Cells(i, "J").Value) = 0 Then
                Set DataSet = oRecordSet(BASE_SIRENE & .Cells(i, "F").Value)
                Set Rcd = VBA.CallByName(DataSet, "records", VbGet)
                For Each Rc In Rcd
                    Set Fld = VBA.CallByName(Rc, "fields", VbGet)
                    T(i - lg + 1, 1) = VBA.CallByName(Fld, "siretsiegeunitelegale", VbGet)
                    T(i - lg + 1, 2) = VBA.CallByName(Fld, "adresseetablissement", VbGet)
                    T(i - lg + 1, 3) = VBA.CallByName(Fld, "codepostaletablissement", VbGet)
                    T(i - lg + 1, 4) = VBA.CallByName(Fld, "libellecommuneetablissement", VbGet)
                Next Rc
            End If
        Next i
        .Range("J" & lg).Resize(UBound(T, 1), UBound(T, 2)) = T
    End With
    Set DataSet = Nothing
    Set Rcd = Nothing
    Set Fld = Nothing
End Sub

Pierre

PS : @Steelson, je pensais bien que du json ça allait t'amuser!

Bonsoir pierrep56,

J'ai essayé votre second fichier et je récupère bien les informations voulues, demain je vais tester en augmentant la variable comme vous l'indiquez et en essayant d'adapter votre code à ma base originale et je vous tiens au courant.

Par contre, je ne connais pas du tout ce système d'API, je vais essayer de me documenter sur ce sujet qui semble très intéressant.

Merci beaucoup pour votre aide et votre investissement pour m'aider à résoudre ce problème.

Bonjour

La doc de cette API est disponible ici =>

https://help.opendatasoft.com/apis/ods-search-v1/#search-api-v1

Pierre

Rechercher des sujets similaires à "extraire donnees fichier ferme recherchev equivalent"