Lire un fichier Excel sans l'ouvrir avec VBA

Résolu/Fermé
-
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022
-
Bonjour,

je souhaite extraire les données d'un fichier Excel très volumineux (lire le fichier sans l'ouvrir).

les données que je veux extraire comporte l'information suivante :

les cellules du champ code-techno = '4GF'. --> [ ce champ se trouve sur la colonne D]

Est ce que c'est possible de m'aider à trouver une solution avec VBA s'il vous plait ?

En vous remerciant d'avance pour vote aide.



Cordialement,
Kenza

14 réponses

Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003
bonjour,
je ne vois pas très bien comment c'est possible sans l'ouvrir.
Le fichier est tres volumineux, il comporte plus que sa capacité donc c'est pas possible de l'ouvrir c'est pour cela je cherche une solution d'extraire ces infos en utilisant un code vba.
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003 > Kenza
cela veut dire quoi "il comporte plus que sa capacité"?
quel est le format exact de ce fichier?
ton explication n'est pas très claire, je suppose que tu veux commencer par examiner toutes les valeurs dans la colonne D?
>
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022

Un fichier qui compre plus que 1000000 de ligne quand j'essaie de l'ouvrir je ne peux pas donc il faut utiliser un split pour le découper moi je ne veux pas utiliser le split c'est pour cela je cherche une façon de traiter ce fichier.

Pour le format du fichier c'est l'extension csv

Par rapport a ta 3 eme question, oui il faut examiner toute la colonne D et chercher le mot '4GF' ensuite extraire les lignes entières (les copier sur un autre fichier excel par exemple)
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003 > Kenza
"je ne peux pas", ce n'est pas très factuel. que se passe-t-il quand tu essaies?
un fichier csv peut simplement être ouvert comme un fichier texte. as-tu essayé cela?
>
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022

En fait moi ce que je veux savoir est ce qu'on peux traiter ou extraire des données d'un fichier sans l'ouvrir on peux par exemple juste mentionner l'emplacement et le nom du fichier.
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022
1 580
Bonjour a vous deux,

yg_be, ca roule?

Kenza:
Quelle Excel avez vous?
Bonjour,

la version Excel: 2002
le numéro de la version: 12527.21330
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022
1 580
Re,

Ok, je ne connais pas cette version. Vous avez deja programme en VBA avec votre Excel?
>
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022

Oui , j'ai déjà programmé
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733
Bonjour,

On est toujours obligé d'ouvrir un fichier pour accéder à son contenu !
Prétendre qu'on accède à un fichier fermé est une erreur de langage.

On peut accéder à un fichier sans avoir besoin de le charger dans une application (contrairement à Excel ou à un éditeur de texte), par exemple en accédant directement au contenu avec Open pathname For ... ou en ouvrant un flux avec ADODB.Stream.
On peut alors utiliser une lecture ligne à ligne pour n'extraire que ce qu'on désire.

Ta demande est plutôt vague, que veux-tu extraire exactement ?
Uniquement les données d'un champ, plusieurs champs, des enregistrements complets ?
Quels sont les critères : égal à, qui contient, ... ?

Ceci dit, CSV n'est pas un format standardisé, il faut préciser :
- le séparateur d'enregistrements (de lignes)
- le séparateurs de champs (de cellules)
- l'identificateur de texte (pour le cas où un texte contient un des séparateurs ci-dessus).
- le séparateur décimal (point ou virgule)
- le séparateur de milliers s'il est utilisé.

Cordialement
Patrice

Personne ne peut détenir tout le savoir, c'est pour ça qu'on le partage.
Oui vous avez tout à fait raison , j'ai juste pas bien exprimé mon problème.

Moi je veux extraire des données d'un fichier excel sans être obligé de charger le classeur. Je veux juste faire des traitements sur ce fichier et récupérer des données sans l'ouvrir.

Par rapport aux données que je veux extraire :
Il exsite un champ qui s'appelle code_techno ce champ se trouve sur la colonne D qui comporte plusieurs informations , moi je veux appliquer un filtre sur ce champ et laisser que l'info "4GF" en suite faire copier toutes les autres colonnes et les coller sur un autre fichier excel.

Merci pour votre aide @patrice :D
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733 > Kenza
Avec un fichier csv c'est assez simple mais avec un fichier Excel (en particulier avec les anciens formats non XML) c'est bien plus complexe !
>
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021

Comment on peux le faire avec un fichier text s'il vous plait?
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022
1 580
Re bonjour a tous

