MACRO : Extractions base de données texte, recherche multicritères avec interrogation

**Moi9999** · 26/03/2012, 15h55

Bonjour à tous

J'ai déjà créé des macros, mais là celle là

......J'ai besoin de vos lumières

voilà j'ai une base de données texte qui change (le nombres de lignes varie disons toutes les semaines). Fichier en TXT.

Je voudrais faire une macro dans Excel comme suit :

avec question qui me demande ou se trouve le fichier de données

La macro ouvre le fichier txt sous excel

elle demande (car je dois pouvoir changer à chaque fois) le texte compris dans certaines colonnes de données (par exemple colonne A, je veux qu'il extrait les lignes comportant le texte par exemple ART, sachant que le texte peut etre mARTin donc je désire l'extraire car mARTin contient ART).

qu'elle me demande également pour la colonne B avec même type de critères (je veux qu'il extrait les lignes comportant le texte par exemple BLE, sachant que le texte peut etre mangeaBLE.

qu'elle extrait donc ces données trouvées avec critères de textes dans colonne A et B (et voir aussi une autre) comme vu ci dessus

Puis qu'elle l'enregitre dans une feuille de calcul Excel, mais surtout dans un fichier texte (comme la base de données source de départ), bien entendu en me demandant le nom du fichier d'enregistrement.

voici, voilà, j'espère avoir été clair

j'ai oublié de dire que le fichiers sources, comporte en ligne de texte, environ 4000 lignes (donc assez lourd), en txt le fichier fait environ 1,3 Mo

Merci bcp bcp d'avance pour vos lumières, car là je plante un pneu.....

**Golonne** · 26/03/2012, 17h10

Bonjour,

Tu plantes un peu ? As tu déjà écrit un peu de code ? Ta demande n'est pas très claire. Tu veux que le programme extraie tous les mots de la colonne A mais un peu plus loin tu indiques que le programme doit extraire des lignes depuis un critère dans la colonne B.

Pas pareil ça non ?

Un petit exemple ne ferait pas de mal...

En ce qui concerne :
1) "avec question qui me demande ou se trouve le fichier de données" : regarde la méthode GetOpenFileName dans F1 ou bien dans les tutoriels.
2) "La macro ouvre le fichier txt sous excel " : regarde la méthode OpenText de l'objet Workbooks ou bien essaie le code suivant extrait du tuto de JM Rabilloud :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Dim NumFichier As Integer, TabLigne() As String, TabCol() As String, _
Recup As String, NomFichier as String
Dim cmpt1 As Long, cmpt2 As Long
Dim Classeur As Workbook
 
NumFichier = FreeFile
 
    Open NomFichier For Binary Access Read As #NumFichier
        Recup = String(LOF(NumFichier), " ")
        Get #NumFichier, , Recup
    Close #NumFichier
 
Set Classeur = Workbooks.Add
    Classeur.Activate
        With Classeur.Worksheets(1).Range("a1")
            TabLigne = Split(Recup, vbCrLf)
                For cmpt1 = 0 To UBound(TabLigne)
                    TabCol = Split(TabLigne(cmpt1), ",")
                For cmpt2 = 0 To UBound(TabCol)
            .Offset(cmpt1, cmpt2).Value = TabCol(cmpt2)
        Next cmpt2
    Next cmpt1
End With

ça, ça marche tout le temps.

Pour le reste, j'attend de voir ton code et tes explications.

**Moi9999** · 27/03/2012, 15h01

Bonjour

Merci bcp Golonne

j'ai un message d'érreur en ce qui concerne la macro

Erreur de compilation: Instruction incorrecte à l'extérieur d'une procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

NumFichier = FreeFile

il surligne en jaune FreeFile

Oui je me suis mal exprimé
non je n'ai jamais développé en VBa

J'ai juste créé macro par enregistrement, d'ou mon pb pour le dossier en cours

Voilà j'ai des données en txt, des lignes d'enregistrements
le nb d'enregistrement change régulièrement et le nom du fichier aussi

je voudrais avoir un bouton relié à une macro qui ouvre ce document sous excel
bien sur qui sépare une ligne d'enregistrement par colonne

ensuite je dois extraire certains enregistrements mais pas tous
donc par critères, qui peuvent varié, sinon ce serait trop simple...,
mais ça on verra aprés.....

