[VBA Excel] Optimisation Import d'un énorme fichier txt

Optimisation Import d'un énorme fichier txt [VBA Excel] - VB/VBA/VBS - Programmation

Marsh Posté le 20-01-2005 à 10:59:20    

Bonjour tout le monde,
 
Voilà j'ai un problème d'importation d'un gros fichier texte de plus de 65000 lignes dans Excel (2000 sous windows 2000).
 
Je m'explique : j'ai une liste générée (par SAP) au format texte, jusqu'à maintenant j'importais ça sous Excel avec une QueryTable. Cout de l'opération : une dizaine de secondes pour importer à peu près 50.000 lignes.
Le problème est que cette liste grandit avec le temps... et va bientôt dépasser les fameuses 65.536 lignes max.
N'ayant pas trouvé comment dire à Excel d'importer les données dans plusieurs onglets si ça dépasse, je me suis résolu à écrire une macro d'importation qui lit le fichier texte et 'range' les lignes dans différents onglets d'après un critère.
 
Je suis dans la première phase... et j'ai déjà un problème de perf.
Je mets 11 secondes à lire un fichier de 52.000 lignes et 150 secondes pour le lire et écrire les lignes dans un classeur.  :sweat:  
La fonction native d'Excel mets 12 secondes pour le tout!
 
Quelqu'un a une idée pour booster tout ça?
 
Le fichier en question est un fichier texte dont les champs sont de longueur fixe, longueur d'une ligne = 198 caractères, séparateur "|".
Seuls quelques champs (non contigus) sont utiles.
Voici une partie du code utilisé :  

Code :
  1. Public Type Contrat
  2.     Num_Cli As String * 10
  3.     Nom_Cli As String * 40
  4.     Statut As String * 5
  5.     Num_PDL As String * 14
  6. End Type
  7. Public Sub ImporterListeContrats(cheminFic as String)
  8. Dim FSO As Scripting.FileSystemObject, txtStr As Scripting.TextStream
  9. Dim strFic As String, ctr As Contrat, i As Long, nb As Single
  10. Dim rgDest As Range
  11.     QuickMode (True) 'paramétrage d'Excel pour aller plus vite
  12.     On Error GoTo gestion_err
  13.     Set FSO = CreateObject("Scripting.FileSystemObject" )
  14.     Set rgDest = ThisWorkbook.Sheets("Test" ).Range("A2" )
  15.     Set txtStr = FSO.OpenTextFile(cheminFic, ForReading)
  16.     For i = 1 To 10 'Les 10 premières lignes d'en-tête
  17.         txtStr.SkipLine
  18.     Next i
  19.     'Pour mesurer le temps d'exécution
  20.     Dim dDeb As Date
  21.     dDeb = Now
  22.     nb = 0
  23.     Do Until txtStr.AtEndOfStream
  24.         If txtStr.Read(1) = "-" Then
  25.             txtStr.ReadLine     'Lignes de présentation
  26.         Else
  27.             txtStr.Skip (39) 'Informations inutiles
  28.             ctr.Num_Cli = txtStr.Read(11)
  29.             ctr.Nom_Cli = txtStr.Read(41)
  30.             txtStr.Skip (22) 'Informations inutiles
  31.             ctr.Statut = txtStr.Read(5)
  32.             txtStr.Skip (64) 'Informations inutiles
  33.             ctr.Num_PDL = txtStr.Read(14)
  34.             txtStr.ReadLine 'Au suivant!
  35.             nb = nb + 1  'Une ligne de plus!
  36.     'Ecriture ligne par ligne des informations...
  37.     'avec rgDest.Value = blabla : il y a peut être mieux
  38.             EcireLigne ctr.Num_Cli, ctr.Nom_Cli, ctr.Statut, ctr.Num_PDL, rgDest
  39.         End If
  40.     Loop
  41.     'Pour mesurer le temps d'exécution
  42.     MsgBox ("Opération exécutée en " & DateDiff("s", dDeb, Now) & " s." & vbCrLf & _
  43.             nb & " lignes lues" )
  44.     QuickMode (False)
  45.     txtStr.Close
  46.     Set FSO = Nothing
  47.     Set txtStr = Nothing
  48. End Sub


 
J'ai essayé de remplacer les lignes 30 à 37 par un

Code :
  1. strBuff = txtStr.readLine


Mais je n'ai pas gagné une seule seconde!
 
Si quelqu'un a déjà été confronté à un problème de ce genre, je suis preneur de tout conseil utile pour optimiser ce code  :hello:  
 
Merci d'avance.  :jap:


Message édité par jeanValjean34 le 20-01-2005 à 13:39:26

---------------
"Engagez-vous... Rengagez-vous" qu'y disaient!
Reply

Marsh Posté le 20-01-2005 à 10:59:20   

Reply

Marsh Posté le 20-01-2005 à 17:10:55    

