Discussion :

[Patrice740]

Bonjour le Forum,

J'utilise Excel pour vérifier et corriger mes fichiers de généalogie à la norme GEDCOM (.ged)
Un fichier Gedcom est un simple fichier texte monocolonne au format UTF8 avec BOM.
Jusqu'à présent, pour ouvrir et enregistrer le fichier, j'utilisais ADODB.Steam qui fonctionne très bien.

Avec le temps mon fichier a pris de l'ampleur (150 000 lignes) et cette méthode s'avère lente, 1 minute pour ouvrir et 4 à 5 minutes pour enregistrer.
Pour ouvrir, OpenText est bien plus rapide et fonctionne parfaitement,
je cherche une méthode pour enregistrer

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
48
 
Sub Gedcom()
 
Dim wbkGed As Excel.Workbook
  ' Ouverture d'un fichier Gedcom
  Application.Workbooks.OpenText _
      Filename:=nomCompletFichier, _
      Origin:=65001, _
      DataType:=xlDelimited, _
      TextQualifier:=xlNone, _
      FieldInfo:=Array(1, 2)
  Set wbkGed = Application.ActiveWorkbook
 
  ' Commandes d'enregistrements qui ne conviennent pas
  wbkGed.Save
  ' et
  wbkGed.SaveAs _
      Filename:=ged.FullName, _
      FileFormat:=xlCSVUTF8
  ' et
  wbkGed.SaveAs _
      Filename:=ged.FullName, _
      FileFormat:=xlCSVUTF8, _
      Local:=True
 
  ' Enregistrement fonctionnel (mais lent)
Dim fUtf8avecBOM As ADODB.Stream
Dim g As Variant
Dim txt As String
Dim n°L As Long
 
  Set fUtf8avecBOM = New Stream
  fUtf8avecBOM.Charset = "utf-8"
  fUtf8avecBOM.Mode = adModeReadWrite
  fUtf8avecBOM.Type = adTypeText
  fUtf8avecBOM.Open
  g = wbkGed.Worksheets(1).Range("A1").CurrentRegion.Columns(1).Value
  txt = ""
  For n°L = 1 To UBound(g)
    txt = txt & g(n°L, 1) & vbCrLf
  Next n°L
  fUtf8avecBOM.WriteText txt
  fUtf8avecBOM.Flush
  fUtf8avecBOM.SaveToFile nomCompletFichier, adSaveCreateOverWrite
  fUtf8avecBOM.Close
  Set fUtf8avecBOM = Nothing
 
End Sub

Ce qui génére la lenteur c'est la boucle For Next, mais avec Join() ce n'est pas plus rapide.

Que ce soit avec Save ou SaveAs, Excel ajoute des doubles côtes sur certaines lignes de texte, celles qui contiennent des , ou des " en anglais ou des ; en local (ce qui logique pour du CSV multicolonne).
C'est incompatible avec la norme GEDCOM.

Si quelqu'un à une solution ....

Cordialement

Patrice

[tototiti2008]

Bonjour,

Peut-être quelques pistes par là :
https://excel.developpez.com/telecha...-fichier-texte

Il y a notamment dedans un code de patricktoulon qui permet d'enregistrer en UTF-8 avec Open, voir si ça peut améliorer les choses

[Nain porte koi]

Hello,

si c'est un "simple fichier texte" pourquoi le gérer avec Excel ?
Perso, les fichiers textes (et beaucoup d'autres d'ailleurs) je les manipules avec NotePad++
https://notepad-plus-plus.org/downloads/
(c'est gratuit et je ne cherche pas à faire de la pub, juste que c'est un programme génial et parfaitement maintenu par son concepteur)

[jurassic pork]

si c'est un "simple fichier texte" pourquoi le gérer avec Excel ?
Perso, les fichiers textes (et beaucoup d'autres d'ailleurs) je les manipules avec NotePad++
(c'est gratuit et je ne cherche pas à faire de la pub, juste que c'est un programme génial et parfaitement maintenu par son concepteur)

