Charger un fichier .txt plus rapidement dans Excel (optimisation de code)Résolu

Question

Bonjour le Forum,

Il y a un an (lors de mes premiers pas sous vba) j'avais un premier morceau de code qui me permettait de charger le contenu d'un fichier texte vers une feuille Excel.

Pour mes premières applications le code était super car le nombre de données à charger était moyen (6 colonnes pour 8 000 lignes en général). Récemment je rencontre une difficulté supplémentaire...J'ai des fichiers Texte "Gourmands" à charger vers Excel (toujours 5 ou 6 colonnes mais 90 000 lignes)

Le code que j'utilise rame donc pour charger tout ça. Récemment j'ai appris que les tableaux dynamiques permettaient un gain de temps pour charger des éléments. Je me pose alors la question si mon code est optimisé ou non ?

Le code serait il plus rapide s'il chargeait les éléments du fichier texte directement vers un tableau, puis ensuite seulement on réinjecterait le contenu vers la feuille Excel ?

Bref qu'elle solution est la plus efficace dans mon cas ?


Voici le code que j'utilise actuellement :

Option Explicit

Public Sub IMPORT()

Dim i As Long 'Variables
Dim Chemin As String, IMPORT As String

i = 1 'Initialise la première ligne
Chemin = "TEST" 'Pour que la boucle ne s'arrête pas tout de suite
Chemin = Application.GetOpenFilename("Texte, *.txt")  'Boite de dialogue

If InStr(Chemin, "") = 0 Then 'Si Chemin n'a pas de \ donc que l'opérateur a fait annuler
GoTo Fin 'Va à l'étiquette Fin:
End If 'Fin du test
Open Chemin For Input As #1 'Ouvre un fichier en lecture
Do While Not EOF(1) 'Tant qu'on est pas à la dernière ligne
Line Input #1, IMPORT 'Lit la ligne et la stocke dans Import
Range("A" & i & ":E" & i).Value = Split(Replace(IMPORT, ",", "."), Chr(9))
'Copie dans la ligne I colonnes A à E la ligne importée où les "," sont remplacées par des "."
'et Split crée un tableau en fonction des tabulations (Chr(9))
'C'est là qu'on pourrait faire appel aux 2 dernières valeurs du tableau, mais ce serait plus lourd
i = i + 1 'On augmente le N° de la ligne
Loop 'On boucle dans le fichier
Close #1 'On ferme le fichier
Fin: 'Etiquette pour quand on sort de la boucle
If i = 1 Then Exit Sub 'Si on a pas importer de fichier, on quitte la Sub
'Le problème, c'est que le Split envoie des nombres au format texte, d'où le traitement complémentaire
Range("I1") = 1 'Met 1 en I1
Range("I1").Copy 'Copie I1
Range("A1:E" & i - 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
'Collage spécial en multiplication par 1 qui transforme les valeurs textes en nombres
Application.CutCopyMode = False 'Désactive la sélection du copier
Range("I1").Clear 'Ote le 1 en I1

End Sub


Merci à ceux qui pourront m'apporter des éléments de réponse.

Bien cordialement,

André

ucfoutu · Accepted Answer

Ben ..
Ultra simple : tu lui donnes une extension csv et tu l'importes en tant que tel dans excel, puisqu'il utilise toujours le même séparateur (tabulation) !
Je me suis amusé à le faire avec ton bout de fichier, que j'ai appelé toto.csv.
J'ai utilisé l'enregistreur de macro ===>> regarde ce qu'il a écrit : ===>>
Sub Macro4()
'
' Macro4 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\ANDRECOURBE	oto.csv", _
        Destination:=Range("$A$1"))
        .Name = "toto_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

J'ai tout effacé sur la feuille, pour vérifier ===>> j'ai relancé la macro, pour voir ===>> impeccable !
Tu n'iras pas plus vite que ne le peut MLicrosoft (qui, lui-même, doit d'ailleurs passer en coulisse par un tableau dynamique)
Pense seulement à Inhiber puis remettre l'affichage (ScreenUpdating).
Plus vite, nul ne le pourrait.

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

