[VBA-E] Ecrire un fichier texte le plus rapidement possible

Version imprimable

Bonsoir,

j'ai de grandes quantités de données à écrire dans un fichier texte: sa taille peut atteindre 100Mo. Aussi je cherche le moyen le plus rapide de l'écrire.

Outre les méthodes classiques qui sont présentées ici, j'ai pensé à écrire mon fichier dans un classeur et à l'enregistrer au format txt.
Pour l'écriture dans le classeur je pensais écrire par plage pour éviter le travail cellule par cellule:

Code:

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
48
49
50
51
 
Option Explicit
Sub test()
    Dim v As Variant
    Dim Plage As Range
    Dim C As Workbook
    Dim F As Worksheet
    Dim iL As Long
 
    'On définit un variant qui ne consomme pas trop de mémoire
    ReDim v(5, 5)
 
    Set C = Workbooks.Add
    Set F = C.Worksheets(1)
    Application.ReferenceStyle = xlR1C1
    iL = 1
 
    v(0, 0) = "Texte11"
    v(1, 0) = "Texte21"
    v(2, 0) = "Texte31"
    v(3, 0) = "Texte41"
    v(4, 0) = "Texte51"
    v(5, 0) = "Texte61"
    v(0, 1) = "Texte12"
    v(1, 1) = "Texte22"
    '...
    '...
    v(5, 5) = "Texte55"
 
    Set Plage = F.Range(F.Cells(iL, 1), F.Cells(iL + 5, 6))
    Plage.FormulaArray = v
 
    'on passe à la plage suivante
    v(0, 0) = "Texte11"
    v(1, 0) = "Texte21"
    v(2, 0) = "Texte31"
    v(3, 0) = "Texte41"
    v(4, 0) = "Texte51"
    v(5, 0) = "Texte61"
    v(0, 1) = "Texte12"
    v(1, 1) = "Texte22"
    '...
    '...
    v(5, 5) = "Texte55"
    iL = iL + 6
    Set Plage = F.Range(F.Cells(iL, 1), F.Cells(iL + 5, 6))
    Plage.FormulaArray = v
 
    'on sauvegarde au format txt
    C.SaveAs "C:\test.txt", FileFormat:=xlUnicodeText
End Sub

Je voudrais savoir si vous avez déjà comparé ces différentes méthodes avec un code du style

Code:

1
2
3
4
 
depart = GetTickcount
'code à tester
debug.print format(GetTickcount-depart)

Merci.

Je pense que, de très loin, les méthodes "classiques" sont les plus rapides.

Par exemple le petit test ci-dessous réalise dix millions de boucles et écrit 150 Mo en 21 secondes (chez moi) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Sub TestVitesse() Dim vStart, vEnd As Variant Dim vFic1 As String Dim i As Single vFic1 = "D:\test.txt" vStart = Timer Open vFic1 For Output As #1 For i = 1 To 10000000 Print #1, "Valeur " & i Next i Close #1 vEnd = Timer MsgBox "Le test a duré " & vEnd - vStart & " secondes", vbOKOnly, "Durée" End Sub
Ce n'est pas nécessairement proportionnel à la taille du fichier d'ailleurs, en ralongeant un peu la phrase à écrire j'obtiens 350 Mo en 25 secondes.
Donc je dirais que ça va surtout dépendre de l'efficacité avec laquelle tu vas remplir tes variables !

Et puis si tu dois écrire pour 100 Mo de données, je ne sais pas si les 65.000 lignes d'une feuille Excel y suffiront.