Hello,
A noter qu'il existe un plugin Notepad++ qui permet de manipuler des fichiers dans Notepad++ par un programme extérieur comme EXCEL VBA par un activeX. Il faut avoir une version de Notepad++ qui correspond à celle du Excel ( 32 bits ou 64 bits).
Et concernant le problème de lenteur, ça ne serait pas plus rapide d'écrire dans le fichier ligne par ligne que de concaténer une seule chaîne à chaque boucle ?
voici ce qu'en pense l'I.A :

Concaténer une chaîne de caractères dans une boucle VBA peut devenir très lent lorsque le nombre d’itérations est élevé. Cela est particulièrement problématique si vous utilisez ensuite ADODB.Stream pour écrire les données dans un fichier — la lenteur de la concaténation peut rendre l'opération inefficace.

---

🇫🇷 Explication : Pourquoi c’est lent ?

En VBA, la concaténation avec str = str & newText dans une boucle :

• alloue de la mémoire à chaque itération ;
• copie à chaque fois toute la chaîne existante + la nouvelle portion ;
• devient exponentiellement plus lent avec la taille croissante de la chaîne.

---

✅ Solutions pour optimiser

1. Utiliser un tableau temporaire (Array) + Join

La méthode la plus rapide pour concaténer des chaînes en VBA est :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Dim buffer() As String
Dim i As Long
Dim ligne As String
 
ReDim buffer(1 To 10000) ' ou le nombre estimé de lignes
 
For i = 1 To 10000
    buffer(i) = "Ligne numéro " & i
Next i
 
Dim resultat As String
resultat = Join(buffer, vbCrLf) ' ou autre séparateur

Cette approche est 100× plus rapide que de faire :

Code :

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

1
2
3
For i = 1 To 10000
    resultat = resultat & "Ligne " & i & vbCrLf
Next i

---

✅ Bonus : Fonction complète en VBA (concat rapide + export fichier)

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
Sub ExportRapideVersFichier()
    Dim buffer() As String
    Dim i As Long
    Dim nbLignes As Long
    nbLignes = 10000
 
    ReDim buffer(1 To nbLignes)
 
    For i = 1 To nbLignes
        buffer(i) = "Ligne " & i
    Next i
 
    Dim texteFinal As String
    texteFinal = Join(buffer, vbCrLf)
 
    ' Écriture dans le fichier avec ADODB.Stream
    Dim stream As Object
    Set stream = CreateObject("ADODB.Stream")
 
    With stream
        .Charset = "utf-8"
        .Open
        .WriteText texteFinal
        .SaveToFile "C:\temp\export.txt", 2
        .Close
    End With
 
    MsgBox "Export terminé !"
End Sub

---

🚀 Résultat :

• Concaténation 10× à 100× plus rapide
• Fichier proprement généré avec ADODB.Stream
• Compatible avec les caractères UTF-8 (très utile en français)

[EDIT] y a même plus simple sans passer par un buffer intermédiaire et sans boucle :

Code :

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

1
2
3
Dim g, texteFinal As String
g = Application.WorksheetFunction.Transpose(Worksheets(1).Range("A1").CurrentRegion.Columns(1).Value)
texteFinal = Join(g, vbCrLf)

Ami calmant, J.P

[Nain porte koi]

A noter qu'il existe un plugin Notepad++ qui permet de manipuler des fichiers dans Notepad++ par un programme extérieur comme EXCEL VBA par un activeX.

Info intéressante, merci

[Patrice740]

Bonjour,

et merci à tous pour vos retours.

@J.P.
J'utilise beaucoup les tableaux de valeurs mais je n'avais jamais essayé avec un tableau de chaines (buffer).
Avec le buffer c'est dèjà très bien, dans mon cas c'est 700 fois plus rapide.
Avec Transpose et Join, le gain de temps est impressionant, c'est plus de 2000 fois plus rapide !
C'est même beaucoup plus rapide pour enregistrer que pour lire le fichier !
[EDIT 2]
Attention : la capacité de la fonction Transpose est limitée, avec un gros fichier, elle tronque le résultat !
J'ai donc utilisé la boucle pour remplir le buffer
[/EDIT 2]

Hello,
[EDIT] y a même plus simple sans passer par un buffer intermédiaire et sans boucle :

Code :

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

