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

Staple1600 · 12 Avril 2024

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)

PowerQuery:

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

alexga78 · 13 Avril 2024

Staple1600 à dit:
Re,

@alexga78
j'utilise aussi Office 365

Les fichiers de test CSV sont issus d'Excel (Enregistrer sous -> CSV, séparateur point virgule)

Tu as essayé avec les fichiers contenu dans le zip du message#2 ?

Tu peux mettre ton code M complet, stp avec le let .... in
merci

Sinon tu vois comment simplifier le code "anglophone" ?

Re,
les fichiers testés sont ceux du zip.

un autre essai pour le nom des fichiers

PowerQuery:

let
    Source = Folder.Files("C:\Users\STAPLE\Documents\PQ_CSV"),
    Result = let x =  Table.SelectColumns(Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=";", Encoding=TextEncoding.Windows, QuoteStyle=QuoteStyle.None]))),{"Name","Custom"}) in Table.ExpandTableColumn(x,"Custom", List.Union(Table.TransformColumns(x, {"Custom", each...

Amilo · 14 Avril 2024

@Staple1600 ,

Pour votre 19ème du jour, ajoutez cette étape à la fin ;

VB:

=Table.TransformColumns(Suppression, { "Source.Name",  each Text.From(_)}, Number.From )

Cordialement

Staple1600 · 14 Avril 2024

@Amilo
Merci pour cette dernière ligne
Si j'ai bien compris
Si la colonne est à l'origine en Texte, elle sera en Texte dans Excel
Si la colonne est numérique (date, heure, ou nombre), elle sera date heure ou nombre selon les cas
Ou ici avec cette ligne, la colonne Name est en Texte et toutes les autres en numérique ?

Il restera plus qu'à tester avec les fichiers réels demain au boulot.
(Et j'espère que le code M parlé par Excel 365 l'est aussi par Excel 2019)

Amilo · 14 Avril 2024

@Staple1600 ,
Oui ici, la colonne "Source.Name" est typé en "Any" et le reste en nombre
Mais utilisez plutôt le code ci-dessous pour le type texte :

VB:

Table.TransformColumns(Suppression, {"Source.Name", each _}, Number.From)

Sinon, je ne sais pas trop comment les types de colonnes sont gérés avec un fichier .csv
Idem pour les fichiers .xlsx, je ne suis pas certain, que les types sont toujours préservés après un Table.ExpandColumn !!

Cordialement

Staple1600 · 14 Avril 2024

Re

@Amilo
Après cette lecture, j'ai plus de billes

Data types in Power Query - Power Query

A comprehensive article on the concepts of data types in Power Query, how to define data types for values and the importance of the data types.

learn.microsoft.com

Unstructured sources such as Excel, CSV, and text files, Power Query automatically detects data types by inspecting the values in the table. By default, automatic data type detection is enabled in Power Query for unstructured sources.

Et j'ai vérifié dans mon PQ, les bonnes options sont cochées

C'est donc la syntaxe du code M qui n'est pas bonne ?
Celles qui utilisent Table.ExpandTableColumn
ou ta dernière proposition avec Table.Combine

Dans ce cas, comment faire pour ne pas avoir à faire l'équivalent dans l'assistant de PQ de:
Détecter le type de données


   #"Type modifié"=Table.TransformColumnTypes(Personnalisé2,{{"ITEM_2", Int64.Type}})

et ce pour N colonnes de mes fichiers CSV ?

Amilo · 15 Avril 2024

@Staple1600 ,

Staple1600 à dit:
Et j'ai vérifié dans mon PQ, les bonnes options sont cochées

Je suppose dans ce cas que vous n'avez pas opté pour la détection automatique des types de données dans les options.
Il est recommandé de désactiver cette option pour que l'assistant PQ ne génère pas inutilement plusieurs fois des étapes de typage.
À chaque typage, les noms de colonnes apparaîtront en dur et donc plus de risque et de boulot pour corriger manuellement ces étapes en cas de modification dans la source de donnée.
Sinon, vous pouvez créer une fonction avec toutes les modifications opérées sur un seul fichier et ensuite appeler celle-ci pour l'appliquer à tous les N fichiers.

P. S : pour info, je ne pourrai pas répondre pendant ma journée de travail

Cordialement

Staple1600 · 15 Avril 2024

Bonsoir le fil

@Amilo

P. S : pour info, je ne pourrai pas répondre pendant ma journée de travail

Tout comme moi

J'ai testé sur les CSV originaux
Pas de soucis avec les types de données, PowerQuery fait le boulot avec
[Delimiter=";", Encoding=Encoding=1252, QuoteStyle=QuoteStyle.None]

Problème du soir
Il y a 29 colonnes dans le CSV, alors je cherche à n'en sélectionner que certaines
(mais pas par leur noms mais avec leur position ou leur N° d'index)
Voila ce que j'ai testé (sur un fichier CSV)

PowerQuery:

let
    Source = Csv.Document(File.Contents("C:\Users\STAPLE\Documents\PQ_CSV\Test01.csv"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    Promu = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
// ici je définis mes N° de colonnes
    NumCols={0,2,6},
Final= Table.SelectColumns(Promu,List.Transform(NumCols, each Table.ColumnNames(Promu){_}))
in
Final

Je ne sais pas si c'est un choix judicieux

Mais avec 29 colonnes au départ, dès que je manipule PQ à la souris, l'assistant inscrit le nom de tous les colonnes dans le code M.

Amilo · 15 Avril 2024

Bonjour à tous, @Staple1600 ,

VB:

Je ne sais pas si c'est un choix judicieux

J'aurais quelques questions concernant les fichiers csv :
- Ont-ils tous le même nombre de colonnes ?
- Les mêmes noms ?
- Même ordre et structure identique ?
- Les titres de colonnes sont-ils amenés à changer ?
- Si oui, de la même manière pour l'ensemble des fichiers csv ?
- Pourquoi vouloir passer par le numéro de colonnes ou un Index même si cela est possible ?
On peut très bien gérer les modifications de nom de colonnes dans Power query

Sinon, une autre approche pour la sélection des colonnes en attendant.
"ListeColonnes" correspond à une liste dans une seule colonne et indiquées sur une feuille Excel avec les noms à filtrer

Code:

let
    Source_Colonnes = Excel.CurrentWorkbook(){[Name="ListeColonnes"]}[Content],
    Source_Data = Excel.CurrentWorkbook(){[Name="Tableau"]}[Content],
    #"Colonnes Sélectionnées" = Table.SelectColumns(Source_Data,Table.Column(Source_Colonnes,"Colonnes à sélectionner"))
in
    #"Colonnes Sélectionnées"

Cordialement

Cousinhub · 15 Avril 2024

Bonjour,
Je plussoye à la solution d'Amilo, c'est ainsi que je procède, lors d'import de fichiers pouvant comporter plus de 150 colonnes, et dont seules une vingtaine m'intéresse.

Staple1600 · 15 Avril 2024

Re, Bonsoir @Cousinhub

@Amilo
Les fichiers CSV sont tous identiques structurellement
(export mensuel d'un logiciel X)
Sur les 29 colonnes, il faut que je vois avec l'utilisateur-trice final lesquelles sont à exploiter dans Excel.

Va pour la même optique que
F_PATH= Excel.CurrentWorkbook(){[Name="CSV_PATH"]}[Content]{0}[Column1],

Mais précédemment, @Cousihub m'indiquait qu'il fallait ajouter ce que j'ai mis en bleu
(cf message#18)

Ici ce n'est pas utile ?

Je teste de ce pas

Staple1600 · 15 Avril 2024

Re

Ca coince (ou plutôt je fais coincer PQ)

PowerQuery:

let
  Source = Csv.Document(File.Contents("C:\Users\STAPLE\Documents\PQ_CSV\Test01.csv"),[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None]),
  Source_Colonnes = Excel.CurrentWorkbook(){[Name="ListeColonnes"]}[Content],
  Source_Data = Excel.CurrentWorkbook(){[Name="TestB"]}[Content],
// ici erreur
  #"Colonnes Sélectionnées" = Table.SelectColumns(Source_Data,Table.Column(Source_Colonnes,"Colonnes Sélectionnées"))
in
  #"Colonnes Sélectionnées"

Expression.Error : Désolé... Nous n'avons pas trouvé la colonne « Colonnes Sélectionnées » de la table.
Détails :
Colonnes Sélectionnées

Moi, non plus je n'ai pas trouvé cette colonne

Amilo · 15 Avril 2024

Re,
Bonsoir @Cousinhub et merci pour votre message,

@Staple1600 , "Colonnes sélectionnées" correspond au titre de la colonne de votre liste.
Quel titre avez-vous indiqué ?

Sinon, merci pour vos réponses,
Une autre solution consiste à indiquer une table "Cible" sans données " mais uniquement avec les colonnes à garder puis de fusionner l'ensemble des 29 colonnes avec la table des colonnes retenues
PQ retiendra uniquement les colonnes de la table "Cible"

Pour l'instant essayez avec une liste de noms et éventuellement à tester cette 2ème méthode

Cordialement

Staple1600 · 15 Avril 2024

Re

@Amilo, @Cousinhub et amis PQristes
Pour le moment, je teste sur un seul fichier CSV
J'ai créé une liste de noms sur une feuille du classeur
Mais j'ai testé en restant dans l'éditeur avancé

Ce que je cherchais à faire, c'est de supprimer (ou sélectionner) avec une ligne de code M les colonnes avant de faire Fermer et Charger
donc un truc du genre
let
NomColonnes=On definit les colonnes à importer
Source
....

Amilo · 15 Avril 2024

@Staple1600 ,
Je viens de tester ce code avec l'ensemble des fichiers,
Pouvez-vous essayer de votre côté ?

VB:

let
    Source_Colonnes = Excel.CurrentWorkbook(){[Name="ListColonnes"]}[Content],
    Source = Folder.Files("C:\Users\STAPLE\Documents\PQ_CSV"),
    Personnalisé1 = Source[[Folder Path],[Name]],
    #"Personnalisée ajoutée" = Table.AddColumn(Source, "Personnalisé", each  Table.PromoteHeaders(Csv.Document(File.Contents([Folder Path] & [Name]), null, ";", 1 , 1252))),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Personnalisée ajoutée", "Personnalisé.1", each Table.AddColumn([Personnalisé], "Nom fichier", (x)=> [Name])),
    #"Personnalisée ajoutée2" = Table.AddColumn(#"Personnalisée ajoutée1", "Personnalisé.2", each Table.SelectColumns([Personnalisé.1], Table.Column(Source_Colonnes, "Liste Colonnes"))),
    Personnalisé2 = Table.Combine(#"Personnalisée ajoutée2"[Personnalisé.2]),
Personnalisé3 = Table.TransformColumns(Personnalisé2, {"Nom fichier", each _}, Number.From)

in
    Personnalisé2

Cordialement

Staple1600 · 15 Avril 2024

Re

@Amilo
Déjà merci pour les tests
Moi, j'avais mis les noms dans le gestionnaires de noms
(pas dans un tableau structuré)

Je teste et je vous redis

Amilo · 15 Avril 2024

Re,
Il faudrait présenter votre liste de noms comme sur ma capture
Cordialement

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

Staple1600

XLDnaute Barbatruc

alexga78

Amilo

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

Amilo

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

Data types in Power Query - Power Query

Amilo

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

Amilo

XLDnaute Accro

Cousinhub

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Amilo

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

Amilo

XLDnaute Accro

Pièces jointes

Staple1600

XLDnaute Barbatruc

Amilo

XLDnaute Accro

Discussions similaires