Merci bcp bcp

je suis débordé et pa le temps de m'y pencher sérieux... et c'est urgent... bref pas le top quoi.......

**Golonne** · 29/03/2012, 12h20

Bonjour Moi,

Ton programme doit démarrer par l'instruction Sub et finir par l'instruction End Sub

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Sub OuvrirFichierTexte()
....
'Ici le code
...
End Sub

Essaye ça pour l'ouverture du fichier texte :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Option Explicit
 
Sub OuvreTxtSousExcel()
Dim Source As String, Filtre As String
Dim NumFichier As Integer
Dim TabLigne() As String, TabCol() As String
Dim Recup As String
Dim Cmpt1 As Long, Cmpt2 As Long
Dim Classeur As Workbook
 
' Ouvrir une boite de dialogue pour sélectionner le fichier .txt
Filtre = "Fichiers texte (*.txt),*.txt"
Source = Application.GetOpenFilename(Filtre, Title:="Sélection du fichier")
Application.ScreenUpdating = False
 
'Convertir le fichier texte en classeur Excel
    NumFichier = FreeFile
 
    Open Source For Binary Access Read As #NumFichier
        Recup = String(LOF(NumFichier), " ")
        Get #NumFichier, , Recup
    Close #NumFichier
 
Set Classeur = Workbooks.Add
    Classeur.Activate
        With Classeur.Worksheets(1).Cells(1, 1)
            TabLigne = Split(Recup, vbCrLf)
                For Cmpt1 = 0 To UBound(TabLigne)
                    TabCol = Split(TabLigne(Cmpt1), ",")
                        For Cmpt2 = 0 To UBound(TabCol)
                            .Offset(Cmpt1, Cmpt2).Value = TabCol(Cmpt2)
                        Next Cmpt2
                Next Cmpt1
        End With
 
Set Classeur = Nothing
 
Application.ScreenUpdating = True
End Sub

C'est testé et ça doit marcher quelque soit la taille du fichier si ton séparateur est une virgule. Sinon modifie la ligne suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TabCol = Split(TabLigne(Cmpt1), " ici tu indiques ton séparateur ")

Pour la suite je vais jeter un oeil si tu attaches un txt en exemple et la liste des critères d'extraction.

Mais lis les tutoriels et l'aide en ligne (F1) ça t'aidera à comprendre le code.

Cordialement,

Golonne

**Moi9999** · 02/04/2012, 10h23

Merci bcp

cela fonctionne test effectué pour l'ouverture du fichier TXT.

Oui j'avais bien mis le sub, mais erreur deux entrées je suppose.

Par contre, je pense que ce que je veux n'ai pas gérable en vb sur Excel.

Je m'explique :

d'une part il faut que je sois sur 2007 car 2003 n'a pas assez de ligne, mon fichier en fait 760 000.

J'ai un fichier txt toujours sur la même forme, qui change tous les 15 jours, le nombres de ligne change, mais la forme est toujours la même, c'est à dire que chaque ligne fait 132 caractères.

Pour l'ouverture et la macro c'est ok, car aprés mes tests d'ouverture depuis msquerry, j'ai vu que je dois importer sans diviser par colonne.
Mes 132 caractères se retrouvent dans une seule colonne, aucun pb.

Ensuite je dois pouvoir trier mes lignes pour n'en extraire que certaines.