Ceci dit, si tu dois vraiment écrire dans la feuille excel, j'ai trouvé je ne sais plus où ce petit code qui accélère considérablement l'écriture dans les cellules en désactivant certaines options d'Excel :
Code:

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 Function FastRun(Setting As Boolean) 'accélérer le code en désactivant en début de procédure puis 'réactivant en fin de procédure les options d'Excel qui 'ralentissent l'exécution. 'Ex : ' Sub MaProc() ' FastRun False ' 'code ' FastRun True ' End Sub 'Peter Fossey, mpep 'Application.StatusBar = "Updating Excel settings, please wait..." Application.EnableCancelKey = xlDisabled Application.ScreenUpdating = Setting Application.DisplayAlerts = Setting Application.Interactive = Setting If Setting = False Then Application.Calculation = xlCalculationManual If Setting = False Then Application.Cursor = xlWait If Setting = True Then Application.Calculation = xlCalculationAutomatic If Setting = True Then Application.Cursor = xlDefault Application.StatusBar = False End Function
Mais encore une fois, je pense qu'écrire dans Excel puis faire une sauvegarde au format texte doit être nettement plus lent.

Bonsoir,

les lignes que j'écris dans mon fichier sont souvent différentes les unes des autres donc la solution de loin la plus pratique c'est l'accès séquentiel. Ci dessous un extrait du genre de fichier que je dois écrire:

Code:

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
 
    -1                                                                     
  2411                                                                     
         1         1         1        11                                   
   -9.470960725683650D+00   -1.126723351112040D+00    0.000000000000000D+00
         2         1         1        11                                   
   -9.358211193235030D+00   -1.126723351112040D+00    0.000000000000000D+00
         3         1         1        11                                   
   -9.245461660786420D+00   -1.126723351112040D+00    0.000000000000000D+00
 
    9.470960725683650D+00    1.126723351112040D+00    1.089165687561030D+00
    -1
    -1
  2412
         1       115         1         1         1         8           
         1         2       171       170      3550      3551      3720      3719           
         2       115         1         1         1         8           
         2         3       172       171      3551      3552      3721      3720           
         3       115         1         1         1         8           
         3         4       173       172      3552      3553      3722      3721           
         4       115         1         1         1         8           
         4         5       174       173      3553      3554      3723      3722           
         5       115         1         1         1         8           
         5         6       175       174      3554      3555      3724      3723                
    -1

J'ai déclaré une variable ligne que je viens modifier localement avec l'instruction mid. Ca marche pas trop mal mais je trouve que ça va pas très vite

Code:

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
    Open "C:\" & Nom & "2.unv" For Output As #NF
 
    'Noeuds
 
    Ligne = String(75, " ")
    NB = Format(-1)
    Mid(Ligne, 6 - Len(NB) + 1, Len(NB)) = NB
    Debug.Print Ligne
    Print #NF, Ligne
    NB = Format(2411)
    Mid(Ligne, 6 - Len(NB) + 1, Len(NB)) = NB
    Debug.Print Ligne
    Print #NF, Ligne
    i = 1
    j = 1
    o = 1
    n = 1
 
    Do
        Do
            Do
                Ligne = String(75, " ")
                'Numéro de label
                'NB = Format(i, "000") & Format(j, "000") & Format(o, "000")
                NB = Format(n)
                Mid(Ligne, 11 - Len(NB), Len(NB)) = NB
                'système de coordonnées principal
                NB = Format(1)
                Mid(Ligne, 21 - Len(NB), Len(NB)) = NB
                'système de coordonnées des déplacements
                NB = Format(1)
                Mid(Ligne, 31 - Len(NB), Len(NB)) = NB
                'couleur des noeuds
                NB = Format(11)
                Mid(Ligne, 41 - Len(NB), Len(NB)) = NB
                Debug.Print Ligne
                Print #NF, Ligne
 
                CX = String(25, " ")
                NB = F.Range(x).Cells(i, 1).Text
                Mid(CX, 26 - Len(NB), Len(NB)) = NB
                Mid(CX, 22, 1) = "D"
                Mid(CX, 6, 1) = "."
 
                CY = String(25, " ")
                NB = F.Range(y).Cells(1, j).Text
                Mid(CY, 26 - Len(NB), Len(NB)) = NB
                Mid(CY, 22, 1) = "D"
                Mid(CY, 6, 1) = "."
 
                CZ = String(25, " ")
                NB = F.Range(z).Cells(o, 1).Text
                Mid(CZ, 26 - Len(NB), Len(NB)) = NB
                Mid(CZ, 22, 1) = "D"
                Mid(CZ, 6, 1) = "."
 
                Ligne = String(75, " ")
                Mid(Ligne, 1, 25) = CX
                Mid(Ligne, 26, 25) = CY
                Mid(Ligne, 51, 25) = CZ
                Debug.Print Ligne
                Print #NF, Ligne
                n = n + 1
                i = i + 1
            Loop While i <= NbPX + 1
            i = 1
            j = j + 1
        Loop While j <= NbPY + 1
        i = 1
        j = 1
        o = o + 1
    Loop While o <= iL - 2
    Ligne = String(6, " ")
    NB = Format(-1)
    Mid(Ligne, 6 - Len(NB) + 1, Len(NB)) = NB
    Print #NF, Ligne