Bonjour, André,
La réponse la plus adéquate dépend de la structure de ton fichier texte.
Peux-tu nous en mettre quelques lignes ici ? Qu'on puisse le "voir" ?


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

SERIEUXETCOOL · Answer

Bonjour Ucfoutu, le Forum,

Pour visualiser la structure du fichier je peux aider 

Du point de vue de la création et du contenu de ce fichier : Il contient les données des capteurs d'une machine. A chaque capteur, on associe une colonne, une entête, et les valeurs vu par le capteur.

En général j'ai 6 capteurs simultanément montés dans la machine. Donc je vais avoir grosso modo 6 colonnes (ce paramètre doit être réglable car on imagine bien que le nombre de capteurs peut varier, et donc automatiquement le nombre de colonnes également). L'entête de la colonne nomme le capteur pour pouvoir s'y retrouver.

Visuellement ça peut ressembler à ceci :

Temps[Tabulation]ForceX[Tabulation]ForceY[Retour de ligne]
0.00[Tabulation]0.00[Tabulation]0.00[Retour de ligne]
1.00[Tabulation]10[Tabulation]12.345666[Retour de ligne]
10.34[Tabulation]10[Tabulation]20.00[Retour de ligne]
15[Tabulation]10[Tabulation]100

Dans cet exemple j'ai simulé 3 capteurs (Donc 3 colonnes) et j'ai placé 4 lignes supplémentaires qui correspondent à 4 mesures. Voila en gros la structure du fichier. Les données sont espacées par des tabulations.

Je précise bien que le nombre de lignes total n'est pas connus à l'avance, mais que le nombre de colonnes oui. Je précise également que la structure est toujours la même. C'est à dire une colonne qui contient une entête et des données. Dans le fichier Texte, le tout premier caractère (Le plus en haut à gauche) est le début de l'entête. Pas de blancs, pas de vides.

Je place tout de même un téléchargement un fichier texte d'exemple au cas ma description ne soit pas claire et si jamais vous voulez un fichier de test.

André

Ps : Le lien pour le fichier

http://fr.packupload.com/FHPDFW5Z1E0

ucfoutu · Answer

Non, pas avec des "[tabulation]" rajoutés !
Tel qu'il est, s'il te plait.
Quelques ligne suffiront (copie/colle)
Je n'ouvre pas ton lien. Pourquoi ? ===>> regarde le message de mon protecteur :
Site Web malveillant bloqué

Vous avez tenté d&#8217;accéder à :

http://fr.packupload.com/FHPDFW5Z1E0

Il s&#8217;agit d&#8217;un site Web malveillant connu. Il vous est recommandé de ne PAS visiter ce site. Le rapport détaillé décrit les risques de sécurité de ce site.




________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

Et voilà le rapport :
Résumé
&#8226;Menaces informatiques : 	
1
&#8226;Menaces portant sur l'identité : 	
0
&#8226;Facteurs d'ennui :

0


Menaces totales sur ce site : 	
1


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

SERIEUXETCOOL · Answer

ah oki je vais donc en copier une petite partie et les collées telles que le fichier est. Je sais pas si ça va bien aimer. Je fais ça de suite.

Pour le site, c'est vrai que j'ai pris le premier venu sur le net. Mais ce qui m'étonne plus c'est que j'utilise "Kaspersky Internet Security" comme protection. Il est pourtant redoutablement fiable. Il m'a déjà signalé des sites malveillants. Mais il ne me dit rien pour ce site d'hébergement.

Bref je suis surpris quand même. Je changerai de site du coup^^ Merci pour l'info Ucfoutu

SERIEUXETCOOL · Answer

Je colle directement dans le message :

