Power Query Simplifier/Universaliser l'import multiple de fichiers CSV

[Staple1600]

Bonsoir le forum

Je cherche à simplifier l'import de CSV à partir d'un dossier donné.

Quand on utilise l'assistant de PowerQuery, il y a de nombreuses étapes (voir ci-dessous)

let
    Source = Folder.Files("C:\Users\STAPLE\Documents\PQ_CSV"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"ITEM_1", Int64.Type}, {"ITEM_2", Int64.Type}, {"ITEM_3", Int64.Type}, {"ITEM_4", Int64.Type}, {"ITEM_5", Int64.Type}, {"ITEM_6", Int64.Type}, {"ITEM_7", Int64.Type}, {"ITEM_8", Int64.Type}})
in
    #"Type modifié"

On peut faire plus simple et générique que Microsoft, non ?

Quelqu'un aurait-il un code M plus synthétique et universel(*) pour faire cette opération ?
(*) notamment ici #"Type modifié" : ne pas avoir à lister tous les noms des colonnes

PS: j'ai regardé sur le forum les discussions avec le préfixe PowerQuery, j'ai regardé quelques vidéos sur YT (mais c'est pas pratique) et enfin je n'ai pas solliciter ChatGPT, préférant mes frères humains (notamment ceux d'XLD) et aussi un peu en souvenir de Sarah Connor.

NB: Il me semble que les étapes de Microsoft sont peu ou prou ce que montrent cette vidéo, non ?
https://www.youtube.com/watch?app=desktop&v=IsLB3XR9eno

 

[Cousinhub]

Re-,

@alexga78
Merci pour les explications sur la fonction magique de PQ
PS: Tu es un utilisateur chevronné de PQ, non ?

Je te confirme, tu peux même le mettre à ma place, je regarde toutes ses réponses (j'ai moi aussi des neurones ayant vécus bien plus longtemps au XXème qu'au XXIème....)

 

[alexga78]

Re,
Pour être honnête, j’utilise ce forum depuis quelques mois pour apprendre PQ.
Mes neurones du XXième siècle commencent à comprendre cette logique, je n’ai aucun doute pour les tiens étant donné ton niveau en vba.
Apprendre est une bénédiction
Flatté si je suis présent dans ton carnet à spirales.

Bon fin de we, c’est l’heure de l’apéro !

Edit: Merci Cousinhub ! On apprend tous de chacun des participants.
Au plaisir de vous recroiser sur un fil.

 

[Cousinhub]

Bon fin de we, c’est l’heure de l’apéro !

Je ne peux que plussoyer... (et je pense que mon voisin rennais ne va pas me contredire....)

 

[JFL_XLD]

Bonsoir à tous !

Pour le "fun", une approche légèrement différente quant à la gestion du dynamisme des champs :

let
    Source = Folder.Files(Excel.CurrentWorkbook(){[Name="CheminDossier"]}[Content]{0}[Column1]),
    Filtre = Table.SelectRows(Source, each [Extension]=".csv"),
    Transform = Table.ReplaceValue(Filtre,each [Content],each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=";"]), [PromoteAllScalars=true]),Replacer.ReplaceValue,{"Content"})[[Name],[Content]],
    Expand = Table.ExpandTableColumn(Transform, "Content", Table.ColumnNames(Transform[Content]{0}))
in
    Expand

L'étape "Filtre" est facultative. A voir en fonction du contenu du dossier.

 

[Cousinhub]

Re-,
Hello JF,
Juste un petit bémol, si les csv n'ont pas tous les mêmes en-têtes (dans mes essais, j'ai modifié le fichier "Test01.csv", en modifiant le titre ITEM_8 par ITEM_9)
Et avec ton code, on a bien de ITEM_1 à ITEM_7, puis juste ITEM_9 (donc du premier csv), sans ITEM_8 des autres
Bonne soirée

 

[Staple1600]

Bonsoir @JFL_XLD

Merci pour cette autre syntaxe
Pourquoi tu passes par Table.ReplaceValue ?