Kenza:
des données d'un fichier excel
C'est un fichier csv (texte) ou Excel parce que vous switchez entre les deux en fonction du moment

Connaissez vous la programmation VBA?
RE

Non un fichier texte -->extension csv
Par rapport à la programmation Vba je suis débutante
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733 > Kenza
Si c'est un csv, réponds à mes questions sur les séparateurs et les critères.
>
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021

Tous les champs sont séparés par des "' ; '"
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022
1 580 > Kenza
Bonjour a tous,

Kenza:
Vous devez repondre a toutes les questions de Patrice3340 plus une:
Les "lignes" du fichier ont toutes le meme nombre de "champs" (format delimite)
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733 > Kenza
«Tous les champs sont séparés par des "' ; '" »
Ça confirme que ce fichier "csv" n'est pas au format "classique", csv signifie en français, valeurs séparées par des virgules.
Il est donc important de répondre à toutes les questions y compris celle de f894009.
En général, les règles sont les suivantes :
- le séparateur de lignes est le couple CR LF (13 10 ou en hexa 0D 0A) mais ça peut être simplement CR ou LF.
- le séparateur de champs est la virgule mais en milieu francophone c'est souvent le point virgule notamment quand la virgule sert de séparateur décimal
- l’identificateur de texte est le guillemet anglais (" ) mais parfois c'est l'apostrophe ('). Il n'est pas forcement utilisé. Il est obligatoire lorsqu'un champs contient un séparateur de lignes et/ou un séparateur de champs, dans ce cas si le champs contient un identificateur de texte celui-ci est doublé. Parfois il est mis systématiquement sur tous les champs.
- le séparateur décimal est le point mais quand le séparateur de champ est le point virgule, le séparateur décimal est souvent la virgule.
- le séparateur de milliers est l'apostrophe (') mais il n'est pas toujours utilisé et quand le séparateur décimal est la virgule, le séparateur de millier est l'espace.
- certains csv passent à l'enregistrement suivant (à la ligne) lorsque tous les champs suivants sont vides d'où la question de f894009 sur le nombre de champs de chaque ligne.

Comme tu peux le constater il n'y a pas de csv standard, d'où l'importance de connaître avec précision la structure de ton fichier. Cela nécessite d'analyser le fichier avec un éditeur de texte et de code hexadécimal.

D'autre part pour extraire les lignes, il faut connaitre avec précision les critères d'extraction.

Edit : CR signifie retour chariot et LF ligne suivante, comme sur les anciennes machines à écrire.
OK c'est fait.
vous trouvez sous ce lien le ficher :

https://mon-partage.fr/f/ZMFBRCBV/
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003
cela t'aiderait-il d'avoir un code VBA qui crée un autre fichier csv, avec uniquement les lignes ayant code_techno = 4GF ?
>
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022

Ouiii c'est ce que je cherche
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003 > Kenza
suggestion:
Option Explicit

Sub kenza4gf()
Dim fichier_source As String
Dim fichier_dest As String
Dim numin As Long, numout As Long, valss() As String, ligne As String
fichier_source = ThisWorkbook.Path + "\fichier.csv"
fichier_dest = ThisWorkbook.Path + "\fichier4gf.csv"
numin = FreeFile
'ouvre le fichier en lecture
Open fichier_source For Input As #numin
numout = FreeFile
Open fichier_dest For Output As #numout
'boucle tant que l'on n'a pas atteint la fin du fichier
While Not EOF(numin)
  Line Input #numin, ligne
  valss = Split(ligne, ";", 5)
    If UBound(valss) > 2 Then
        If valss(3) = "4GF" Then
            Print #numout, ligne
        End If
    End If
Wend
Close #numin 'fermeture
Close #numout
End Sub
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733
Bonjour

Un exemple avec un stream :
https://mon-partage.fr/f/6rAx7eFH/