N° seq	Temps (s)	Vitesse bille (mm/s)	Vitesse plan (mm/s)	Couple (N.m)	Fz Statique (N)	Fx dynamique (N)	
0.0000	0.5200	0.0000	0.0000	0.0076	5.5416	-0.0003
0.0000	1.0040	0.0000	0.0000	0.0056	5.7191	-0.0002
0.0000	1.5090	0.0000	0.0000	0.0056	5.7099	-0.0002
0.0000	2.0140	0.0000	0.0000	0.0057	5.7245	-0.0002
0.0000	2.5160	0.0000	0.0000	0.0058	5.7055	-0.0002
0.0000	3.0180	3922155.2182	0.0000	0.0057	5.7232	-0.0002
0.0000	3.7380	-2611288.4276	0.0000	0.0058	5.7120	-0.0002
0.0000	4.2390	0.0000	0.0000	0.0058	5.7124	-0.0002
0.0000	4.7410	0.0000	0.0000	0.0058	5.7204	-0.0002
0.0000	5.2430	0.0000	0.0000	0.0059	5.7040	-0.0002
0.0000	5.7440	0.0000	0.0000	0.0058	5.7216	-0.0002
0.0000	6.2450	0.0000	0.0000	0.0058	5.7064	-0.0002
0.0000	6.7460	3930015.2487	0.0000	0.0057	5.7209	-0.0002
0.0000	7.2470	0.0000	0.0000	0.0056	5.7068	-0.0002


Je recolle dans une citationa au cas ou :

N° seq	Temps (s)	Vitesse bille (mm/s)	Vitesse plan (mm/s)	Couple (N.m)	Fz Statique (N)	Fx dynamique (N)	
0.0000	0.5200	0.0000	0.0000	0.0076	5.5416	-0.0003
0.0000	1.0040	0.0000	0.0000	0.0056	5.7191	-0.0002
0.0000	1.5090	0.0000	0.0000	0.0056	5.7099	-0.0002
0.0000	2.0140	0.0000	0.0000	0.0057	5.7245	-0.0002
0.0000	2.5160	0.0000	0.0000	0.0058	5.7055	-0.0002
0.0000	3.0180	3922155.2182	0.0000	0.0057	5.7232	-0.0002
0.0000	3.7380	-2611288.4276	0.0000	0.0058	5.7120	-0.0002
0.0000	4.2390	0.0000	0.0000	0.0058	5.7124	-0.0002
0.0000	4.7410	0.0000	0.0000	0.0058	5.7204	-0.0002
0.0000	5.2430	0.0000	0.0000	0.0059	5.7040	-0.0002
0.0000	5.7440	0.0000	0.0000	0.0058	5.7216	-0.0002
0.0000	6.2450	0.0000	0.0000	0.0058	5.7064	-0.0002
0.0000	6.7460	3930015.2487	0.0000	0.0057	5.7209	-0.0002
0.0000	7.2470	0.0000	0.0000	0.0056	5.7068	-0.0002


Et je recolle en tant que code vba tjs au cas ou :

N° seq	Temps (s)	Vitesse bille (mm/s)	Vitesse plan (mm/s)	Couple (N.m)	Fz Statique (N)	Fx dynamique (N)	
0.0000	0.5200	0.0000	0.0000	0.0076	5.5416	-0.0003
0.0000	1.0040	0.0000	0.0000	0.0056	5.7191	-0.0002
0.0000	1.5090	0.0000	0.0000	0.0056	5.7099	-0.0002
0.0000	2.0140	0.0000	0.0000	0.0057	5.7245	-0.0002
0.0000	2.5160	0.0000	0.0000	0.0058	5.7055	-0.0002
0.0000	3.0180	3922155.2182	0.0000	0.0057	5.7232	-0.0002
0.0000	3.7380	-2611288.4276	0.0000	0.0058	5.7120	-0.0002
0.0000	4.2390	0.0000	0.0000	0.0058	5.7124	-0.0002
0.0000	4.7410	0.0000	0.0000	0.0058	5.7204	-0.0002
0.0000	5.2430	0.0000	0.0000	0.0059	5.7040	-0.0002
0.0000	5.7440	0.0000	0.0000	0.0058	5.7216	-0.0002
0.0000	6.2450	0.0000	0.0000	0.0058	5.7064	-0.0002
0.0000	6.7460	3930015.2487	0.0000	0.0057	5.7209	-0.0002
0.0000	7.2470	0.0000	0.0000	0.0056	5.7068	-0.0002



