Optimisation Duree Traitement FichierCSV avec VBA

Le 12/11/2017 à 19:05

Bonjour, Bonsoir à tous,

Je sollicite vos conseils pour m’aider à accélérer le traitement de ce programme.

J’essaie de traiter en automatique des dizaines de fichiers CSV au format identique (3 colonnes de valeurs, à chaque ligne une valeur pour 10mn). Pour des fichiers comprenant 1 mois de données (4400 lignes) j’arrive à tourner rapidement avec 10 à 15 fichiers, lorsqu’il s’agit de 24 mois de données (104000 lignes) c’est très très lent.

Le programme pour chaque fichier CSV :

  • Ouverture du fichier
  • Copie des data dans un tableau tampon VBA (tableauCSV) avec une boucle
  • Création d’un nouvel onglet pour recopie des données du tableau tampon VBA (tableau CSV), avec une boucle For
  • Conversion dans le nouvel onglet de la colonne string (avec les virgules) en plusieurs colonnes de valeurs, avec la fonction XLS « convertir »
  • Copie des data du nouvel onglet dans tableau tampon VBA (tableauColonne) puis transformation des valeurs unicolonne en 6 colonnes, avec une boucle For
  • Affectation des valeurs du tableau tampon VBA (tableauColonne) aux colonnes du nouvel onglet, avec une boucle For

Environnement : MAC OSX 10.11.6 + Excel 2016

Pour optimiser le traitement, je pense que :

  • Ouverture des fichiers CSV : elle se fait réellement, il est possible de rapatrier les données sans ouvrir le fichier, mais je suis à mes limites de compréhension du code
  • Copie des data du fichier CSV dans le tableau tampon VBA (tableau CSV), existe-t-il une solution plus simple et rapide qu’une boucle ?
  • Conversion d’un unicolonne CSV en multi-colonne XLS : vaut-il mieux utiliser la fonction « convertir » d’XLS directement dans un onglet ou est-il préférable de faire le traitement dans le tableau tampon VBA (tableau CSV) ?
  • Je copie une feuille vers un tableau VBA puis renvoie les données vers une feuille pour les réintégrer dans un autre tableau VBA et ensuite rapatrier les valeurs vers la feuille, est-ce trop ?
  • Comment pourrais-je dimensionner 1 seul tableau VBA dans lequel tout faire ?

J’espère que j’ai été suffisamment clair pour votre compréhension. Ci-joint mon fichier avec la procédure (onglet source CSV : fichier tel qu’ouvert par la routine, onglet 30000…. : données en fin de routine, onglet synthese : là où je récupère les noms de fichier csv)

Mon code vous fera peut-être sourire, soyez indulgent car à ce stade je ne sais pas mieux faire !

Merci par avance à celui ou celle d’entre vous qui pourra m’aiguiller.

Rafael

Option Explicit

Option Base 1

Sub Ouverture2Fichier()

'

Dim indexPRM As Integer

Dim indexFichier As Integer

Dim nombrePRM As Integer

Dim Chemin As String

Dim MonChemin As String

Dim FichierOuvrir As String

Dim FeuilleActive As String

Dim NomOnglet As String

Dim TableauCSV() As Variant

Dim TableauColonnes As Variant

Dim NbLignes As Long

Dim NbLignes6C As Long

Dim Nblignes2C As Long

Dim RefPRM As String

Dim RefPuiss As String

Dim CellTest As String

Dim i As Long

Dim IndexLigne As Long

Dim IndexLigneUniCol As Long

Dim indexColonne As Integer

Dim TotalLignes As Long

Dim TotalHeure As Integer

Dim moyHeure As Integer

Dim maxHeure As Integer

Dim Puiss10 As Integer

Dim Puiss20 As Integer

Dim Puiss30 As Integer

Dim Puiss40 As Integer

Dim Puiss50 As Integer

Dim Puiss60 As Integer

Chemin = ThisWorkbook.Path

MonChemin = ThisWorkbook.Path & Application.PathSeparator

Sheets("synthese").Cells(3, 2) = MonChemin

'Nombre de PRM

nombrePRM = Sheets("synthese").Cells(4, 3)

'MsgBox nombrePRM

' Lancement de la procedure pour chaque PRM

For indexPRM = 1 To 1 'nombrePRM

indexFichier = indexPRM + 5

FichierOuvrir = MonChemin & Sheets("synthese").Cells(indexFichier, 2)

'MsgBox FichierOuvrir

Sheets("synthese").Cells(5, 5) = FichierOuvrir

Workbooks.OpenText Filename:=FichierOuvrir

ActiveSheet.Select

NbLignes = ActiveSheet.UsedRange.Rows.Count + 1

NbLignes6C = NbLignes / 6

NbLignes6C = Application.WorksheetFunction.RoundUp(NbLignes6C, 0) + 1

Nblignes2C = NbLignes / 2

Nblignes2C = Application.WorksheetFunction.RoundUp(Nblignes2C, 0) + 1

'MsgBox NbLignes & "-- en 6C--" & NbLignes6C & "-- en 2C --" & Nblignes2C

' Copie des donnees de la feuille csv ouverte dans tableau VBA

ReDim TableauCSV(NbLignes, 20)

For i = 1 To NbLignes

TableauCSV(i, 1) = ActiveSheet.Cells(i, 1)

Next i

' MsgBox TableauCSV(1, 1) & "--------" & TableauCSV(2, 1) & "--------" & TableauCSV(3, 1)

'Fermeture du fichier CSV

ActiveWorkbook.Close False

'Creation d'une feuille pour copie des donnees du tableau VBA vers nouvelle feuille + traitement des donnees