Voilà si quelqu'un a des idées pour accélérer tout ça...

Je vais tester ton code en accès random en accès séquentiel sur ma machine pour voir.

A+ et merci.

Résultats pour ce code:

Code:

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
48
49
50
51
52
53
54
 
Declare Function GetTickCount Lib "kernel32" () As Long
Option Explicit
Sub TestVitesse()
 
    Dim Depart As Long
    Dim vFic1 As String
    Dim i As Long
    Dim t As String
    Dim l As Integer
    Dim Ligne As String * 50
    Dim vFic2 As String
 
    Ligne = Empty
 
    vFic1 = "C:\test1.txt"
 
    Depart = GetTickCount
    Open vFic1 For Output As #1 Len = 50
        For i = 1 To 10000
            t = "Valeur " & Format(i)
            l = Len(t)
            Mid(Ligne, 51 - l, l) = t
            Print #1, Ligne
            Ligne = Empty
        Next i
        t = "Le test a duré " & Format(GetTickCount - Depart) & " ms"
        l = Len(t)
        Mid(Ligne, 51 - l, l) = t
        Print #1, Ligne
        Ligne = Empty
        Print #1,
    Close #1
 
 
 
 
 
    vFic2 = "C:\test2.txt"
 
    Depart = GetTickCount
    Open vFic2 For Random As #2 Len = 50
        For i = 1 To 10000
            t = "Valeur " & Format(i) & Chr(10)
            l = Len(t)
            Mid(Ligne, 51 - l, l) = t
            Put #2, i, Ligne
            Ligne = Empty
        Next i
        t = "Le test a duré " & Format(GetTickCount - Depart) & " ms"
        l = Len(t)
        Mid(Ligne, 51 - l, l) = t
        Put #2, i, Ligne
    Close #2

Accès séquentiel: 93ms, taille du fichier:520 054 octets
Accès direct (random): 79ms, tallie du fichier:500 050 octets
(ouvrir les fichiers avec wordpad plutôt qu'avec le bloc notes)

Résultats pour ce code ( le copier à la suite du précédent)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
    Ligne = Empty
 
    Dim c As Workbook
    Dim f As Worksheet
    Dim tabl(1 To 10000, 1 To 1) As Variant
 
    Set c = Workbooks.Add
    Set f = c.Worksheets(1)
 
    Depart = GetTickCount
    For i = 1 To 10000
        t = "Valeur " & Format(i)
        l = Len(t)
        Mid(Ligne, 51 - l, l) = t
        tabl(i, 1) = CStr(Ligne)
        Ligne = Empty
    Next i
    f.Range(f.Cells(1, 1), f.Cells(i - 1, 1)).Value = tabl
    f.SaveAs "C:\test3.txt", FileFormat:=xlTextWindows
    MsgBox GetTickCount - Depart

266 ms, taille du fichier: 520 000 octets

La meilleure solution en temps d'exécution semble bien être l'accès direct.

A+

13/09/2007, 20h51
spileo

Bonsoir,

toujours dans le but d'accélérer l'écriture de fichier texte, je me demandais s'il ne serait pas possible de placer les informations à écrire dans le fichier dans le presse papier puis faire un copier/coler du presse papier dans le fichier et enfin de le sauvegarder.

Bonne soirée.