Et voila. On va voir si ça ressort bien.

SERIEUXETCOOL · Answer

Ps : Dommage que ce site n'intègre pas un mode interne de gestion des pièces jointes

SERIEUXETCOOL · Answer

Je n'ai pas tout suivi Ucfoutu.

Tu donnes une nouvelle extension à mon fichier ASCII. Par vba, manuellement ?
Pour rajouter l&#8217;extension .csv, peut on faire "renommer fichier" puis rajouter ".csv" ou ce n'est pas la bonne procédure ???


Est-il possible de faire le tout par vba ? c'est à dire en automatique. On évite ainsi toutes ces questions justement. Grosso modo j'ai en entrée :

-Un fichier texte (pas un fichier .csv)
-J'ai le code suivant pour récupérer le chemin du fichier texte
Chemin = Application.GetOpenFilename("Texte, *.txt")  'Boite de dialogue

C'est la base minimale de départ. Il manque la modification de l'extension .txt en .csv et c'est tout je pense. Non ?



Je verrais bien la chose suivante :

*Je charge mon fichier texte par vba
*Je modifie l&#8217;extension .Txt en .csv par vba
*Et la je peux utiliser le code que tu as proposé pour importer le tout sur Excel avec prise en compte automatique du non du fichier en .csv


La comme ça je reste bloqué sans pouvoir charger de fichier car j'ai pas tout compris. Si l'on ne peut pas modifier l'extension .txt en .csv par vba, je crains que ça ne me plaise pas trop. Je ne peux pas contraindre les utilisateurs à convertir eux même les fichiers .txt en .csv. C'est embêtant. Moi même je ne suis pas sur de la marche à suivre pour modifier l'extension 


Serait il possible d'avoir un peu plus de détails Ucfoutu ?

SERIEUXETCOOL · Answer

Nan je dois mal m'y prendre, car j&#8217;obtiens l'erreur suivante :

"Impossible de trouve le fichier texte pour l'actualisation de cette plage de données externes.
Assurez vous que le texte n'a été ni déplacé ni renommé et réessayez"

C'est sans doute au moment de modifier l&#8217;extension. Je m'y prends mal.


Voici le début du code que j'utilise :

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Documents and Settings\MAGIC DD\Bureau\FICHIER DE TEST.csv", _
        Destination:=Range("$A$1"))


Voila pourquoi je cherche à changer l'extension en automatique par vba si possible.

André

ucfoutu · Answer

Tu n'as même pas besoin de changer l'extension. Elle peut  rester en txt et cela marche. Je viens de le vérifier. ===>> pas de problème !


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

SERIEUXETCOOL · Answer

Bien après quelques petites recherches, le format .csv est un fichier au format texte et non binaire. Les données sont séparées par des virgules.

Donc pour ouvrir mon Fichier texte UCfoutu, tu propose de l'ouvrir par Excel, puis de l'enregistrer au format .csv, puis de nouveau de l'ouvrir pour en importer les données ???

Je ne suis pas vraiment sur que c'est ce que tu fait. La boucle est bouclée ! On tourne en rond pour rien avec mon raisonnement.

Je n'ai donc toujours pas compris ta méthode "Ultrat simple" Ucfoutu 

Je dois être ultrat C... 

Il doit me manquer un petit détail qui à son importance...

Bien cordialement,

André

ucfoutu · Answer

Si tu n'y parviens pas, je ne sais comment tu t'y prends !
J'y parviens à tous coups, avec plusieurs fichiers !
Utilise donc l'enregistreur de macos Macros>> enregistrer une macro ===>> 
quand lancé : données ===>> à partir du texte ===>> ti renseignes jusqu'à la fin ===>> tu arrêtes l'enregistreur ===>> puis tu rouvres ta macro en moduf et tu vois ...