NomOnglet = Sheets("synthese").Cells(indexFichier, 3)

' MsgBox NomOnglet

Sheets.Add.Name = NomOnglet

' Copie de tableauCSV dans nouvel onglet

For i = 1 To NbLignes

ActiveSheet.Cells(i, 1) = TableauCSV(i, 1)

Next i

' Traitement des donnees du tableau CSV : separation virgules vers colonnes

Columns("A:A").Select

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _

Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _

Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _

))

' TableauColonnes pour conversion 6 colonnes

ReDim TableauColonnes(NbLignes, 20)

For indexColonne = 1 To 20

TableauColonnes(1, indexColonne) = Sheets("Masque").Cells(1, indexColonne).Value

Next

IndexLigne = 2

IndexLigneUniCol = 2

'Test sur conformite des donnees

CellTest = Sheets(NomOnglet).Cells(2, 3).Value

'MsgBox "1ere valeur =" & CellTest

If CellTest = NomOnglet Then

Sheets(NomOnglet).Cells(2, 3).Value = 0

RefPuiss = Sheets(NomOnglet).Cells(2, 11).Value

Else

RefPuiss = Sheets(NomOnglet).Cells(2, 13).Value

End If

'MsgBox RefPuiss & "-- format puissance"

For IndexLigne = 2 To NbLignes

TableauColonnes(IndexLigne, 4) = IndexLigneUniCol

TableauColonnes(IndexLigne, 5) = Sheets(NomOnglet).Cells(IndexLigneUniCol, 1).Value

TableauColonnes(IndexLigne, 6) = Sheets(NomOnglet).Cells(IndexLigneUniCol, 2).Value

TableauColonnes(IndexLigne, 7) = Sheets(NomOnglet).Cells(IndexLigneUniCol, 3).Value / 1000

TableauColonnes(IndexLigne, 8) = Sheets(NomOnglet).Cells(IndexLigneUniCol + 1, 3).Value / 1000

TableauColonnes(IndexLigne, 9) = Sheets(NomOnglet).Cells(IndexLigneUniCol + 2, 3).Value / 1000

TableauColonnes(IndexLigne, 10) = Sheets(NomOnglet).Cells(IndexLigneUniCol + 3, 3).Value / 1000

TableauColonnes(IndexLigne, 11) = Sheets(NomOnglet).Cells(IndexLigneUniCol + 4, 3).Value / 1000

TableauColonnes(IndexLigne, 12) = Sheets(NomOnglet).Cells(IndexLigneUniCol + 5, 3).Value / 1000

Puiss10 = TableauColonnes(IndexLigne, 7)

Puiss20 = TableauColonnes(IndexLigne, 8)

Puiss30 = TableauColonnes(IndexLigne, 9)

Puiss40 = TableauColonnes(IndexLigne, 10)

Puiss50 = TableauColonnes(IndexLigne, 11)

Puiss60 = TableauColonnes(IndexLigne, 12)

moyHeure = Application.WorksheetFunction.Average(Puiss10, Puiss20, Puiss30, Puiss40, Puiss50, Puiss60)

maxHeure = Application.WorksheetFunction.Max(Puiss10, Puiss20, Puiss30, Puiss40, Puiss50, Puiss60)

TableauColonnes(IndexLigne, 13) = moyHeure

TableauColonnes(IndexLigne, 14) = maxHeure

TableauColonnes(IndexLigne, 15) = TableauColonnes(IndexLigne, 5)

TableauColonnes(IndexLigne, 16) = TableauColonnes(IndexLigne, 6)

IndexLigneUniCol = IndexLigneUniCol + 6

Next IndexLigne

For IndexLigne = 1 To NbLignes6C

For indexColonne = 4 To 20

Sheets(NomOnglet).Cells(IndexLigne, indexColonne).Value = TableauColonnes(IndexLigne, indexColonne)

Next indexColonne

Next IndexLigne

'Mise en forme des colonnes

Columns("F:F").NumberFormat = "0,0000000"

Columns("G:N").NumberFormat = "0,00"

Columns("f:f").NumberFormat = "h:mm;@"

Next indexPRM

End Sub

17traitement2ans-v.xlsm (0.99 Mo)
Le 24/11/2017 à 22:42

Salut Rafael,

un début de quelque chose car je ne comprends pas :

  • où tu vas puiser les données du tableau en '3000...' de [F3:L7]
  • comment enchaîner l'affichage des données (tu parles de données de 24 mois...)

Il faudrait une source CSV plus fournie et un '3000...' plus exemplatif de ce que tu souhaites comme résultat!

Ici, je "capte" la source CSV dans tData dont je "splitte" les données d'après ";".

'
Dim tData, tSplit
'
'on prend les données dans 'Source CSV'
iRow = Range("A" & Rows.Count).End(xlUp).Row
tData = Range("A1:A" & iRow).Value
With Worksheets("30000510217077") 'NomOnglet
    .Range("A1:N1").Value = Worksheets("Masque").Range("A1:N1").Value
    For x = 2 To UBound(tData, 1)
        tSplit = Split(tData(x, 1), ";")
        Union(.Range("A" & x), .Range("E" & x)).Value = tSplit(0)    'affichage de la date -tSplit(0)- en [A x] et [E x]
    Next
End With
'

A suivre...

A+

Le 24/11/2017 à 22:59

Bonjour,

Pourquoi ne pas créer une requête qui va chercher tes données des fichiers csv (source de données- requête) vers ton classeur excel et ensuite traiter tes données dedans?

Cdt,

sandrine

Rechercher des sujets similaires à "optimisation duree traitement fichiercsv vba"