Parce que j'ai mixé ta syntaxe avec celle-ci et c'est OK aussi

let
F_PATH= Excel.CurrentWorkbook(){[Name="CSV_PATH"]}[Content]{0}[Column1],
Colonnes = Table.SelectColumns(Table.AddColumn(Folder.Files(F_PATH), "Custom", each Table.PromoteHeaders(Csv.Document([Content], [Delimiter=";"]))),{"Name","Custom"}),
Final=Table.ExpandTableColumn(Colonnes, "Custom", Table.ColumnNames(Colonnes[Custom]{0}))
in
Final

On a donc désormais moult syntaxes pour le Table.ExpandTableColumn
Chouette

@Cousinhub
@alexga78 ne peut pas prendre ta place dans mon carnet
Il est à la page des A
et toi à la page des C
Et il y a encore de la place dans le dit car je dirais qu'à vue de cellules, il n'y pas plus de 20 afficionados de PQ sur XLD
(Si je compte bien, il n'y a que trois pages de messages sur le forum avec le préfixe PowerQuery)

Spoiler: aparté pour expliquer le pourquoi du besoin de passer par des csv

Le but initial était de compiler 12 *.xlsx mensuels généré par un logiciel (j'ignore lequel)
Ces fichiers sont de structure identique et avec toujours le même nombre de colonnes
Quand je les compile avec PQ et je fais un TCD avec le tableau issu de la requête
Il apparait des lignes fantômes dans le TCD
Exemple
(vides) = NNNN ou NNNN est le total d'une colonne du premier fichier importé
Par contre, si je transforme les *.xlsx en *.csv et que c'est ces *csv que j'utilise avec mon TCD pas de phénomène bizarre et mon TCD est ok
Si j'arrive à reproduire le phénomène avec un fichier *.xlsx anonymisé, je créerai un post à ce sujet
Mais je pense que le logiciel qui doit créé des *.xlsx mal fagotés

 

[Staple1600]

@Cousinhub
Dans ma réalité, dans mon dossier, dans ma Bretagne ou vice et versa, les fichiers ont toujours la même structure.
(Pourvu que ca dure )
Je cours aussi un risque en utilisant la syntaxe de JFL_XLD à l'étape Final ?
(cf le code M du message#36)

 

[Cousinhub]

Re-,

Je cours aussi un risque en utilisant la syntaxe de JFL_XLD à l'étape Final ?

Tu ne cours aucun risque, tu as eu affaire à 2 maestro*, le simple "bémol" que je remonte, c'est au cas que...
Mais comme tu le précises :

Dans ma réalité, dans mon dossier, dans ma Bretagne ou vice et versa, les fichiers ont toujours la même structure.
(Pourvu que ca dure )

Je ne vais pas en ajouter sur la dernière phrase....

* Edit, je ne parle surtout pas de moi, mais des 2 autres "répondeurs", qui, à mon humble avis, font partie du gratin "PQ"iste

 

[JFL_XLD]

Bonsoir à tous de nouveau !

@Cousinhub : Hello !
J'ai construit ma requête avec l'idée d'une stabilité dans la structure des fichiers CSV. Si tel n'était pas le cas, le remède est simple :

= Table.ExpandTableColumn(Transform, "Content", Table.ColumnNames(Table.Combine(Transform[Content])))

"Maestro" ? Fichtre.....
Ne jamais oublier que si, parfois, nous sommes meilleurs sachant que certains, nous sommes aussi petits sachant relativement à d'autres. Bref...cultivons l'humilité !

@Staple1600 :

Pourquoi tu passes par Table.ReplaceValue ?

J'évite ainsi le traitement lié à l'ajout d'une colonne supplémentaire (Table.AddColumn).

 

[Staple1600]

Re

@JFL_XLD
C'est vrai qu'il vaut mieux laisser les colonnes à Monsieur BUREN que d'en flanquer dans PQ
Désolé pour cet humour de CM2 (sans groupe de niveaux)

@Cousinhub
Il existe un droit à l'oubli numérique
Il n'existe pas de droit à l'oubli dans le carnet à spirales à Staple
Désolé

Je suis ravi, j'ai plein de PQ dans mon Excel
(j'en suis à 9 requêtes)

PS: je reste quand même étonné de la "lourdeur" utilisé par l'assistant de PQ
(cf image dans mon 1er message)
comparé aux syntaxes fluides proposées par alexga78 et JFL_XLD

 

[job75]

Bonsoir à tous,

Ce fil est dédié à Power Query mais ne pas oublier que VBA est toujours là :

Sub Import_CSV()
Dim t#, chemin$, fichier$, nf&, x%, fich$, texte$, a$(), n&, b$(), i&
t = Timer
chemin = ThisWorkbook.Path & "\"
fichier = Dir(chemin & "*.csv") '1er fichier du dossier
While fichier <> ""
    nf = nf + 1
    x = FreeFile
    fich = Left(fichier, Len(fichier) - 4)
    Open chemin & fichier For Input As #x 'ouverture en lecture séquentielle
    Line Input #x, texte
    If n = 0 Then ReDim Preserve a(n): a(n) = ";" & texte: n = n + 1
    While Not EOF(x)
        ReDim Preserve a(n)
        Line Input #x, texte
        a(n) = fich & ";" & texte
        n = n + 1
    Wend
    Close #x 'fermeture
    fichier = Dir 'fichier suivant
Wend
Application.ScreenUpdating = False
Cells.ClearContents 'RAZ
If n Then
    ReDim b(n - 1, 0)
    For i = 0 To n - 1
        b(i, 0) = a(i) 'transposition
    Next i
    With [A1].Resize(n)
        .Value = b
        .TextToColumns .Cells, xlDelimited, Other:=True, OtherChar:=";" 'commande Convertir
    End With
End If
Application.ScreenUpdating = True
MsgBox nf & " fichier" & IIf(n > 1, "s", "") & " importé" & IIf(n > 1, "s", "") & " en " & Format(Timer - t, "0.00 \sec")
End Sub

Ci-joint les fichier zippés, à télécharger dans le même dossier.

Il sera intéressant de comparer les durées avec Power Query sur de gros et nombreux fichiers.

A+

 

[job75]

Pour tester j'ai utilisé 4 tableaux sources 8 x 200 000 remplis avec =ALEA()

La macro s'exécute chez moi en 11,7 secondes.

 

[Staple1600]

Bonsoir job75

Merci pour ta proposition.

Mais le but de ce fil n'est pas de comparer PQ vs VBA mais de simplifier la syntaxe d'un code M.

Simplification désormais obtenue

Pour ce qui me concerne, j'oublie peu à peu VBA (enfin j'essaie) pour appréhender PQ.

Durée, gros et nombreux fichiers ne sont pas un critère pour moi.

Ce sera toujours 12 fichiers CSV par an, avec la même structure, et un nombre de lignes < à 10 000 lignes.

NB: L'avantage de PQ c'est que je peux l'utiliser au boulot.
Il arrivera un jour où notre DSI nous fournira des PC vraiment bridés.
(Pas VBScript, pas de powershell, pas de VBA dans Office)
J'utilise déjà un Windows 10 bridé (pas de session admin), navigateur bridé, panneau de Configuration bridé.

Il paraitrait aussi que Microsoft veut abandonner le VBA dans Office

 

[job75]

Il paraitrait aussi que Microsoft veut abandonner le VBA dans Office

On le dit depuis longtemps mais à mon avis ce serait une grossière erreur.

Et pour quelles raisons le ferait-il ? VBA est un très bel outil.

Bonne nuit.

 

[Staple1600]

Re,

VBA est un bel outil, mais pas pour tous (Windows d'abord)
et PQ commence lui aussi à parler VBA (un peu avec l'enregistreur de macros)
(Ce qui me permets de croiser les deux univers)

Donc si jamais VBA devait disparaitre (au profit de .NET, Office Script), ma méthadone sera PowerQuery

Sur ce bonne nuit, également