Désolé de faire un UP mais les personnes qui lise ce post  pourraient avoir l'amabilité de me signaler au moins pourquoi elle ne réponde pas?
Est-ce que ce n'est pas assez clair?
Est-ce qu'il n'existe pas de moyens d'aller plus vite que ça?
Est-ce trivial?
Est-ce trop compliqué?
Est-ce que ce n'est pas le bon forum pour poser cette question?
 
Merci d'avance.


---------------
"Engagez-vous... Rengagez-vous" qu'y disaient!
Reply

Marsh Posté le 20-01-2005 à 20:57:09    

Salut,
 
Je viens de lire ton post. J'ai eu ce problème de fichier trop long à importer et je l'ai résolu autrement (une information est donnée à l'utilisation pour qu'il réalise une nouvelle extraction de données).
Ton post est clair, pas de problème de ce côté là.
Il est normal que la fonction native d'Excel aille plus vite. Je ne connais pas suffisament le VBA pour savoir si ton code peut être optimiser...
Laisses-tu les commentaires dans la macro que tu exécutes ? Si c'est le cas, essaies avec une copie sans commentaires. Mais je pense que cela ne te ferai pas gagner grand chose...


---------------
Proverbe chinois: il vaut mieux apprendre à pêcher à un mendiant que de lui donner du poisson...
Reply

Marsh Posté le 21-01-2005 à 15:18:56    

Salut,  
 
En fait je rencontre plusieurs problèmes :
1 - lorsque j'importe un fichier contenant plus de 65536 lignes, Excel mets ~65230 lignes (même pas 65536) dans une feuille sans autre forme de procès et sans m'averir que les données ont été tronquées.
2 - Comme je l'ai dit, le fichier va être de plus en plus grand ... et doit dépasser les 200.000 lignes.
Je me demande le temps que va prendre l'importation dans ce cas.
 
En fait je me satisfait plus ou moins du temps de lecture (12 s pour 52.000 lignes).
Ce que je ne comprends pas c'est le temps d'écriture dans la feuille qui raméne le temps total à 150 s, alors que je prends soin de désactiver le rafraichissement automatique de l'affichage et le mode de recalcul automatique.
 
Je pense (rapidement) que le problème peut venir de 2 sources.
1 - J'écris directement dans un classeur situé dans un dossier partagé sur un serveur d'un réseau local (100M/s théoriques).  
2 - La méthode d'importation implique la lecture d'une ligne du fichier texte en mémoire puis l'écriture directement dans une feuille au moyen d'une affectation de la valeur Range.Value.
 
Y a t il un autre moyen d'écrire plus vite?
En créant un objet de connection par exemple ou en utilisant un autre technique obscure?
Ou en stockant plusieurs lignes en mémoire et en les écrivant d'un coup?
 
J'imagine dans un moment de folie que la fonction native est plus rapide car compilée. Y-a-t-il moyen d'appeler une fonction d'une DLL pour l'écriture dans un classeur??
 
Bref, ça fait pas mal de question tout ça!
De toutes façons, Excel n'est pas fait pour gérer de telles quantités d'information mais nous n'avons pas Access (qui n'est déjà pas terrible) sur nos postes.


---------------
"Engagez-vous... Rengagez-vous" qu'y disaient!
Reply

Marsh Posté le 21-01-2005 à 15:50:53    

Il est vrai qu'écrire ligne par ligne via un réseau n'accélère pas les choses.
Essaies avec une insertion de dix lignes à la fois. J'ai fait un petit bout de code mais je ne l'ai pas testé:

Code :
  1. Do Until txtStr.AtEndOfStream
  2.    i=0
  3.    do until i>10
  4.       If txtStr.Read(1) = "-" Then
  5.             txtStr.ReadLine     'Lignes de présentation
  6.       Else
  7.             txtStr.Skip (39)    'Informations inutiles
  8.             ctr.Num_Cli(i) = txtStr.Read(11)
  9.             ctr.Nom_Cli(i) = txtStr.Read(41)
  10.             txtStr.Skip (22)    'Informations inutiles
  11.             ctr.Statut(i) = txtStr.Read(5)
  12.             txtStr.Skip (64)    'Informations inutiles
  13.             ctr.Num_PDL&i = txtStr.Read(14)
  14.             txtStr.ReadLine    'Au suivant!
  15.             nb = nb + 1        'Une ligne de plus!
  16.       End If
  17.       i=i+1
  18.    Loop
  19.    i=0
  20.    do until i>10
  21.       'Ecriture ligne par ligne des informations... 
  22.       'avec rgDest.Value = blabla : il y a peut être mieux   
  23.       EcireLigne ctr.Num_Cli(i), ctr.Nom_Cli(i), ctr.Statut(i), ctr.Num_PDL(i), rgDest
  24.       i=i+1
  25.    Loop
  26. Loop


---------------
Proverbe chinois: il vaut mieux apprendre à pêcher à un mendiant que de lui donner du poisson...
Reply

Marsh Posté le 21-01-2005 à 17:13:41    

Merci du tuyau,
 
Je teste cela lundi et je posterai les résultats.
 
Bon week end.


---------------
"Engagez-vous... Rengagez-vous" qu'y disaient!
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed