Power Query Import de valeurs registre

merinos

merinos

XLDnaute Accro
Bonjour,

je dois importer /- un million de lignes de données...Mais dans quelques mois se pourait être 10 millions de lignes. Donc la reponse doit être en PowerQuery.

Il s'agit de 4 registres pour une serie de compteurs. Un registre ne peut que monter quand de l'energie passe dedans.
Dans chaque fichier il y a plusieurs compteurs. (dans l'exemple je n'en ai gardé que quelques uns).

Mes données sont dans 400 fichiers qui ont tous la même structure.. donc en soi pas de probleme.

Je vous mets le resultat de l'import pour l'un d'entre eux... Traficoté de façon a ce que les valeurs soient bien visibles.

Ce que je cherche a obtenir c'est la quantité d'énergie (pas les valeurs de registres)

Je m'arrache un peu les cheveux depuis hier.

Merçi de votre aide...
1699460711431.png


Merinos
 

Pièces jointes

  • analyse Sibe.xlsx
    78.5 KB · Affichages: 6
Solution
mromain
Bonsoir merinos, job, le forum,

Ci-dessous une autre requête basée sur le tableau Table1 du fichier que tu as fourni en exemple au post #1.
La requête fait une jointure sur elle-même pour récupérer la ligne précédente d’un même compte.
Pas sûr que ce soit plus performant que ta proposition au post #2 mais on sait jamais…
PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TriParCptEtDate = Table.Sort(Source,{{"CPT ID", Order.Ascending}, {"date", Order.Ascending}}),
    AjoutColonneIndex0 = Table.AddIndexColumn(TriParCptEtDate, "Index0", 0, 1, Int64.Type),
    AjoutColonneIndex1 = Table.AddIndexColumn(AjoutColonneIndex0, "Index1", 1, 1, Int64.Type),
    FusionRequête = Table.NestedJoin(AjoutColonneIndex1, {"CPT...
Trier par date Trier par votes
merinos

merinos

XLDnaute Accro
re bonjour a tous,

J'ai trouvé une methode qui permet de prendre sur une ligne les informations de ce qui est sur la ligne précédante...

Mais pas sûr que ce soit la méthode a employer.

Merinos
 

Pièces jointes

  • PQ lien ligne precedante.xlsx
    32.2 KB · Affichages: 4
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Bonsoir merinos,

Au vu de l'image fournie il faut en effet remplacer chaque valeur de registre par la différence entre sa valeur et celle de la ligne précédente.

Avec 10 000 000 de lignes on peut stocker les données dans un fichier Texte.

A traiter par PQ ou par lecture/écriture séquentiels avec une macro.

A+
 
Vote positif 0 Vote négatif
mromain

mromain

XLDnaute Barbatruc
Bonsoir merinos, job, le forum,

Ci-dessous une autre requête basée sur le tableau Table1 du fichier que tu as fourni en exemple au post #1.
La requête fait une jointure sur elle-même pour récupérer la ligne précédente d’un même compte.
Pas sûr que ce soit plus performant que ta proposition au post #2 mais on sait jamais…
PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TriParCptEtDate = Table.Sort(Source,{{"CPT ID", Order.Ascending}, {"date", Order.Ascending}}),
    AjoutColonneIndex0 = Table.AddIndexColumn(TriParCptEtDate, "Index0", 0, 1, Int64.Type),
    AjoutColonneIndex1 = Table.AddIndexColumn(AjoutColonneIndex0, "Index1", 1, 1, Int64.Type),
    FusionRequête = Table.NestedJoin(AjoutColonneIndex1, {"CPT ID", "Index0"}, AjoutColonneIndex1, {"CPT ID", "Index1"}, "DonnéesPrécédentes", JoinKind.LeftOuter),
    RecordDonnéesPrécédentes = Table.TransformColumns(FusionRequête,{{"DonnéesPrécédentes", Table.First, type record}}),
    ColonneAjoutéeRegistre1 = Table.AddColumn(RecordDonnéesPrécédentes, "Registre1", each [Registre 1] - (if [DonnéesPrécédentes] = null then [Registre 1] else [DonnéesPrécédentes][Registre 1])),
    ColonneAjoutéeRegistre2 = Table.AddColumn(ColonneAjoutéeRegistre1, "Registre2", each [Registre 2] - (if [DonnéesPrécédentes] = null then [Registre 2] else [DonnéesPrécédentes][Registre 2])),
    ColonneAjoutéeRegistre3 = Table.AddColumn(ColonneAjoutéeRegistre2, "Registre3", each [Registre 3] - (if [DonnéesPrécédentes] = null then [Registre 3] else [DonnéesPrécédentes][Registre 3])),
    ColonneAjoutéeRegistre4 = Table.AddColumn(ColonneAjoutéeRegistre3, "Registre4", each [Registre 4] - (if [DonnéesPrécédentes] = null then [Registre 4] else [DonnéesPrécédentes][Registre 4])),
    ColonnesSélectionnées = Table.SelectColumns(ColonneAjoutéeRegistre4,{"CPT ID", "date", "Registre1", "Registre2", "Registre3", "Registre4"})
in
    ColonnesSélectionnées

A+
 
Vote positif 0 Vote négatif
Solution
merinos

merinos

XLDnaute Accro
@mromain

Tout le truc est dans la ligne:
Code: 
RecordDonnéesPrécédentes = Table.TransformColumns(ExpandedCustom,{{"D2", Table.First, type record}}),

Je me demande s'il n'est pas plus rapide de selectionner une ligne plutôt que de faire une jointure.
Cela passe biensûr par un groupement d'abord.

Je vais tester.

Merci
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Pour ceux qui préfèrent le VBA voyez cette macro :
VB: 
Sub Fichiers_Textes()
Dim t#, chemin$, source$, destination$, ncol%, x1%, x2%, n%, i&, texte$, s2, memo, j%, s1, nlig&
t = Timer
chemin = ThisWorkbook.Path & "\"
source = Dir(chemin & "*.txt")
destination = "Fichier destination.txt"
ncol = 6
x1 = FreeFile
Open chemin & destination For Output As #x1
While source <> ""
    If source <> destination Then
        x2 = FreeFile
        Open chemin & source For Input As #x2
        n = n + 1
        i = 0
        While Not EOF(x2)
            i = i + 1
            Line Input #x2, texte
            s2 = Split(texte, vbTab)
            memo = s2
            If i > 1 Then
                If s2(0) = s1(0) Then 'si le texte en 1ère colonne ne change pas
                    For j = 2 To ncol - 1
                        s2(j) = s2(j) - s1(j)
                    Next j
                    texte = Join(s2, vbTab)
                End If
            End If
            s1 = memo
            Print #x1, texte 'transfert
            nlig = nlig + 1
        Wend
        Close #x2 'ferme le fichier source
    End If
    source = Dir
Wend
Close #x1 'ferme le fichier destination
MsgBox n & " fichier(s) et " & nlig & " lignes traités en " & Format(Timer - t, "0.00 \sec")
End Sub
Les 400 fichiers sources sont supposés être tous des fichiers Textes.

Edit : en fait il ne faut pas modifier les valeurs des registres quand le texte en 1ère colonne change.
 

Pièces jointes

  • Fichier source.txt
    50.5 KB · Affichages: 1
  • Pilote(1).xlsm
    18.9 KB · Affichages: 0
Dernière édition:
Vote positif 0 Vote négatif
merinos

merinos

XLDnaute Accro
Bonjour a Tous
(@chris @mromain )

La Methode "Merino2" est un essai par ligne... Pour l'instant elle semble plus rapide...
Demain je lance cela sur les gros volumes. Je vous tiens au jus.

@job75 : Merci de la methode. Cela sera peut-être utile si je dois reformater les fichiers d'origine.
Malheureusement je dois faire des analyses sur les 10.000.000 de lignes... donc les avoir en mémoire en une fois. Biensur on peut en profiter pour eliminer les lignes ou les 4 registres sont nulls...



Merci
 

Pièces jointes

  • analyse Sibe.xlsx
    95 KB · Affichages: 1
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Bonjour merinos, mromain, chris, le forum,

En fait il ne faut pas modifier les valeurs des registres quand le texte en 1ère colonne change, j'ai donc corrigé mon post #7.
merinos à dit:
Biensur on peut en profiter pour eliminer les lignes ou les 4 registres sont nulls...
Cliquez pour agrandir...
Pour cela voyez la macro de ce fichier (2) et la variable retenu.

A+
 

Pièces jointes

  • Fichier source.txt
    50.5 KB · Affichages: 4
  • Pilote(2).xlsm
    19.5 KB · Affichages: 3
Vote positif 0 Vote négatif
mromain

mromain

XLDnaute Barbatruc
Bonjour merinos, job, chris, le forum,


Il est possible de se passer de l'étape Table.TransformColumns(ExpandedCustom,{{"D2", Table.First, type record}}) avec la requête suivante :
PowerQuery: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TriParCptEtDate = Table.Sort(Source,{{"CPT ID", Order.Ascending}, {"date", Order.Ascending}}),
    AjoutColonneIndex0 = Table.AddIndexColumn(TriParCptEtDate, "Index0", 0, 1, Int64.Type),
    AjoutColonneIndex1 = Table.AddIndexColumn(AjoutColonneIndex0, "Index1", 1, 1, Int64.Type),
    FusionRequête = Table.NestedJoin(AjoutColonneIndex1, {"CPT ID", "Index0"}, AjoutColonneIndex1, {"CPT ID", "Index1"}, "DonnéesPrécédentes", JoinKind.LeftOuter),
    ColonneAjoutéeRegistre1 = Table.AddColumn(FusionRequête, "Registre1", each try [Registre 1] - [DonnéesPrécédentes][Registre 1]{0} otherwise 0),
    ColonneAjoutéeRegistre2 = Table.AddColumn(ColonneAjoutéeRegistre1, "Registre2", each try [Registre 2] - [DonnéesPrécédentes][Registre 2]{0} otherwise 0),
    ColonneAjoutéeRegistre3 = Table.AddColumn(ColonneAjoutéeRegistre2, "Registre3", each try [Registre 3] - [DonnéesPrécédentes][Registre 3]{0} otherwise 0),
    ColonneAjoutéeRegistre4 = Table.AddColumn(ColonneAjoutéeRegistre3, "Registre4", each try [Registre 4] - [DonnéesPrécédentes][Registre 4]{0} otherwise 0),
    ColonnesSélectionnées = Table.SelectColumns(ColonneAjoutéeRegistre4,{"CPT ID", "date", "Registre1", "Registre2", "Registre3", "Registre4"})
in
    ColonnesSélectionnées

Vu que tu vas travailler sur un gros volume de données, peut-être que ça jouera...

Bonne journée

A+
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
merinos à dit:
Transformation des fichiers en VBA puis import des données propres par Query
Cliquez pour agrandir...
Puisqu'on utilise VBA pourquoi ne pas l'utiliser aussi pour importer les données ?

J'ai testé mon fichier (2) en répétant 20 000 fois le traitement séquentiel des 673 lignes :

- nombre de lignes traitées 13 460 000

- nombre de lignes retenues 2 480 000

- durée 81 secondes chez moi sur Win 11 Excel 2019.

Power Query va peut-être plus vite, de combien ?
 
Vote positif 0 Vote négatif
merinos

merinos

XLDnaute Accro
power query permet d'employer power Pivot... et impossible de réaliser les mêmes choses autrement.

les données sont dans le document, mais pas dans des cellules.

calculer les croissances a 12 mois de décalage, super simple.
Créer des pivots sur des tables liées, impossible hors PP...
 
Vote positif 0 Vote négatif

Discussions similaires

S
  • Question
XL 2016 Comment remplir mon tableau quotidiennement par liaison et conserver les valeurs de la veille?
Réponses
8
Affichages
772
xUpsilon
xUpsilon

Membres actuellement en ligne

14 membres et 130 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 207
Messages
2 086 237
Membres
103 162
dernier inscrit
fcfg