1
2
3
Dim g, texteFinal As String
g = Application.WorksheetFunction.Transpose(Worksheets(1).Range("A1").CurrentRegion.Columns(1).Value)
texteFinal = Join(g, vbCrLf)

A noter qu'il existe un plugin Notepad++ qui permet de manipuler des fichiers dans Notepad++ par un programme extérieur comme EXCEL VBA par un activeX.

Quand à NotePad,++, c'est bon à savoir, mais je ne vois pas d'avantage à manipuler les données dans NotePad, autant le faire directement dans Excel.

@Nain porte koi

Hello,
si c'est un "simple fichier texte" pourquoi le gérer avec Excel ?
Perso, les fichiers textes (et beaucoup d'autres d'ailleurs) je les manipules avec NotePad++

Dans mon cas, NotePad++ n'est pas adapté, ce n'est pas parce que c'est un simple fichier texte que les manipulations ne sont pas complexes et répétitives !
Pour cela les macros Excel font merveille !

@tototiti2008

Bonjour,
Peut-être quelques pistes par là :
https://excel.developpez.com/telecha...-fichier-texte
Il y a notamment dedans un code de patricktoulon qui permet d'enregistrer en UTF-8 avec Open, voir si ça peut améliorer les choses

J'ai jeté un coup d'oeil rapide au fichier de Patrick, je n'ai pas essyé son option 4 (UTF8), mais vu qu'il encode un par un les caractères en UTF8 je doute fort que la rapidité soit là.

Encore merci à tous

[EDIT][EDIT 2]
Ma procédure finale :

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
Private Sub Enregistrer_ged_UTF8_avec_BOM(wsh As Worksheet, ByVal nomCompletFichier As String)
' Enregistrement d'une feuille Excel au format ged encodé UTF8 avec BOM
' Établir la référence à ADODB :  Microsoft ActiveX Data Objects 6.1 Library
'
Dim fUtf8avecBOM As ADODB.stream
Dim g As Variant
Dim buffer() As String
Dim txt As String
Dim n°L As Long
 
  With wsh
    g = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
  End With
  ReDim buffer(1 To UBound(g))
  For n°L = 1 To UBound(g)
    buffer(n°L) = g(n°L, 1)
  Next n°L
  txt = Join(buffer, vbCrLf) & vbCrLf
  Set fUtf8avecBOM = New stream
  With fUtf8avecBOM
    .Charset = "utf-8"
    .Mode = adModeReadWrite
    .Type = adTypeText
    .Open
    .WriteText txt
    .Flush
    .SaveToFile nomCompletFichier, adSaveCreateOverWrite
    .Close
  End With
  Set fUtf8avecBOM = Nothing
 
End Sub

Note : End(xlUp] plutôt que CurrentRegion pour prendre en compte les éventuelles lignes vides

[Nain porte koi]

Dans mon cas, NotePad++ n'est pas adapté, ce n'est pas parce que c'est un simple fichier texte que les manipulations ne sont pas complexes et répétitives !
Pour cela les macros Excel font merveille !

Ah ok, je ne savais pas qu'il y avait des macros derrière.
Juste pour info : NotePad++ a des macros https://nliautaud.developpez.com/tut...atique/macros/

[tototiti2008]

Bonjour,

Content que tu ai trouvé ta solution

je n'ai pas essyé son option 4 (UTF8), mais vu qu'il encode un par un les caractères en UTF8 je doute fort que la rapidité soit là

ça c'est supposer que les autres solutions ne font pas pareil, au bout convertir en UTF devra bien se faire d'une manière ou l'autre, mais peut-être as-tu raison
sans tester, le mystère demeure

Edit : j'ai testé, en effet ça a l'air lent sur les gros fichiers

[Patrice740]

Bonjour,

Content que tu ai trouvé ta solution

ça c'est supposer que les autres solutions ne font pas pareil, au bout convertir en UTF devra bien se faire d'une manière ou l'autre, mais peut-être as-tu raison
sans tester, le mystère demeure

Edit : j'ai testé, en effet ça a l'air lent sur les gros fichiers

Pour le fun, j'ai aussi testé : c'est 1300 fois plus lent qu'avec la méthode à JP et la macro que j'ai publié précédemment,
presque deux fois plus lent que mon code initial que je trouvais trop lent.