Lire un fichier Excel sans l'ouvrir avec VBA [Résolu]

Signaler
-
Messages postés
15501
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
3 janvier 2021
-
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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775
bonjour,
je ne vois pas très bien comment c'est possible sans l'ouvrir.
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 > 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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 > Kenza
il y a surtout de multiples façons d'ouvrir un fichier csv:

1) l'ouvrir "à la main" en utilisant Excel
2) l'ouvrir "à la main" en utilisant un éditeur de texte comme notepad, ou d'autres programmes similaires plus performants
3) l'ouvrir en VBA comme un classeur Excel
4) l'ouvrir en VBA comme un fichier texte

comment mentionnerais-tu l'emplacement?

"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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

Commen peut-on ouvrir un fichier en VBA?

Exemple d'un Emplacement d'un fichier sous vba:

Dim fichier_source
Fichier_source= c:\bureau \fichier.csv
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 > Kenza
Messages postés
15501
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
3 janvier 2021
1 425
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
15501
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
3 janvier 2021
1 425
Re,

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

Oui , j'ai déjà programmé
Messages postés
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555
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
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555 > 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
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020

Comment on peux le faire avec un fichier text s'il vous plait?
Messages postés
15501
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
3 janvier 2021
1 425
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?
>
Messages postés
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020

Tous les champs sont séparés par des "' ; '"
Messages postés
15501
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
3 janvier 2021
1 425 > 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
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555 > 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.
>
Messages postés
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020

Bonjour,

Merci beaucoup pour cette explication, mais comme je suis débutante j'arrive pas vraiment à comprendre ce que vous voulez dire.

je voulais mettre en PJ le fichier pour que vous poussiez voir sa structure mais apparemment ce n'est pas possible.

moi quand j'ouvre le fichier avec un éditeur texte je vois que:
- des points virgule entre les champs (après il revient à la ligne )
- pas de d'apostrophe ni guillemet
-le nombre de champs est 8
Messages postés
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555 > kenza
Tu peux poster une version allégée de ton fichier (une centaine de lignes suffisent) sur un site de partage comme https://mon-partage.fr/ et mettre le lien obtenu dans ton prochaine post
OK c'est fait.
vous trouvez sous ce lien le ficher :

https://mon-partage.fr/f/ZMFBRCBV/
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775
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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

Ouiii c'est ce que je cherche
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 > 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
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555
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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775
ce que fait le code suggéré en #30.
>
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 > 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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

oui ça fonctionne sur un petit fichier mais par contre sur mon fichier réel ça tourne depuis 1H 30 de temps et ça n'a pas encore terminé.
est ce qu'il y a une façon d'optimiser le temps s'il vous plait ?
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 > kenza
tu as fait deux choix: travailler en VBA et ne pas splitter le fichier.
ces deux choix sont sources de lenteur.
jusqu'à présent, tu n'as pas mentionné que cela devait aller vite.
il est préférable, dès le départ, de donner le maximum d'explications.
Messages postés
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555
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
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555
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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775
erreur sur le split?
>
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 > kenza
sur quelle ligne de code l'erreur se produit-elle?
>
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

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
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555
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
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020

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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 > kenza
qu'as-tu essayé?
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 >
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

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

par
colonne = 5
Messages postés
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555
Sur la feuille de lancement en E4 il suffit d'écrire E à la place de D !
d'accord

merci beaucoup
Messages postés
15501
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
3 janvier 2021
1 425
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
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020
1 555 >
Messages postés
15501
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
3 janvier 2021

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
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021
775 >
Messages postés
8322
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
31 décembre 2020

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
15501
Date d'inscription
dimanche 25 novembre 2007
Statut
Membre
Dernière intervention
3 janvier 2021
1 425 >
Messages postés
13780
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
5 janvier 2021

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