Le code :
'————————————————————————————————————————————————————————————————————————————————————————————————————————————————
' Module    : mImportCsvFiltre
' Objet     : Lecture et filtrage ligne à ligne d'un gros fichier cvs UTF8
'————————————————————————————————————————————————————————————————————————————————————————————————————————————————
' 21/12/2020    Patrice33740        V1-0-00  Création
'
' Établir la référence à ADODB :  Microsoft ActiveX Data Objects 6.1 Library
'
Option Explicit
'
' Séparateurs
Const sepL$ = vbCrLf                                 'séparateur de lignes
Const sepV$ = ";"                                    'séparateur de valeurs
Const idTxt$ = """"                                  'identificateur de texte chr(34)
'

Sub Lire_csv_UTF8_filtre()
' Choix et lecture du fichier csv en filtrant
'
Dim wbk As Workbook
Dim nomComplet As Variant, colonne As Long, filtre As Variant

  On Error Resume Next
  colonne = Feuil1.Columns(Feuil1.Range("E4").Value).Column
  If Err <> 0 Then MsgBox "Colonne erronée", vbCritical: Exit Sub
  On Error GoTo 0
  If Feuil1.Range("E5").Text = "" Then MsgBox "Préciser la valeur à filtrer", vbExclamation: Exit Sub
  filtre = Feuil1.Range("E5").Value
  ' Choix fichier
  nomComplet = ChoisirFichier(".csv", ThisWorkbook.Path & "\")
  If nomComplet = "" Then Exit Sub
  Set wbk = Lire_Filtrer_csv_UTF8(nomComplet, colonne, filtre)
  wbk.Saved = True

End Sub

Private Function Lire_Filtrer_csv_UTF8(ByVal nomCompletFichier As String, col As Long, filtre As Variant) As Workbook
' Lecture et filtrage ligne à ligne d'un [très gros] fichier au format csv encodé UTF8 (avec ou sans BOM)
'
Dim fUtf8 As ADODB.Stream
Dim wbk As Excel.Workbook
Dim cel As Range
Dim txt As String
Dim lgn As String
Dim lgr As Long

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Set wbk = Application.Workbooks.Add(xlWBATWorksheet)
  Set cel = wbk.Worksheets(1).Range("A1")
  Set fUtf8 = New Stream
  With fUtf8
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .LineSeparator = adCRLF
    .Open
    .LoadFromFile nomCompletFichier
    Do Until .EOS
      lgn = .ReadText(-2)         '-2 = une ligne
      If cel.Row = 1 Then
        Call EcrireLigneCSV(lgn, cel)
      Else
        Call FiltrerLigneCSV(lgn, cel, col, filtre)
      End If
    Loop
    .Close
  End With
  Set fUtf8 = Nothing
  wbk.Worksheets(1).Columns.AutoFit
  wbk.Worksheets(1).Rows.AutoFit
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Set Lire_Filtrer_csv_UTF8 = wbk
  
End Function

Private Sub FiltrerLigneCSV(lgn As String, cel As Range, col As Long, filtre As Variant)
' Filtrage et écriture d'une ligne d'un fichier au format csv
'
Dim txt As String
Dim frm As String
Dim lgr As Long
Dim nbC As Long
Dim t As Variant
Dim i As Long
  
  If lgn = "" Then Exit Sub
  t = Split(lgn, sepV)
  If t(col - 1) = filtre Then
    For i = LBound(t) To UBound(t)
      cel.Offset(0, nbC).FormulaLocal = t(i)
      nbC = nbC + 1
    Next i
    Set cel = cel.Offset(1)
  End If
End Sub

Private Sub EcrireLigneCSV(lgn As String, cel As Range)
' Ecriture d'une ligne d'un fichier au format csv
'
Dim txt As String
Dim frm As String
Dim lgr As Long
Dim nbC As Long
Dim t As Variant
Dim i As Long
  
  If lgn = "" Then Exit Sub
  t = Split(lgn, sepV)
  For i = LBound(t) To UBound(t)
    cel.Offset(0, nbC).FormulaLocal = t(i)
    nbC = nbC + 1
  Next i
  Set cel = cel.Offset(1)
End Sub

Private Function ChoisirFichier(ByVal strExtension As String, Optional ByVal strChemin As String = "") As String
' Choix d'un fichier
'
Dim dlgParcourir As FileDialog
  
  If strChemin = "" Then strChemin = ThisWorkbook.Path
  Set dlgParcourir = Application.FileDialog(msoFileDialogFilePicker)
  With dlgParcourir
    .InitialFileName = strChemin
    .Title = "Sélectionner un fichier " & strExtension & " :"
    .AllowMultiSelect = False
    .InitialView = msoFileDialogViewDetails
    .ButtonName = "Sélection fichier"
    If .Filters.Count > 0 Then .Filters.Delete
    .Filters.Add "Fichiers " & strExtension, "*" & strExtension, 1
    If .Show = -1 Then ChoisirFichier = .SelectedItems(1) Else ChoisirFichier = ""
  End With
  Set dlgParcourir = Nothing

End Function


merci beaucoup pour ce code ça fonctionne mais pas pour mon fichier qui comporte environ 30000000 lignes quand j'ai exécuté la macro avec ce fichier ça saute je pense que c'est normale pcq ça dépasse la capacité d'un fichier Excel (il arrive à remplir un million de lignes après le code s'arrête)

il n y a pas un moyen d'enregistrer directement le résultat (le filtre ) sans l'afficher sur le classeur ?

ou bien rajouter une condition ( si ça dépasse le nombre max d'une feuille il crée une autre feuille par exemple )
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733
C'est ce que fait le code de yg_be au post #30
>
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021

@patrice le code au poste 30 ne fonctionne pas pour moi malheureusement .

votre code fonctionne bien, c'est juste que je trouve pas comment enregistrer le résultat sans l'afficher en utilisant votre code .

est ce que vous pouvez m'aider s'il vous plait ?
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003
ce que fait le code suggéré en #30.
>
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022

merci beaucoup pour votre aide mais le code suggéré en #30 ne fonctionne pour moi.

est ce que je peux vous demander s'il vous plait de le mettre sur le site de partage avec mon exemple peut être c'est moi qui s'est trompé quelques part . et me dire si c'est possible les changement que je dois faire pour que le code fonctionne chez moi

excusez moi pour toutes ces questions, je suis vraiment débutante et je n'arrive pas à comprendre vraiment ce code.

merci encore une fois pour votre réactivité (@patrice et @yg_be) :D
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003 > kenza
le code doit être dans un fichier Excel se trouvant dans le même dossier que fichier.csv.
il crée un fichier nommé fichier4gf.csv, toujours dans le même dossier.
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733
Re,

Voici une adaptation du code de yg_be :
https://mon-partage.fr/f/io41NRNu/
Merci beaucoup @patrice

Le même problème ça tourne depuis 2h de temps
Je sais pas s'il y a un moyen d'optimiser le temps
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733
Il faut découper le fichier en fichiers plus petits.

Chez moi pour 2 millions de lignes ça met 12 secondes.
15 x 12 = 3 minutes
il vient de finir le traitement après 4H
j'ai eu une erreur à la fin :

erreur d'exécution 14
espace de chaine insuffisant
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003
erreur sur le split?
>
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022

non , c'est une erreur qui s'affiche après avoir exécuter le code vba de @patrice sur mon fichier qui comporte 120000000 de ligne
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003 > kenza
sur quelle ligne de code l'erreur se produit-elle?
>
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022

Normalement c'est sur cette ligne
Do while not EOF(n°F)
effectivement sur un fichier de 10 millions ça fonctionne super bien (je vais juste découper mes fichier en fichiers plus petits )

milles merci à tout le monde vous êtes génial.

problèmes résolus :D
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733
Content que ça fonctionne, pourvu que ça dure ...
1 million de lignes il y a 3 jours, 30 millions hier, 120 millions aujourd'hui ...
Ton fichier grossit très vite !
>
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021

hhh ouiii

il y a eu une modification de dernière minute , le champ code_techno se trouve maintenant sur la colonne E et non pas la colonne D.

est ce que c'est possible d'adapter le programme en prenant compte ce changement s'il vous plait ? j'ai essayé de le faire mais ça fonctionne pas :(
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003 > kenza
qu'as-tu essayé?
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003 >
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022

je pense qu'il faut remplacer
colonne = Feuil1.Columns(Feuil1.Range("E4").Value).Column

par
colonne = 5
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733
Sur la feuille de lancement en E4 il suffit d'écrire E à la place de D !
d'accord

merci beaucoup
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022
1 580
Bonjour a tous,

Changement de "colonne"
En remplacent le Split par un Instr ca derait le faire

  Do While Not EOF(n°F)
    Line Input #n°F, ligne
    If Not donnees Then
      ' Titres
      Print #n°C, ligne
      donnees = True
    Else
        If InStr(ligne, filtre) Then
            ' Ligne filtrée
            Print #n°C, ligne
        End If
    End If
  Loop
  
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 733 >
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022

C'est bien moins rigoureux :
- ce n'est plus une recherche sur le champ complet, ça renvoie aussi un champ partiel
- la chaine cherchée peut se trouver dans un des autres champs ...
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022
1 003 >
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021

si on ajoute les ";" devant et derrière le filtre, cela fait une recherche sur le champ complet (dans n'importe quelle colonne, en effet).
ce pourrait-il que l'usage de nombreux split conduise à l'erreur?
erreur d'exécution 14
espace de chaine insuffisant
Messages postés
16203
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
25 janvier 2022
1 580 >
Messages postés
18316
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
25 janvier 2022

Re,
Merci yg_be, en effet ça verrouille mieux pour cette recherche.