VBA recherche dans un fichier .txt
Hello Le forum,
J'aimerais vous sollicitez aujourd'hui concernant un problème de recherche dans un fichier texte.
Je m'explique, je fais une extraction de mon ERP de ma base de donnée articles en fichier texte, celui-ci fait environ 250'000 lignes.
Pour éviter d'importer une aussi grande quantité de ligne dans mon fichier, je vais faire une recherche directement dans le .txt
Malheureusement, j'ai deux problème :
Le premier étant que ma recherche ne s'effectue pas seulement sur la première colonne du .txt mais dans tout le fichier.
Et par conséquent, dès qu'il trouve une occurrence il l'affiche. Il arrive que mon numéro d'article apparaisse dans la désignation d'un autre article et donc vient s'arrêter sur la mauvaise ligne.
Le deuxième est au niveau de l'affichage du résultat.. J'aimerais sur mon fichier mettre la désignation trouver dans le .txt en colonne F, mais je n'arrive pas à trouver la bonne syntaxe pour lui dire de prendre ce qu'il y a après la première tabulation et avant la deuxième tabulation.
Je vous joint un exemple de l'Excel et du fichier texte pour que mes dires soit plus compréhensible.
Merci d'avance !
Kilian
Bonsoir Killian1906,
Dans le cas d'un fichier txt (comme en csv) il n'existe qu'une seule colonne mais des séparateurs permettent de dissocier les données.
Et c'est grâce à ces séparateurs que l'on peut afficher les données enregistrées en colonnes sur une feuille Excel directement ou par tableau.
Voir l'image ci-dessous.
Par contre si tu ne souhaites pas prendre toutes les lignes, je suppose que c'est plutôt par le code Article que tu filtrerais pour alimenter ton tableau ?
Et ainsi avoir uniquement les lignes correspondantes à l'article souhaité. Exemple: le 993125. Ou tu pourrais avoir besoin d'autres possibilités de filtres ?
Ici, j'ai préféré le code Article mais selon ta réponse je pourrais modifier le code.
Hello X Cellus,
Merci pour ta réponse.
Effectivement tu as juste, concrètement ce que j'aimerais c'est faire une recherche sur un code article, puis venir afficher sa désignation dans la feuille "Création_Affiche" colonne F.
En temps normal, j'aurais fait une RECHERCHEV sur une requête, mais comme le fichier fait 250'000 lignes, c'est bien trop lourd et long pour ceci.
Cette solution de la recherche dans le fichier texte me paraît rapide, après je n'ai fait le test que sur une ligne, il est possible que je le fasse sur des centaines de lignes depuis "Création_Affiche".
Encore merci pour ton aide.
Kilian
bonjour,
avec ceci on peut facilement filtrer ces 250.000 lignes, il faut encore ajouter le "texttocolumns"
Sub Search_TextFile()
Dim Path$, fileName$, Items$, FileNo, A, i, TextData, Fl
Path = ActiveWorkbook.Path ' Chemin vers le fichier
fileName = "ITEMS-1011" '- au lieu de _ !!!!!
FileNo = FreeFile 'premier numero libre
Open Path & "\" & fileName & ".txt" For Input As #FileNo
TextData = Split(Input$(LOF(FileNo), FileNo), vbCrLf) 'vers une matrice et séparer sur le CarriageReturnLineFeed
Close #FileNo 'fermer TXT
With Sheets("Création_Affiche").ListObjects("TBID") 'votre tableau
If .ListRows.Count = 0 Then MsgBox "rien": Exit Sub 'il n'y a pas de données = fin mission
A = .DataBodyRange.Value 'lire le contenu
End With
For i = 1 To UBound(A) 'boucle le contenu
If Len(A(i, 5)) > 0 Then 'article <>""
Fl = Filter(TextData, A(i, 5), 1, 1) 'filtrer la matrice pour les lignes contenant cet article
If UBound(Fl) > -1 Then Sheets("Teste").Range("A" & Rows.Count).End(xlUp).Offset(2).Resize(UBound(Fl) + 1).Value = Fl 's'il y en a, copier vers (pour le moment) feuille "Teste"
End If
Next
End SubHello BsAlv,
Merci pour ta réponse et proposition.
Ton code fonctionne évidemment, mais il y a quelque paramètre à prendre en compte.
Dans mon fichier .txt originale, il arrive que j'ai plusieurs fois le même article, même désignation, mais une autre donnée change.
Avec ton code, il vient donc m'insérer les données de tous les articles qu'il trouve.
De plus, comme je l'ai dit, il y a des désignations qui comporte le numéro d'article rechercher. Et donc ici je n'arrive pas à avoir la bonne ligne.
Je n'aimerais également pas créer une feuille test, mais insérer dans la feuille "Création_Affiche" en colonne F.
Concrètement, ton code réitère les même "erreurs" que le mien.
Mais je te remercie tout de même pour ta réponse.
A+,
Kilian
Bonjour
Cela se fait simplement par PowerQuery intégré à Excel
Dans le cas présent si on prend les champs Div., Mag., Article, l'article correspondant de ton txt n'a pas de désignation.
Remarques est un champ ajouté manuellement ?
Hello Chris,
Habituellement je le fais avec PowerQuery.
Mais le fichier texte étant volumineux, une requête ainsi qu'une boucle sur toutes les lignes prennent beaucoup de temps.
C'est pourquoi j'ai chercher à éviter d'importer ce fichier et rechercher directement à l'intérieur.
Mais peut-être existe t'il un moyen plus rapide ou je m'y prend mal.
EDIT : Oui le champs remarques est un champ rempli manuellement. Seul le champ désignation doit-être importé.
Mais ceci me servira de base pour plusieurs autres projet ou j'importerai des données depuis ce fichier .txt
Merci, A+
Kilian
Bonjour Kilian1906, Le Forum,
Donc, puisqu'il s'agit seulement du filtrage sur le numéro d'article. J'ai adapté un fichier de Patrice33470, servant à lire de gros fichiers (CSV ou TXT).
Ne pas oublier d'établir la référence à ADODB : Microsoft ActiveX Data Objects 6.1 Library. Voir dans l'éditeur Outils puis Références et cochez.
En ajoutant un Regex pour contrôler, vu que les articles ont différentes longueurs.
Deux boutons, le premier pour l'extraction et une fois celle-ci réalisée. Un deuxième pour l'inscription dans la première feuille.
Ce fichier très complet comporte plusieurs fonctions que tu pourras tester à l'occasion. Notamment si tu changes de filtrage ou si tu ne souhaites pas conserver la deuxième page qui sert de tableau de vérification. Et passer plutôt par un tableau mémoire.
RE
Habituellement je le fais avec PowerQuery.Mais le fichier texte étant volumineux, une requête ainsi qu'une boucle sur toutes les lignes prennent beaucoup de temps.
Non, si on n'importe pas les données dans un onglet, mais qu'on filtre dans la requête, on évite et le volume et la boucle VBA...
Il faut utiliser la puissance des requêtes pas juste l'import...
bonjour,
je ne vois pas encore le but final, quelles lignes sont des duplicates et tout ça ...
Donc des données brutes seront ajoutées et j'éspère dans les bonnes colonnes.
En attendant des nouvelles ... ou bien le PQ de Chris
Option Explicit
Sub Search_TextFile()
Dim Path$, fileName$, Items$, FileNo, A, i, TextData, Fl, Rijen, Kolommen, LO
Path = ActiveWorkbook.Path ' Chemin vers le fichier
fileName = "ITEMS-1011" '- au lieu de _ !!!!!
FileNo = FreeFile 'premier numero libre
Open Path & "\" & fileName & ".txt" For Input As #FileNo
TextData = Split(Input$(LOF(FileNo), FileNo), vbCrLf) 'vers une matrice et séparer sur le CarriageReturnLineFeed
Close #FileNo 'fermer TXT
Set LO = Sheets("Création_Affiche").ListObjects("TBID") 'votre tableau
With LO
If .ListRows.Count = 0 Then MsgBox "rien": Exit Sub 'il n'y a pas de données = fin mission
A = .DataBodyRange.Value 'lire le contenu
End With
With Sheets("aux") 'feuille auxiliaire
.Visible = xlHidden 'avec une feuille qu'on ne voit pas
.Cells.Clear 'vider
For i = 1 To UBound(A) 'boucle le contenu
If Len(A(i, 5)) > 0 Then 'article <>""
Fl = Filter(TextData, A(i, 5), 1, 1) 'filtrer la matrice pour les lignes contenant cet article
If UBound(Fl) > -1 Then .Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(Fl) + 1).Value = Fl 's'il y en a, copier vers (pour le moment) feuille "Teste"
End If
Next
If .Range("A2").Value = "" Then MsgBox "rien": Exit Sub
.Range("A2").CurrentRegion.TextToColumns Destination:=.Range("A2"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), DecimalSeparator:=".", ThousandsSeparator:=",", TrailingMinusNumbers:=True
.Range("A1").Value = "x" 'pourque A1 ne soit pas vide
With .UsedRange
A = .Offset(1).Resize(.Rows.Count - 1, 7).Value 'à partir de la ligne 2, 7 colonnes
End With
Rijen = Evaluate("=row(" & Range("A1").Resize(UBound(A)).Address(0, 0) & ")") 'sequence 1,2,3,...,nombre de lignes
Kolommen = Array(6, 7, 4, 3) 'ces 4 colonnes
LO.ListRows.Add.Range.Range("B1").Resize(UBound(A), 4).Value = Application.Index(A, Rijen, Kolommen) 'coller dans le tableau
End With
End SubA nouveau,
Correctif, j'ai omis sur la version postée d'apporter quelques modifications de la version test sur celle finale. Désolé.
Sur Regex il manquait le DIM.
Function chiffre(txt As String)
Dim Matches As Variant
'AJOUT d'un REGEX
With CreateObject("VBScript.RegExp"): .Global = True: .Pattern = "(\d+)": Set Matches = .Execute(txt): chiffre = Matches(0): End With
End FunctionEt sur le Sub Inscription la modif suite au non-filtrage de l'en-tête du fichier texte.
Sub Inscription()
With Sheets("Extrait")
For i = 1 To .Range("A" & Rows.Count).End(xlUp).Row
Range("A" & i + 1) = Now
Range("B" & i + 1) = .Range("G" & i)
Range("C" & i + 1) = .Range("H" & i)
Range("E" & i + 1) = .Range("D" & i)
Range("F" & i + 1) = .Range("E" & i)
Next i
End With
End SubHello,
Merci à tous pour vos propositions.
Je vais analyser et essayer tous ça.
Je ne penses pas réussir à regarder ça aujourd'hui, mais je vous fait un retour dès que possible.
A+,
Kilian
Hello,
J'ai donc analyser vos retours.
BsAlv ton fichier renseigne tout un tas de ligne dans la feuille "aux", j'ai essayer de modifié pour arrivé à mes fins, mais il ne trouve pas la ligne exacte que je veux.
X Cellus ton fichier de Patrice33470 est effectivement bien complet et je le garde au chaud il me servira certainement.
Par contre, pour mon projet ici, je n'arrive pas à l'adapté pour avoir ce que je veux.
Ton fichier, demande l'ouverture d'un fichier, pour moi le lien est connu. Il demande également l'article, qui doit être bouclé sur la colonne E.
J'ai essayer de modifié tout ça, mais n'ai pas réussi à tester car le fichier est lent et plante.. (peut-être dû à ma bécane qui devient vieille).
Est-ce qu'il y aurais une possibilité de créer un tableau mémoire ? Ce n'est pas ce que je connais le mieux et je peine à créer ceci.
Encore merci à vous !
Kilian
Bonjour Kilian1906,
Merci de ton retour. En effet il y a une demande de choisir un fichier car prévu aussi bien pour chercher des fichiers de type txt que csv.
Mais je te laisse le code ci-dessous pour démarrer avec seulement ton fichier. Je suppose qu'il est différent en réel et que celui posté est un nom pour l'occasion.
Sub FichTxt()
Dim nom As String, csv As Variant
csv = Tableau_csv_UTF8("items-1011.txt")
Sheets("Création_Affiche").ListObjects("TBID").DataBodyRange.Clear
Range("A2").Select
Call LisCSV
End SubIl suffira de remplacer items-1011.txt par celui nécessaire. Puis de lancer cette macro FichTxt.
Pour ce qui concerne l'article demandé, si c'est un article unique et que tu n'as pas besoin d'en chercher d'autres.
Sur la fonction Public Function Tableau_csv_UTF8(....
Remplace la ligne de code ou met là en commentaire.
'Article = InputBox("Numéro d'article: ", "Recherche sur Article")
Article = 993125
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
..... suite du code
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Functionpar
Article = 993125
Puis dans le code de la feuille change la macro existante par
Private Sub CmbExtrait_Click()
Sheets("Extrait").Range("A1:N10000").ClearContents
Call FichText
End SubVoilà
Hello X Cellus,
Effectivement le fichier est différent car je ne vous ai mis que deux trois exemples, mais comme dit il contient 250'000 lignes.
Merci pour tes modifs, mais c'est bien ce que j'ai fait avant.
Le numéro d'article ici sera variable, j'ai à disposition potentiellement 80'000 références différentes.
Mon but étant qu'en renseignant le numéro d'article sur la feuille "Création_Affiche" je puisse venir mettre la désignation, mais ceci sur plusieurs lignes.
J'ai donc également ajouté une boucle dans la procédure Tableau_csv_UTF8 pour répéter l'opération sur toutes les lignes de la feuille "Création_Affiche".
Je n'ai malheureusement pas pu voir le résultat car le fichier plante avant... Et donc pour éviter ceci, je me disais qu'un tableau en mémoire réduirait le temps d'exécution et éviterais mon plantage.
Merci, A+
Kilian
A nouveau,
Je n'ai malheureusement pas pu voir le résultat car le fichier plante avant
As tu un message d'erreur particulier ? lors du plantage.
Premièrement
Je viens d'essayer avec l'absence d'article dans le fichier. Normalement la première colonne (Article) doit disposer d'un numéro d'article.
Mais si celui est absent, alors le Regex qui renvoie uniquement des nombres va planter.
C'est pourquoi je lui ai ajouté une ligne de code avec On Error comme ci-dessous.
Function chiffre(txt As String)
Dim Matches As Variant
'AJOUT d'un REGEX
On Error Resume Next
With CreateObject("VBScript.RegExp"): .Global = True: .Pattern = "(\d+)": Set Matches = .Execute(txt): chiffre = Matches(0): End With
End FunctionLa fonction
Private Function TableauCSV(strCSV As String, sepV As String) As Variant
' Transpose le texte d'un fichier csv dans un tableau excel à 2 dimensions
ayant une procédure de contrôle d'erreur, avec affichage de celle-ci. Cela indique que le tableau est bien créé puis renvoyé à la première fonction.
Donc modifie une des lignes du FILTRAGE d'ARTICLES par
If Nb = Article And lgr < 100 Then lgr = lgr + 1: Sheets("Extrait").Range("A" & lgr) = tL(nL, 1)Ainsi cela va arrêter l'extrait aux 100 premiers références de l'article cherché. Et en l'absence de plantage, regarde sur la feuille Extrait.
Puis augmente la limite lgr avec par exemple 500, puis 1000 etc...Afin de voir ou se situe la survenance de l'erreur.
bonjour de nouveau,
Une autre piste, on importe vos 250k lignes du txt-file et puis on utilise un autofilter avec une matrice pour les 80k différents références
Mise à jour : la limite est +16k et le temps d'execution semble long
Mise à jour 2 : si on fait tout dans la 2ieme feuille avec 1 autofilter et un RechercheV, alors ... ???
250k lignes et 80k références uniques seront copié et collé d'une feuille à l'autre en 5.7 sec
Sub AutoFilteren()
Dim Fl
t = Timer
ReDim Fl(1 To 80000, 1 To 1)
For i = LBound(Fl) To UBound(Fl)
Fl(i, 1) = i
Next
Application.ScreenUpdating = False
Sheets("blad2").UsedRange.Offset(1).ClearContents
Sheets("blad1").Range("R1").Resize(UBound(Fl)).Value = Fl
With Sheets("blad1").Range("A1").CurrentRegion
.AutoFilter
.Offset(, 2).Resize(, 1).FormulaR1C1 = "=--(ISNUMBER(MATCH(RC2,R1C18:R" & UBound(Fl) & "C18,0)))"
.Range("C1").Value = "C"
End With
With Sheets("blad1").Range("A1").CurrentRegion
.AutoFilter 3, 1
.Offset(1).Copy
Sheets("blad2").Range("A2").PasteSpecial xlValues
End With
MsgBox Timer - t
End SubHello,
X Cellus, pour le plantage il ne s’agit pas d’une erreur VBA, mais simplement d’Excel qui ne répond plus.
Certainement dû à la lenteur, comme dit à BsAlv en privée, mon fichier est sur un serveur et prend donc déjà plus de temps à l’ouverture.
Mais merci pour la gestion des erreurs ☺️
Je n’ai pas beaucoup de temps ces jours pour essayer tous ça. Mais je fais au plus vite et vous donne des nouvelles.
Merci, A+,
Kilian