Et enregistrer en txt ces lignes extraites (mes tests me donne en txt ok mais avec des CR LF à la fin de chaque ligne, je voudrais que des LF. Mais bon ça on verra après.

Mais pb, les critères varient, je dois donc avoir une invite pour demander le critères choisi ou une liste de choix.

Voici un exemple de deux lignes (j'peux pô envoyer les 760 000 lignes... lol...) :

SAFRD AKW DN111380VDH N04534295E008061778 N04534295E008061778W0021002000 UYO 000231104
SAFRD AKW DN2S UU W002000162 000241104

Quand j'importe en txt, les espaces sont respectés entre les caractères et quand je reexporte également, donc c'est tout bon ;-)
ouf déjà ça

mes critères se situent entre des caractères, j'ai testé la fonction TROUVE.
Cela fonctionne mais j'ai des soucis d'enchainement pour ma macro etc..... j'suis pas assez bonne....... bouhhhhhhh

Alors voilà....
CRITERE 1 :
Je dois rechercher les caractères 1, 2 et 3 de chaque ligne (par exemple AFR pour mes deux lignes d'exemple ci dessus)

CRITERE 2 :
Je dois rechercher le caractère 5 de chaque ligne (par exemple ici D)

CRITERE 3 :
Je dois rechercher le caractère 6 de chaque ligne

CRITERE 4 :
recherche caractères 11 et 12 de chaque ligne

CRITERE 5 :
rechercher le caractère 13 de chaque ligne

Donc avec une invite demandant chacun des 6 critères qu'on recherche, en sachant qu'un peut etre laissé libre.
par ex, j'aurais critère 1, 2, 3, 5, 6 our je voudrais trouvé pour un texte mais le 4 je le laisse libre, sans critères.

la macro doit lancer ses recherches pour resortir (uniquement les lignes correspondantes aux critères choisis.
puis copier /coller sur une autre feuille ou autre fichier) ça ok pas de pb.

Voici, voilà exactement ce dont j'ai besoin.

Donc ouverture fichier txt, c'est résolu, merci bcp car pendant ce temps j'ai chercher sur le restant, et fait des tests déjà de recherches simples etc...

mais là je pêche.....

j'ai testé avec RECHERCHE, mais a priori ce n'est pas la bonne idée
avec TROUVE cela fonctionnait mieux, mais aprés comme dire pour multicritères !..... parmis ces lignes trouvée,s tu recherches xx caractères puis parmis celles là.....

Je me demande si en VB c'est possible ?...
sinon quel type de programme pourrait etre capable de faire cela?

MERCI bcp par avance

**Golonne** · 02/04/2012, 18h34

Salut Moi,

760000 lignes il vaut mieux avoir 2007 ou 2010 en effet, si tu passes par Excel, mais si j'ai bien compris ta demande, a priori on peut même se passer d'Excel tout court (sauf pour lancer la macro of course puisqu'on est en VBA)

Je reformule ta demande avant de coder, n'hésite pas à préciser si je me trompe.

1) Ouvrir le fichier FichierTxtSource
2) Sélectionner les critères
a) 'abc' des trois premiers caractères de la ligne
b) 'd' du cinquième caractère de la ligne
c) 'e' du sixième caractère de la ligne
d) 'fg' du onzième et douzième caractère de la ligne
e) 'h' du 13ème caractère de la ligne
3) Pour chaque ligne du fichier
Vérifier qu'il a 4 critères sur 5 de remplis
a) si tous les critères correspondent conserver la ligne
b) si tous les critères sauf 1 correspondent conserver la ligne
Ecrire la ligne dans FichierTxtCible
Ligne suivante

Ca te semble correct ?

Guillaume

**Moi9999** · 03/04/2012, 10h45

voui c'est cela à qques précisions prêt

1) Ouvrir le fichier FichierTxtSource
3) ne rechercher que dans les lignes qui commencent par la lettre "S"
2) Sélectionner les critères choisis par l'utilisateur en saisisant dans une invite ou liste de choix (à choix multiples) peut importe. (attention les critères peuvent etre des caractères ou des chiffres).
a) 'bcd' des 2 eme, 3eme et 4eme premiers caractères de la ligne
b) 'e' du cinquième caractère de la ligne
c) 'f' du sixième caractère de la ligne
d) 'kl' du onzième et douzième caractère de la ligne
e) 'm' du 13ème caractère de la ligne

3) Pour chaque ligne du fichier commencant par la lettre S. Si la ligne ne commence pas par "S" alors ne pas rechercher dans cette ligne.

Par contre la suite non : "Vérifier qu'il a 4 critères sur 5 de remplis" ce n'est pas cela

en fait les critères peuvent être multiples ou vides.
Par exemple l'utilisateur choisi les critères suivants :
a) "klm"
b) "f"
c) "1"
d) "kl" et "mn " et "xs"
e) vide, c'est à dire que tout est retenu

a) si tous les critères correspondent conserver la ligne
b) si tous les critères sauf 1 correspondent NE PAS conserver la ligne

Ecrire la ligne dans FichierTxtCible
Ligne suivante

Toutes les lignes du fichier Txt Source qui correspondent exactement aux critères choissis par l'utilisateur doivent être copiées à la suite les unes des autres dans le fichier TXT Cible.

j'espère avoir été plus claire... pas facile à expliquer.

