Importer correctement des fichiers txt et csv

**Flo3301** · 21/01/2011, 11h45

Bonjour à tous,

Voila maintenant des mois que je parcours ce forum afin de trouver les solutions à mes divers problèmes en matière de programmation, mais j'avoue que cette fois ci je bloque un peu pour optimiser mon code.

En fait, je dois importer un certain nombre de fichiers txt et csv (présent dans un même dossier qui contient des dizaines de milliers de fichiers) pour les inclure les uns à la suite des autres dans une ou plusieurs feuilles Excel.
J'ai à faire à 3 type de fichiers de structure différente :
Le premier type, .txt, 29 colonnes, séparateur ";"
Le second, .txt, 9 colonnes, séparateur ";"
Le troisième, .csv, 24 colonnes, séparateur "," et "/"
Les fichiers des deux premiers types sont respectivement copiés et collés l'un en dessous de l'autre (ils sont consolidés en fait car meme structure de colonne), dans une feuille excel (2 feuilles excel différentes donc)
Pour le troisième type je n'ai qu'un seul fichier à importer à chaque fois donc pas de consolidation dans ce cas la, mais seulement la création d'une troisième feuille (contenant les données) dans excel.

J'arrive parfaitement à importer les fichiers des 2 premiers types directement en utilisant le code suivant :

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
    w = Dir(Path)
 
    Do While w <> ""
        If InStr(w, best_quote(k) & EDate(i)) > 1 Then
                Workbooks.OpenText Filename:=Path & w, DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=";"
                If Not [a1].Offset(1, 0) = "" Then
                        Range([a1], [a1].End(xlDown)).Select
                        Range(Selection, Selection.End(xlToRight)).Copy
                        Workbooks(XLBook).Activate
                        ActiveSheet.Range("A1048576").Select
                            If Not Selection.End(xlUp).Value = "" Then
                            Selection.End(xlUp).Offset(1, 0).Select
                            ActiveSheet.Paste
                            Workbooks(w).Close
                            Application.CutCopyMode = False
                            Else
                            ActiveCell.End(xlUp).Select
                            ActiveSheet.Paste
                            Workbooks(w).Close
                            Application.CutCopyMode = False
                            End If
                    Else
                Workbooks(w).Close
                End If
        End If
        w = Dir
    Loop

