Accélérer import fichier texte

[Mikaëlkaël]

Bonjour a tous,

Tout d'abord Bravo pour toutes les réponses et la base de connaissances que l'on trouve sur ce site. Jusqu'à il y a 2 mois, je savais à peine ce que signifiait VBA, et aujourd'hui à force de recherches sur ce site j'ai pu créer un ensemble de macros qui vont me faciliter la vie quand j'aurais fini mes développements. (J'ai tout de même fait pas mal de programmation C pendant mes études, mais ça date!!)

Ne réussissant pas à trouver une réponse à mon dernier problème, je me permets d'interroger les experts :

Exposé du problème:
Je souhaite optimiser le temps d'importation des données d'un fichier texte.
Les lignes de mon fichier se présentent sous la forme 'A;B;1;2;3;4;5', le nombre de champs est fixe, mais la longueur de chaque champ est variable, les deux premiers sont des strings et les autres des nombres.
Actuellement j'utilise le code suivant dans une boucle :

Line Input #F_file, str
ligne = Split(str, ';', 7)
Sheets(sh).Cells(Tmp_line, 1).Value = ligne(0)
Sheets(sh).Cells(Tmp_line, 2).Value = ligne(1)
Sheets(sh).Cells(Tmp_line, 3).Value = ligne(2)
...
Ce code marche bien, mais il est sacrément lent. A terme, il devra traiter autour de 500 lignes à la fois, soit 3500 cellules.

Connaissez-vous des moyens d'accélérer l'affectation de valeurs à des cellules ?
Ne peut-on pas traiter une ligne entière à la fois ?

PS : Si le séparateur de champs cause problème, je peux le modifier en amont en traitant le fichier texte soit en VBA, soit au prompt avec les commandes unix portés sous dos.

Merci de votre aide.

 

[CBernardT]

Bonjour Mikaëlkaël,

Pour faciliter la compréhension de ton besoin, joints nous un extrait de ton travail. Un petit schéma vaut mieux...

Cordialement

Bernard

 

[Mikaëlkaël]

Bonjour CBernardT,

Tu as tout à fait raison. Je vais créer un nouveau fichier avec la ou les procédures indispensables à une meilleure compréhension du problème. Je l'envoi demain.

 

[MichelXld]

bonsoir Mikaëlkaël , bonsoir cher Bernard


tu peux faire un essai en utilisant une requete ADO (Microsoft ActiveX Data Objects)


Sub importFichierTexte_ADO()
'
'Necessite d'activer la reference Microsoft ActiveX Data Objects x.x Library
'
Dim Rc As Object
Dim Cn As String, Chemin As String, Fichier As String
Dim i As Long

Chemin = 'C:\\\\\\\\Documents and Settings\\\\\\\\michel\\\\\\\\dossier\\\\\\\\excel'
Fichier = 'monFichier.txt'

Cn = 'Driver={Microsoft Text Driver (*.txt; *.csv)};' & _
'Dbq=' & Chemin & ';Extensions=asc,csv,tab,txt'

Set Rc = New ADODB.Recordset
Rc.Open 'SELECT * FROM ' & Fichier, Cn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not Rc.EOF Then

For i = 0 To Rc.Fields.Count - 1 'recupere 1ere ligne
Range('A1').Offset(0, i) = Rc.Fields(i).Name
Next
Range('A2').CopyFromRecordset Rc

End If

Rc.Close
Set Rc = Nothing
End Sub


bonne soirée
MichelXld

 

[Mikaëlkaël]

Bonjour,
A la demande de CbernardT, j’ai joint à ce post la procédure qui me cause du tracas, ainsi qu’un fichier de données pour l’import.
Allez directement dans l’onglet LG, puis « Importer les données », le fichier « FR0000120537.txt » doit être dans le même répertoire.

J’ai identifié que c’était la méthode utilisée pour renseigner les valeurs dans les cellules qui prenait beaucoup de temps (une vingtaine de secondes sur un processeur à 1.6 Ghz lors du premier import). Avez-vous des idées pour accélérer cette partie du code ??, d’autres astuces pour accélérer le tout ?

PS1 : la première importation est très lente, les tests suivants sont plus rapides car windows/excel doit maintenir des données en mémoire, ce qui a pour effet d’accélérer considérablement les importations suivantes. (Après avoir lancé 2 ou 3 applications lourdes on arrive à retrouvé un temps d’exécution proche du premier).

PS2 : J’utilise le « . » en tant que séparateur décimal, si ce n’est pas votre cas, il y a probablement une ligne à supprimer dans le code.


MichelXld,

j'essaye ta méthode ce soir et vous tiens au courant.

Message édité par: Mikaëlkaël, à: 06/03/2006 19:32

 

[Mikaëlkaël]

J’ai un souci pour vous envoyer un fichier exemple car je n’arrive pas à descendre en dessous des 50ko. Je vous fournis tout de même le fichier à importer et le code initial.


L’exemple de MichelXld est absolument stupéfiant en termes de différence de rapidité !!! Je pensais pouvoir diviser le temps d’exécution par 2, voire 4, mais là !!! Ce qui me prenait parfois 40 à 120 secondes se fait en 1 seconde.

Cependant cela me cause certains problèmes lorsque j’utilise cette méthode dans mon code :
1/ Mon fichier compte 7 colonnes, mais je ne veux pas importer la première (ce point n’est pas très grave car je peux m’arranger en la cachant)
2/ Ce qui me gêne le plus c’est que chaque fichier texte à importer correspond à un onglet. Quand j’en importe un la première fois, tout vas bien, par contre les fois suivantes, je n’importe que le delta (c'est-à-dire les n dernières lignes du fichier, car celui-ci a été mis à jour entretemps et est trié en ordre inverse). Il y a-t-il un moyen pour ne pas importer les n premières lignes du fichier ?
3/ Les lignes du fichier sont triés par date et dans l’ordre inverse de ce que je veux obtenir dans excel. Il y a-t-il un moyen de les importer à partir du bas dans excel et par conséquent donc dans l’ « ordre de lecture du fichier » ?
4/ Vu la rapidité d’exécution, je suis tenté d’utiliser cette méthode pour créer le fichier d’import lui-même, ma source initiale est beaucoup plus complexe. Actuellement je génère 250 fichiers comme ceux que je vous ai fournis à partir de la source initiale. Est-il possible d’écrire des Recordset dans un fichier texte ?

Ci dessous mon code initial (il est moins d’actualité maintenant, mais permet de mieux cerner le problème)

Public Const First_line As Byte = 10
Public Const First_cell As String = 'A10'

Public Sub rempli_cours(sh As Byte, nb_sauts As Integer, nb_import As Integer)
Dim F_ISIN As Integer, i As Integer, Num_line As Integer, Tmp_line As Integer
Dim str As String, ligne() As String

Application.ScreenUpdating = False
Sheets(sh).Rows(First_line & ':' & First_line + nb_import - 1).Insert Shift:=xlDown
'Insère le nombre de lignes nécessaires pour l'import des cotations à partir de la ligne 10 (First_line)

F_ISIN = FreeFile
Open ActiveWorkbook.Path & '\\' & Sheets(sh).Range('C2').Value & '.txt' For Input As #F_ISIN
‘Saute les lignes à ne pas importer
For i = 1 To nb_sauts
Line Input #F_ISIN, str
Next i

Num_line = First_line + nb_import 'numéro de la ligne suivant la dernière ligne à importer
With Sheets(sh)
'importe toutes les lignes du fichier en commençant par la première soit la dernière sur excel
For i = 1 To nb_import
Line Input #F_ISIN, str
ligne = Split(str, ';', 7) 'renseigne le tableau ligne
Tmp_line = Num_line - i
.Cells(Tmp_line, 1).Value = CDate(Format(ligne(1), 'dd/mm/yy'))
.Cells(Tmp_line, 2).Value = ligne(2)
.Cells(Tmp_line, 3).Value = ligne(3)
.Cells(Tmp_line, 4).Value = ligne(4)
.Cells(Tmp_line, 5).Value = ligne(5)
.Cells(Tmp_line, 6).Value = ligne(6)
Next i
End With
Close #F_ISIN

'Met en forme les données insérées en fonction de la ligne suivant la dernière ligne importée (elle a été pré formaté)
Range(Cells(First_line + nb_import, 1), Cells(First_line + nb_import, 6)).Select
Selection.Copy
Range(Cells(First_line, 1), Cells(First_line + nb_import - 1, 6)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'remplace les virgules par des points sur les lignes importés (Le séparateur décimal est le '.' sur mon PC)
'Je pense que cette ligne est à mettre en commentaire si ce n'est pas le cas chez vous
Selection.Replace What:=',', Replacement:='.', LookAt:=xlPart

'Copie les formules en face des données importées à partir de la ligne suivant la dernière ligne importée
Range(Cells(First_line + nb_import, 8), Cells(First_line + nb_import, 50)). _
Copy (Sheets(sh).Range(Cells(First_line, 8), Cells(First_line + nb_import - 1, 50)))

Sheets(sh).Range('F2').Select 'Supprime visuellemnent à l'écran la dernière sélection
Application.ScreenUpdating = True
End Sub
[file name=FR0000120537.zip size=6459]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/FR0000120537.zip[/file] [file name=FR0000120537.zip size=6459]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/FR0000120537.zip[/file]

 

[MichelXld]

bonjour Mikael

1/ Mon fichier compte 7 colonnes, mais je ne veux pas importer la première (ce point n’est pas très grave car je peux m’arranger en la cachant)

Oui ,Il est possible de filtrer les colonnes : l'ideal serait si possible d'avoir des entetes pour chaque colonne du fichier Texte . cela faciliterait le traitement des données nottament pour les tris et les filtres que tu évoques dans les autres questions .

2/ Ce qui me gêne le plus c’est que chaque fichier texte à importer correspond à un onglet. Quand j’en importe un la première fois, tout vas bien, par contre les fois suivantes, je n’importe que le delta (c'est-à-dire les n dernières lignes du fichier, car celui-ci a été mis à jour entretemps et est trié en ordre inverse). Il y a-t-il un moyen pour ne pas importer les n premières lignes du fichier ?

3/ Les lignes du fichier sont triés par date et dans l’ordre inverse de ce que je veux obtenir dans excel. Il y a-t-il un moyen de les importer à partir du bas dans excel et par conséquent donc dans l’ « ordre de lecture du fichier » ?

Un exemple pour importer les 10 derniers enregistrements par ordres décroissant de date

Rc.Open 'SELECT TOP 10 * FROM ' & Fichier & ' ORDER BY laDate DESC;', _
Cn, adOpenForwardOnly, adLockReadOnly, adCmdText

(en supposant que tu as ajouté des entetes à chaque colonne , dont un nommé 'laDate' )

4/ Vu la rapidité d’exécution, je suis tenté d’utiliser cette méthode pour créer le fichier d’import lui-même, ma source initiale est beaucoup plus complexe. Actuellement je génère 250 fichiers comme ceux que je vous ai fournis à partir de la source initiale. Est-il possible d’écrire des Recordset dans un fichier texte ?

Oui , peux tu preciser le format de la source de données ?


ce ne sont que des infos générales à adapter en fonction de ton projet



bonne journée
MichelXld

 

[Mikaëlkaël]

MichelXLD,

Merci pour ces réponses, à moi de bosser maintenant et de voir comment adapter tout ça dans mon code.
Pour répondre à ta question, la source initiale reprend la même structure de colonnes que le fichier que j'ai joint.
La différence est que le fichier d'import, que je construis, est dédié à une valeur FRXXXXXXX et comporte ses cotations depuis plusieurs années.
Alors que la source initiale (téléchargeable sur le net) contient les cotations de 250 valeurs sur une durée maximum de 30 jours.
Quand je met à jour un fichier d'import, je dois donc faire un contrôle sur les champs date et valeur ...

Le fichier d'import est en fait une 'base locale' simple qui a l'historique d'une valeur.

J'ai récupérer de la doc sur les recordset dans ce forum, cependant as-tu des liens sur les possibilités qu'offre cet outil ?

 

[Mikaëlkaël]

Merci beaucoup de votre aide, j'ai terminé mon code sur cet aspect, je m'attaque maintenant à d'autres points.