________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

Et (je le répète) : marche même en gardant l'extension txt !
Je peux faire beaucoup, sauf venir sur ta machine 


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

Et non : le séparateur peut ne pas être une virgule ! c'est toi, qui le définit !


________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

SERIEUXETCOOL · Answer

ayé j'ai enfin reussi à charger mon fichier .txt moi aussi. Avec ton indice du post précédent j'ai mis de coté le format .csv.

Je vais faire un ou deux tests de rapiditié d'exécution pour voir quelle méthode vba permet de charger mes 100 000 lignes le plus rapidement...

Je reviens prochainement. Pourvu que ça charge plus vite !!!!

Merci Ucfoutu pour ce début 

André

SERIEUXETCOOL · Answer

Après test sur le même fichier texte contenant 100 000 lignes, il s'avère que ta méthode Ucfoutu est plus rapide.

Elle charge les données 4 fois plus vite que la méthode que je proposais au début.

Mais toute fois il y a un truc qui cloche. Ta méthode Ucfoutu charge en 3,5 secondes. L'autre méthode en 14 secondes. C'est certes plus rapide mais ce n'est pas encore ce qui fait ramer ma macro.

Chez moi ça met 1Min30 pour charger les données et y appliquer une mise en forme personnelle.

C'est probablement de l'autre coté que ça prends du temps pour exécuter le code.

Bon néanmoins j'ai une meilleur méthode pour charger mes données maintenant. C'est déjà un rapport de 4 de gagné !


Il faut donc que j'optimise également ma procédure de mise en forme qui doit être gourmande. Elle marche donc bien pour de faibles quantités de données, mais n'est plus optimisée pour de grosses sessions. En gros je charge mes données, puis j'applique une petite mise en forme. Donc si c'est pas l'un qui prend du temps, c'est l'autre^^

Merci Ucfoutu pour cette méthode de chargement. C'est impec. Au moins plus de besoin de spécifier le nombre de colonnes ni rien. C'est automatique et auto-adapté.

Merci,

André


Ps : Ucfoutu, veux tu par hasard jeter un coup deuil à ma procédure de mise en forme et essayer d'optimiser les temps ou ça ne te branche pas trop ? C'est pas grave si tu ne veux pas. C'est pas la partie la plus intéressante j'en conviens. Si tu es intéressé, j'ouvre une autre discussion pour grappiller des dizaines de secondes à mon code. Sinon j'attendrai 2 min devant mon pc que tout se charge

ucfoutu · Answer

Si tu es intéressé, j'ouvre une autre discussion pour grappiller des dizaines de secondes à mon code. Sinon j'attendrai 2 min devant mon pc que tout se charge 

Montre toujours.
Tout dépendra, toutefois, de la complexité de cette mise en forme.

________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

J'appelle par ailleurs ton attention sur le fait que cette partie du code figurant dans ton 1er message, à savoir :
Range("I1") = 1 'Met 1 en I1
Range("I1").Copy 'Copie I1
Range("A1:E" & i - 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
*
ne fait rien d'autre que ferait tout bêtement ceci :
Range("A1:E" & i - 1)=1
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

ucfoutu · Answer

Alors : juste après avoir mis mon bulletin dans l'urne, un grand gaillard s'est mis à hurler (il devait croire que j'étais sourd) : "A voté !"
Ah bon !
Nous allons donc faire la même chose : constater, comme lui. Aussi surprenant que cela puisse paraître :
Range("I1") = 1 'Met 1 en I1
Range("I1").Copy 'Copie I1
Range("A1:E" & i - 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
devient donc :
Range("A1:E" & i - 1).Value = Range("A1:E" & i - 1).Value


Surprenant ? Pas tant que cela, quand on y pense deux fois !
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ

Charger un fichier .txt plus rapidement dans Excel (optimisation de code)

22 réponses

Votre réponse

sur les mêmes thèmes