Cependant la procédure prend malgré tout pas mal de temps (environ 20 secondes suivant le nombre de fichier qu'il importe) dnoc je me demandais si l'on ne pouvait pas optimiser cela. (éviter d'utiliser OpenText par exemple qui consomme pas mal à mon avis)

C'est véritablement concernant le troisième type de fichier que cela coince car quand j'utilise le code suivant il ne sépare pas les datas en "/" :

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
w = Dir(Path)
 
    Do While w <> ""
        If InStr(w, "trades" & EDate(i)) > 1 Then
                Workbooks.OpenText Filename:=Path & w, DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:="/"
                If Not [a1].Offset(1, 0) = "" Then
                Range([a1], [a1].End(xlDown)).Select
                Range(Selection, Selection.End(xlToRight)).Copy
                Workbooks(XLBook).Activate
                ActiveSheet.Paste
                Workbooks(w).Close
                Application.CutCopyMode = False
                Else
                Workbooks(w).Close
                End If
        End If
        w = Dir
    Loop

Je me retrouve donc avec 15 colonnes au lieu de 24, dont une qui regroupe des données séparées par des "/".
En bidouillant j'ai réussi à trouver une solution alternative mais ce n'est vraiment pas propre et cela alourdit fortement ma macro.

Auriez vous quelques pistes pour m'aider ?

Je peux bien évidemment revenir sur certains points si ce n'est pas clair, merci infiniment pour votre aide !

F.

**cafeine** · 21/01/2011, 15h44

Bonjour,

mon conseil serait de déclarer le répertoire de fichier comme une base de donnée avec l'objet DAO, chaque fichier devenant une table
en bouclant sur la collection des tables TableDefs, tu peux aisément ouvrir un recordset sur chaque et l'envoyer vers une feuille de calcul au moyen d'un .CopyFromRecordset
J'ai une petite inquiétude sur le 3eme type de fichier qui a 2 séparateurs ...
En revanche, modèle DAO me semble permettre d'obtenir de bonnes performances.

**Flo3301** · 21/01/2011, 19h11

Bonsoir,

Tout d'abord merci de votre réponse. Je ne connais pas les objects DAO mais je vais m'y pencher de près lundi surtout si cela accelère le traitement !

Si d'autre idées vous viennent n'hésitez pas à m'en faire part il faut vraiment que le process soit le plus light possible et je ne suis malheureusement pas un grand spécialiste pour faire des macros version light

Bon week end !

**neupont** · 24/01/2011, 15h52

Bonjour,
tu as la fonction Line Input (alliée à la fonction split) qui est très bien pour ça, pour les .txt comme pour les .csv, mais il faut s'y connaître un peu plus en programmation que pour Workbooks.OpenText, et je ne sais pas ce qu'il est pour toi.

En outre elle peut poser problème avec les fichiers issus d'Unix, à cause des retours ligne.

J'ai retrouvé un exemple d'utilisation ici :
http://www.developpez.net/forums/d73...s-fichier-csv/

**Flo3301** · 24/01/2011, 16h17

Bonjour,

Merci de vote intervention. Effectivement je connais l'existence de cette fonction qui permet de lire les fichiers txt et csv comme source extérieure mais je ne l'ai jamais vraiment utilisée. Je m'étais renseigné mais les tuto que j'ai trouvés ne m'ont pas parus trés clairs et détaillés comme je l'aurais voulu.
Mais même si comme vous le dites elle à l'air plus complexe que l'OpenText je vais quand même insister et me pencher davantage dessus (notamment sur le lien que vous m'avez donné) car elle me semble très utile. De plus elle permettra de faire tourner mon programme bcp plus rapidement je pense et c'est un critere déterminant pour cette macro.

Si j'éprouve de réelles difficulté je reviendrais poster ce qui me bloque.

Merci encore.

**el_slapper** · 25/01/2011, 11h24

On a parfois des problèmes avec les fichiers venant d'Unix. Dans ce cas, il arrive que les retours chariots soient erronés. On a eu le problème ce matin, et j'ai fait un petit code qui transforme ça :

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
40
41
42
43
44
45
46
47
Sub Reecriture()
    Dim NumFich As Integer, NumFich2 As Integer
    Dim Chemin As String
    Dim NomFich As String, NomFich2 As String
    Dim CaractereLu As String * 1, LongueurLue As Integer, PositionLue As Long
    Dim CaractereEcrit As String * 1, LongueurEcrite As Integer, PositionEcrite As Long
    Dim ValeurAscii As Integer
 
 
    Chemin = "c:\Travail\"
    NomFich = Chemin & "S03.txt"
    NomFich2 = Chemin & "S03corr.txt"
    LongueurLue = Len(CaractereLu)
    LongueurEcrite = Len(CaractereEcrit)
    NumFich = FreeFile
    Open NomFich For Random As #NumFich Len = LongueurLue
    NumFich2 = FreeFile
    Open NomFich2 For Random As #NumFich2 Len = LongueurEcrite
 
 
    PositionLue = 0
    PositionEcrite = 0
 
    Do While Not EOF(NumFich)
        PositionLue = PositionLue + 1
        Get #NumFich, PositionLue, CaractereLu
        ValeurAscii = Asc(CaractereLu)
        'les caractères normaux sont retranscrits sans changements
        If ValeurAscii <> 10 Then
            PositionEcrite = PositionEcrite + 1
            CaractereEcrit = CaractereLu
            Put #NumFich2, PositionEcrite, CaractereEcrit
        'les retours chariots passent de chr(10) à chr(13) & chr(10)
        Else
            PositionEcrite = PositionEcrite + 1
            CaractereEcrit = Chr(13)
            Put #NumFich2, PositionEcrite, CaractereEcrit
            PositionEcrite = PositionEcrite + 1
            CaractereEcrit = Chr(10)
            Put #NumFich2, PositionEcrite, CaractereEcrit
        End If
    Loop
 
    Close #NumFich
    Close #NumFich2
 
End Sub

La logique, c'est qu'UNIX ne met qu'un retour chariot CHR(10), là ou le line input de notre macro habituelle attend CHR(13) & CHR(10)

Derrièrre, le line input est codé(par ma collègue) comme suit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Dim NomFich As String
Dim NumFich As Integer
Dim chainelue as string
 
NomFich = "c:\Travail\S03corr.txt"
NumFich = freefile
Open NomFich For Input As #NumFich
(.../...)
While Not EOF(NumFich)
    Line Input #NumFich, chaineLue
    (..../traitements de la ligne "chainelue"/...)
Wend
(.../...)
close #NumFich

Après, il faut traiter la ligne lue en fonction du contenu. Ca, c'est spécifique à ton besoin.

**Flo3301** · 28/01/2011, 14h10

Bonjour à tous,

Je me suis donc replié sur la méthode des Input Lines qui marche à merveille !

Cependant, suivant la facon dont on la code cela met plus ou moins de temps... J'avais commencé par un import ligne par ligne avec split comme suit :

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
  w = Dir(Path)
  NumFile = FreeFile
  NumRow = 1
    Do While w <> ""
        If InStr(w, best_quote(k) & EDate(i)) > 1 Then
            Open Path & w For Input As #NumFile
            NumCol = 1
            l = -1
            While Not EOF(NumFile)
                Line Input #NumFile, Text
                Table = Split(Text, ";")
                For l = 0 To UBound(Table)
                Workbooks(XLBook).ActiveSheet.Cells(NumRow, NumCol + l) = Table(l)
                Next
                NumRow = NumRow + 1
            Wend
            Close #NumFile
        End If
        w = Dir
    Loop

... Mais cela met beaucoup trop de temps ! Alors je me suis replié sur un import ligne par ligne en supprimant le split, et en faisant appel à un TextToColumn sur la colonne A par la suite (que je n'ai pas inclu ds le code qui suit car il arrive plus tard dans ma procédure) :

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
  w = Dir(Path)
  NumFile = FreeFile
  NumRow = 1
    Do While w <> ""
        If InStr(w, best_quote(k) & EDate(i)) > 1 Then
            Open Path & w For Input As #NumFile
            While Not EOF(NumFile)
                Line Input #NumFile, Text
                Workbooks(XLBook).ActiveSheet.Cells(NumRow, 1) = Text
                NumRow = NumRow + 1
            Wend
            Close #NumFile
        End If
        w = Dir
    Loop

J'ai gagné pas mal de temps mais je pense que l'on peut encore faire mieux... En effet au lieu d'importer ligne par ligne peut on importer tout le contenu du fichier txt d'un seul coup (que je spliterai ensuite selon la méthode la plus rapide) ? Si oui avez vous une petite idée de la manière à procéder ? Je ne pense pas que cela soit trés difficile mais je ne sais pas trop comment m'y prendre :s

Merci d'avance pour vos conseils !

**kiki29** · 28/01/2011, 16h20

Salut, voir peut-être sur http://excel.developpez.com/faq/?page=ChapFichiersTxt
http://warin.developpez.com/access/fichiers/

**el_slapper** · 28/01/2011, 16h23

J'ai eu des problèmes de performances avec de très gros fichiers mis intégralement en mémoire - mais c'était du 40 mégas.

Est-ce que tu as mis Application.Screenupdating = false ? Si non, la mise à jour se fait sous tes yeux, et c'est beaucoup plus lent.

**neupont** · 31/01/2011, 13h03

j'ai modifié le tableau de variables pour gérer un split à 29 colonnes, mais je ne sais pas trop si on y gagnera.

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
40
41
42
43
44
45
46
Dim vTab() As Variant 'déclaration d'un tableau de variables dynamique
Dim e As Integer
Dim firstRow As Long, NumRow As Long

w = Dir(Path)
  NumFile = FreeFile
  NumRow = 1
firstRow = 1
    Do While w <> ""
        If InStr(w, best_quote(k) & EDate(i)) > 1 Then
            Open Path & w For Input As #NumFile
            NumCol = 1
            l = -1
            ReDim vTab(1 To 1000, 1 To 29) 'dimensionnement du tableau de variables à 1000 lignes et 29 colonnes + vidage s'il existe déjà
            e = 1          
  
            While Not EOF(NumFile)
                Line Input #NumFile, Text
                vtemp = Split(Text, ";")
                For c = 0 To UBound(vtemp)
                    vTab(e, c + 1) = vtemp(c)
                Next c
                              
                e = e + 1
                NumRow = NumRow + 1
                
                'toutes les 1000 lignes on copie le tableau sur la feuille excel
                If e = 1001 Then 
                    Workbooks(XLBook).ActiveSheet.Range("A" & firstRow & ":AC" & NumRow - 1).Value = vTab
                    ReDim vTab(1 To 1000, 1 To 29) 'le ReDim a entre autres pour effet de vider le tableau de variables, ce que l'on cherche ici pour ne pas conserver des données déjà traitées                    
                    e = 1
                    firstRow = NumRow
                End If[/COLOR]

            Wend
            Close #NumFile
            
            'on copie le reliquat qui n'a pas atteint 1000 lignes
            Workbooks(XLBook).ActiveSheet.Range("A" & firstRow & ":AC" & NumRow - 1).Value = vTab
            firstRow = NumRow           
        End If
        w = Dir
    Loop
    
    
End Sub

**Flo3301** · 31/01/2011, 15h59

Bonjour,

et tout d'abord merci à vous trois pour vos interventions respectives.

@Kiki29 : je ne connaissais qu'un seul des deux sites mais le second ne m'a pas vraiment aidé à résoudre mon problème. Néanmoins, il y a pas mal de lignes de codes trés interessantes, je vais donc le garder bien au chaud !

@el_slapper : Oui j'avais bien fais intervenir le screenupdating:=false mais même si on gagne un peu de temps ce n'est pas ce qui fait ramer mon process... :/

@Neupont : Déjà un merci particulier pour le temps que tu as passé sur mon code ! Surtout que celui que tu m'as fourni marche parfaitement et se révèle très utile. Mais je viens de me rendre compte que ce n'est pas tant l'import des données qui prend du temps mais plutôt le TextToColumn... Donc du coup il n'y a pas de grosse différence avec mon import ligne par ligne d'avant...

A part le Split en amont, ou le TexttoColumn en aval je ne vois pas vraiment d'autres alternatives donc je suis un peu bloqué là. Je vais quand même tester de faire Split avec ton code qui permet l'import par plage de 1000 lignes pour voir ce que ca donne car le split ligne par ligne c'était vraiment l'horreur.

Je vous tiens au courant de ce que ca donne, merci encore !

**neupont** · 31/01/2011, 17h36

Oups mince j'ai édité mon post précédent au lieu d'en poster un nouveau.

**Philippe Tulliez** · 31/01/2011, 23h57

Bonsoir,
As-tu essayé avec l'ouverture du fichier en binaire ?
Je n'ai malheureusement pas de fichiers d'une grosse importance pour tester mais je me souviens d'avoir fait un travail de récupération de fichiers txt il y a 10 ans et cette méthode était pour l'époque la plus rapide.
Donc prendre ma réponse avec précaution.
Ici mon test est fait sur un fichier de petite taille (542 kb) qui donne en sortie sur le classeur Excel 2667 lignes et 32 colonnes. Temps 1 seconde.
Diminuer le Buffer si la taille du fichier ne peut pas tenir en mémoire

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
 
  Dim Repertoire As String
  Dim Channel As Integer
  Dim sBuffer As String
  Dim r As Long
  Dim Line() As String, Colonne() As String
  Debug.Print "Début " & Time
  Repertoire = "Z:\Développement\Tests\rw Texte Binary"
  Channel = FreeFile
  Open Repertoire & "\CPTMVT01.txt" For Binary Access Read As #Channel
  sBuffer = Space(LOF(Channel))
  Get #Channel, , sBuffer
  ' Line = Split(sBuffer, vbCrLf)
  Line = Split(Replace(sBuffer, Chr(34), ""), vbCrLf) ' Enlève les Guillemets
  For r = 0 To UBound(Line) - 1
    Colonne = Split(Line(r), ";")
    Range(Cells(r + 1, 1), Cells(r + 1, UBound(Colonne) + 1)) = Colonne()
  Next r
  Close #Channel
  Debug.Print "Fin  " & Time

**Flo3301** · 03/02/2011, 09h57

Bonjour à tous, et désolé pour le retard !

@Neupont j'ai testé ton EDIT et la encore ca marche parfaitement, et ca me fait gagner encore un peu de temps ! Je pense donc l'intégrer à mon processus en rajoutant certaines composantes dans la boucle car ma demande initiale à un peu évoluée.

En tout cas merci ton code il marche vraiment bien et il est super propre !

@Corona : Merci pour ton code la encore, je vais tester ca dans la journée et comparer avec ce que j'obtenais avec Neupont. Prk pas une combinaison des deux aussi à la limite ^^

Quoi qu'il en soit je considere mon probleme comme résolu, toutes vos explications m'ont vraiment aidées, Merci infiniment !

Importer correctement des fichiers txt et csv [XL-2007]

Macros et VBA Excel

Discussions similaires

Partager

Partager