encore milles mercis

**Golonne** · 03/04/2012, 12h46

Bonjour Moi,

Ouch ! je commence à avoir peur que cela dépasse mes faibles compétences

, néanmoins j'aime les challenges alors je m'y colle

Quelques questions :

1) Le critère de choix des lignes qui commencent par "S" doit il être codé en dur ou bien est-ce un critère variable supplémentaire ?
2) Le critère d) est il le seul à être multiple, ou bien peut avoir un triple choix sur chacun des critères ?
3) Corollaire de 2) => Quelle limite y a-t-il pour chaque combinaison de critères ? Au plus trois ? Plus ?
4) L'ordre dans lequel tu écris tes lignes dans FichierTxtCible est il important ? Doit-il respecter l'ordre du fichier original ou non ?

Mon idée pour l'instant -qui peut évoluer avec tes réponses- serait de créer un UserForm avec x TextBoxes qui permettrait à l'utilisateur de sélectionner ses critères de choix et des boutons pour lancer la macro et quitter. On traiterait directement que sur les fichiers textes sans passer par la feuille de calcul excel ce qui résout ton problème de version. Après si tu as besoin d'excel on peut toujours imaginer découper les fichiers en bloc de 65000 lignes.

A plus,

Guillaume

**Moi9999** · 03/04/2012, 14h46

voui j'te dis, dur dur........

1) Le critère de choix des lignes qui commencent par "S" doit il être codé en dur ou bien est-ce un critère variable supplémentaire ?

non c'est en dur, on ne recherche que dans les lignes commencant par un "S" toujours.

2) Le critère d) est il le seul à être multiple, ou bien peut avoir un triple choix sur chacun des critères ?

je vérifie et te dis cela exactement

3) Corollaire de 2) => Quelle limite y a-t-il pour chaque combinaison de critères ? Au plus trois ? Plus ?

je vérifie et te dis cela exactement

4) L'ordre dans lequel tu écris tes lignes dans FichierTxtCible est il important ? Doit-il respecter l'ordre du fichier original ou non ?

oui l'ordre est important, il doit respecter l'ordre du fichier original

Mon idée pour l'instant -qui peut évoluer avec tes réponses- serait de créer un UserForm avec x TextBoxes qui permettrait à l'utilisateur de sélectionner ses critères de choix et des boutons pour lancer la macro et quitter.

Oui c'est tout à fait cela qu'il faudrait.

On traiterait directement que sur les fichiers textes sans passer par la feuille de calcul excel ce qui résout ton problème de version. Après si tu as besoin d'excel on peut toujours imaginer découper les fichiers en bloc de 65000 lignes.

Non pas besoin de passer par excel si c'est possible. Mais je ne sais pas faire de macro etc... sans passer par excel.

je reviens vers toi aprés vérif concernant le nombre de choix par critère

**Golonne** · 03/04/2012, 18h00

Mais je ne sais pas faire de macro etc... sans passer par excel

Etant donné qu'on va faire ce programme en VBA, on aura toujours besoin d'Excel (ou d'une application Microsoft Office) pour servir de "plateforme de lancement". Il faudrait du "vrai" Visual Basic pour créer un exécutable indépendant d'Excel.

**Pierre Fauconnier** · 04/04/2012, 06h41

Salut.

Personnellement, voila comment je procéderais.

1) Récupération du fichier dans Excel
2) Mise en place d'une zone de critères pour filtre avancé
3) Expression des critères dans la zone de critères
4) Extraction
5) Déplacement de l'extraction dans une autre feuille
6) Sauvegarde dans un fichier

**Moi9999** · 04/04/2012, 14h37

Citation:
2) Le critère d) est il le seul à être multiple, ou bien peut avoir un triple choix sur chacun des critères ?

Voici la/les réponses :

a) Le critère des 2 eme, 3eme et 4eme caractères de la ligne,
Pour ce critère, il y aura 10 choix exactement, qui peuvent etre en dur dans le code sans problème, car ils sont fixes, 10 choix possibles.
Mais le choix devra être multiple (choix multiples possible). Et également un choix "tous".

Pour les autres critères (b, c, d), je vérifie si critère multiple ou pas, et je vous donne l'info dés que possible.

En tout cas merci du coup de main

**Golonne** · 04/04/2012, 16h12

