XL 2019 VBA et Power Query - Barre de progression

[Flx1er]

Bonjour,
Je dois importer des fichiers CSV de plusieurs milliers de lignes. Ce traitement prend un certain temps.

Est-il envisageable de récupérer le nombre de lignes chargées (suite à l'actualisation) et le nombre maximum de lignes de la source de données pour concevoir une barre de progression en VBA afin de faire patienter la personne chargée de ce travail?

Ou bien existe-t-il une autre méthode pour créer une barre de progression ?

Mon exemple est relativement sommaire

En vous remerciant

 

[crocrocro]

Bonjour Flx 1er,
Quelques questions :
A chaque import, le nombre, la taille des fichiers à importer est-elle à peu près la même ?
Le "un certain temps" , moins d'1 minute, plusieurs minutes, plus d'1 heure ?
Moins d'une minute, un simple "veuillez patienter quelques instants au lancement du traitement" me parait suffisant.
Dans le cas d'une boucle classique, par exemple de 1 à 100 000, il est facile de mettre au point une barre de progression, par exemple en affichant dans la StatusBar, toutes les 1000 occurrences une barre en % du traitement.
Dans votre cas, ce n'est évidemment pas possible. Une proposition :
- Effectuer une série de tests de durée en fonction de la taille (en octets), du nombre de lignes des fichiers, du nombre de fichiers à importer.
- En tirer une règle qui donne approximativement la durée globale selon les valeurs (taille, nombre de lignes, nombre).
-Utiliser Application.OnTime pour déclencher l'affichage de la barre de progression en fonction du % de la durée totale prévue, par exemple pour une durée totale estimée = 10 minutes, chaque 6" si vous souhaitez faire actualiser à chaque 1 % du traitement.

En pj, un fichier avec quelques exemples d'utilisation de la barre de progression dans la barre de Statut. A adapter bien sûr ...

 

[crocrocro]

Bonjour le fil,
Le code correspondant à la proposition de mon post précédent

Option Explicit
Public TempsPlannif
Public PremiereFois As Boolean
Sub LancerTempsRestantPlanifStatusBar()
Dim i As Long, ifin As Long

    PremiereFois = True
    TempsRestantPlanifStatusBar
    ' Boucle bidon juste pour simuler le traitement (sans boucle)
    ifin = 20000
    For i = 1 To ifin
        Range("b1") = i
        Range("b2") = ifin - i
        DoEvents ' pour afficher
    Next i
    MsgBox "boucle fini"
    On Error Resume Next
    Application.OnTime TempsPlannif, Procedure:="TempsRestantPlanifStatusBar", Schedule:=False
    Application.DisplayStatusBar = False
End Sub
Sub TempsRestantPlanifStatusBar()
Dim NomTraitement As String
Dim Tps As Double
Dim TpsRest As Date, Duree As Long
Dim TpsRestAffiche As String
Static T0 As Double
    'Texte dans la StatusBar

    Duree = 20 'en secondes (doit correspondre en gros à la durée du traitement estimé)

    Application.DisplayStatusBar = True

    If PremiereFois Then
        T0 = Timer
    End If
   'temps restant
    Tps = T0 + Duree - Timer
    If Tps > 0 Then
        'traitement non fini
        NomTraitement = " Traitement en cours, veuillez patienter ...  "
        TpsRest = TimeSerial(0, 0, Tps)
        TpsRestAffiche = "(Temps restant : " & Format(TpsRest, "hh\hmm\m\nss\s") & ")"
    Else
        'traitement terminé (normalement)
        NomTraitement = " Le Traitement devrait être terminé ...  "
        TpsRest = TimeSerial(0, 0, -Tps)
        TpsRestAffiche = "(Temps dépassé depuis : " & Format(TpsRest, "hh\hmm\m\nss\s") & ")"
    End If
    
    Application.StatusBar = NomTraitement & TpsRestAffiche
    PremiereFois = False

    ' ici on raffraichit l'affichage de la SttusBar toutes les 5 secondes
    TempsPlannif = Now + TimeValue("00:00:05")
    Application.OnTime TempsPlannif, "TempsRestantPlanifStatusBar"


End Sub

Ici, pour simuler le traitement, j'ai utilisé une boucle bidon qui affiche en B1 et B2 le nombre d'itérations exécutées et restantes.
Le principe :
il faut connaitre approximativement la durée du traitement pour la renseigner dans le code (variable Duree)
Dans la StatusBar, on va afficher toutes les 5 secondes (dans l'exemple avec TempsPlannif = Now + TimeValue("00:00:05")), un message (à adapter) qui indique le temps restant (par rapport à la durée annoncée) ou le temps dépassé dans le cas où la durée du traitement a été sous-estimée.

 

[Lolote83]

Bonjour à tous.
@crocrocro , ton code est pas mal, mais je crois (ou du moins, je pense) que cela ne correspond pas vraiment à la demande puisque notre ami @Flx1er souhaitait une barre de progression mais suite à une requête Power Query. (Voir titre de la demande)
J'utilise aussi de temps en temps Power Query pour mettre à jour de grosses bases de données et j'ai moi aussi ce problème de temporalité. On ne sais pas à l'avance le temps que la requête prendra pour faire le traitement d’où la complexité. Le fait de figer la durée à 20 sec (comme dans ton exemple) n'est pas réalisable puisque le temps ne peut être connu à l'avance. Et une requete Power Query (même si elle travaille sur une BDD définie) ne laisse entrevoir son temps d'exécution.
Bref, je m'étais inscrit sur ce fil pour avoir moi aussi une solution, mais là, ce n'est pas vraiment ce que l'on cherche (A moins que je me trompe)
Du coup, je continue mes recherches de mon coté aussi
Cordialement
@+ Lolote83

 

[crocrocro]

Bonjour le fil,
Pour Lolote :
Au cours de l'extraction, peut-on connaitre le nombre de données (lignes, enregistrements ...) extraits et à extraire ? Il semble que non puisque c'est une des questions de Flx 1er.
Si oui, j'ai une solution en adaptant le code du fichier que j'ai joint dans mon post 2 (pas le 3).
une option permet de ré-estimer au fur et à mesure le temps restant et donc d'adapter la barre de progression.
N'y a-t-il pas moyen avant de lancer la requête d'extraction, de lancer la même requête mais avec juste un count ? on connaitrait alors le nombre d'enregistrements à extraire.
Peut-on lancer la requête en fixant le nombre d'enregistrements à extraire (un échantillon) ? Ce qui permettrait d'établir une valeur approximative du temps par enregistrements extrait et donc de la durée du traitement. Puis de lancer la requête complète.
Ma suggestion est un peu une usine à gaz... juste pour faire patienter.
Sinon, je ne vois pas comment on pourrait estimer la progression et donc mettre en place une barre, mais probablement quelqu'un aura LA solution.

 

[Flx1er]

Bonjour Flx 1er,
Quelques questions :
A chaque import, le nombre, la taille des fichiers à importer est-elle à peu près la même ?
Le "un certain temps" , moins d'1 minute, plusieurs minutes, plus d'1 heure ?
Moins d'une minute, un simple "veuillez patienter quelques instants au lancement du traitement" me parait suffisant.
Dans le cas d'une boucle classique, par exemple de 1 à 100 000, il est facile de mettre au point une barre de progression, par exemple en affichant dans la StatusBar, toutes les 1000 occurrences une barre en % du traitement.
Dans votre cas, ce n'est évidemment pas possible. Une proposition :
- Effectuer une série de tests de durée en fonction de la taille (en octets), du nombre de lignes des fichiers, du nombre de fichiers à importer.
- En tirer une règle qui donne approximativement la durée globale selon les valeurs (taille, nombre de lignes, nombre).
-Utiliser Application.OnTime pour déclencher l'affichage de la barre de progression en fonction du % de la durée totale prévue, par exemple pour une durée totale estimée = 10 minutes, chaque 6" si vous souhaitez faire actualiser à chaque 1 % du traitement.

En pj, un fichier avec quelques exemples d'utilisation de la barre de progression dans la barre de Statut. A adapter bien sûr ...

Bonjour crocrocro,
Merci de votre aide, il faut que je regarde pour acquitter l'erreur dû au 32 bits
La durée du traitement prend environ 20 minutes
Bonne journée

 

[alexga78]

Bonjour Flx1er, le forum,
Importer plusieurs milliers de lignes même sur plusieurs fichiers ne doit pas prendre autant de temps.
Quelles sont les transformations réalisées ? Si la requête est optimisée plus besoin de barre de progression
Peux tu nous fournir un exemple avec donnée anonymisées ?


Bonne journée

 

[Flx1er]

Bonjour Flx1er, le forum,
Importer plusieurs milliers de lignes même sur plusieurs fichiers ne doit pas prendre autant de temps.
Quelles sont les transformations réalisées ? Si la requête est optimisée plus besoin de barre de progression
Peux tu nous fournir un exemple avec donnée anonymisées ?


Bonne journée

Bonjour alexga78,
Vous transmettre un fichier anonymisé va très compliqué voir impossible. Il y a trop de sources de données extérieures.
Bonne journée,

 

[mapomme]

Bonsoir @Fix1er et bienvenu sur XLD , à tous ,

Si le traitement total est long, ce n'est sans doute pas le comptage préalable des lignes du fichier CSV qui va pénaliser notoirement la durée du traitement (pour un csv de 1 000 000 de lignes cela prend environ 0,6 s). Nommons ntot ce nombre de lignes.

Ensuite si vous parcourez une nouvelle fois le fichier CSV mais cette fois avec les traitements longs, vous connaissez le numéro n de la ligne traitée. Vous calculez donc un pourcentage d'avancement : n/ntot*100 qui vous servira pour la barre de progression.

Un exemple pour déterminer le nombre de ligne du CSV :

Sub CompterLignesCSV()
Dim idxfic#, fichier$, nbrLignes&, x$
   fichier = "C:\toto\1 000 000 lignes.csv"
   On Error GoTo Err001
   idxfic = FreeFile()
   Open fichier For Input As #idxfic
   Do While Not EOF(idxfic): nbrLignes = nbrLignes + 1: Line Input #idxfic, x: Loop
Err001:
   Close #idxfic
   MsgBox Format(nbrLignes, "#,##0 lignes\.")
End Sub

 

[mapomme]

Re,

Pour le fun et pour illustrer mes propos, voici un classeur dont l'utilisateur :

• construit un fichier de taille choisie par lui
• puis lance une simulation de traitement de ce fichier CSV

La durée de la simulation du traitement est variable en fonction de la valeur de la constante lenteurSimu

Dans la barre d'état, on voit l'avancement en pourcentage ainsi qu'une" pseudo" barre de progression. Le traitement est complet quand on est à 100% et quand la barre a rattrapé le point.

nota 1 : Le fichier CVS est construit dans le même dossier que le classeur XLS joint.
nota 2 : La fréquence d'actualisation de d'affichage dans la barre d'état peut aussi être ralenti ou accélérer en modifiant la valeur du diviseur du modulo "If n Mod 1000 = 0 Then..."

 

[crocrocro]

Bonjour le fil,
Pour mapomme :
J'ai crû comprendre, d'après le fichier fourni par Flx 1er, qu'il n'y avait pas de boucle et donc pas possibilité d'afficher une barre de progression toutes les n itérations (d'une boucle) comme tu le proposes. D'où ma proposition au post 3.
Mais, encore une fois, je n'ai peut-être pas bien compris
Flx 1er pourra nous en dire plus...