Envoyé par Pierre Fauconnier

Salut.

Personnellement, voila comment je procéderais.

1) Récupération du fichier dans Excel
2) Mise en place d'une zone de critères pour filtre avancé
3) Expression des critères dans la zone de critères
4) Extraction
5) Déplacement de l'extraction dans une autre feuille
6) Sauvegarde dans un fichier

Bonjour Pierre,

Merci de ton aide. Elle est la bienvenue ! Je garde tes recommandations en tête avant de commencer à coder. Juste pour préciser, Moi9999 utilise Excel2003, je ne sais pas si elle peut avoir accès à la version 2007 et +. Et ses fichiers peuvent atteindre plusieurs centaines de milliers de lignes. D'où le recours aux fichiers texte. Cependant le filtre élaboré, c'est astucieux, je n'y avais pas pensé... Penser Excel avant de penser VBA ? OK !

Bonjour Moi9999,

Il serait temps que tu me fournisses la liste de tes critères et une version light de ton fichier texte (quelques centaines de lignes), pour voir à quoi ça ressemble. Merci.

Guillaume.

**Moi9999** · 05/04/2012, 10h36

Tout d'abord merci

Je suis passée en Excel 2007, donc plus de souci du côté de 2003.

Les fichiers font en moyenne 760 000 lignes.

Les fichiers sont en txt d'origine (ascii?), quand on les ouvre avec un editeur texte, comme notepad, les lignes se terminent par LF.
Ce qu'il faudra d'ailleur au final aprés l'exportation fin de ligne en LF et non en CR LF.

Concernant les critères, je vérifie s'ils sont multiples nécessairement ou pas. Je pense vous fournir cela demain au plus tard.

Je fais un extrait d'une centaine de ligne ok.

Bonjour à tous

Voici un extrait du fichier test nomé BASETEST.txt
C'est un extrait sur les 760 000 lignes. Je l'ai zippé.

Concernant les critères, je finalise afin d'être sur de la nécessité de choix unique ou multiple. Je reviens rapidement, ce soir surement.

Pour le moment le premier critère lui est déjà défini :
2 eme, 3eme et 4eme caractères de la ligne,
il y aura 10 choix exactement qui ne bougeront pas qque soit le fichier base source, qui peuvent etre en dur dans le code sans problème
Le choix devra être multiple (choix multiples possible).
Et également un choix "tous".

Pour plus de simplicité voici les critères "nommés"

Variable Area = caractères 2, 3, 4 de chaque ligne

Variable Section = caractère 5 de chaque ligne

Variable Subsect1 = caractère 13 de chaque ligne

Variable SubSect2 = caractère 6 de chaque ligne

Variable ICount = Caractères 11 et 12 de chaque ligne

**Moi9999** · 06/04/2012, 11h10

La macro d'ouverte renvoi une erreur "erreur d'excusion 1004 - erreur définie par l'application ou par objet"

quand je clique sur débogage ça débloque et le fichier TXT s'ouvre bien

Il renvoi l'erreur sur la ligne .Offset(Cmpt1, Cmpt2).Value = TabCol(Cmpt2)

tu as une idée ?

-----------

J'ai testé de réenregistrer en txt c'est ok ensuite quand je l'ouvre dans NotePad, les lignes sont alignées, donc espaces gardés, c'est ok, j'ai crains car sous Excel tout est décalé. Mais c'est Ok. Ouf. Merci bcp

------------

Pourrais tu me dire comment ouvrir le fichier txt dans le même classeur que celui ou j'aurais mes boutons reliés aux macro (ouverture txt/ extractions /enregistrer en txt), dans une nouvelle feuille avec invite box pour le nomage de la feuille ?

**Moi9999** · 17/04/2012, 15h33

Bonjour à tous
Je vais tester avec GREP car a priori les extractions sont assez puissantes pour les fichiers txt et cela parait le plus simple car je ne m'en sors pas avec les macros.

le comptage du nombre de caractères surtout, pour lui dire de trier les lignes dont le xx caractères est par exemple 1

Car concernant les filtres avancés avec zones de critères, ce n'est pas possible car j'importe le fichier txt tel quel, c'est à dire que chaque ligne est composée d'une seule colonne, sinon à l'export en txt il supprime les espacements qui doivent etre gardés.

Tks à tous

si vous avez une idée je suis tj preneuse