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

Staple1600

XLDnaute Barbatruc
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)
PQ_CSV_Capture.PNG


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
 
Solution
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...

Staple1600

XLDnaute Barbatruc
@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

XLDnaute Accro
@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
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re

@Amilo
Après cette lecture, j'ai plus de billes
  • 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

XLDnaute Accro
@Staple1600 ,
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
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
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

XLDnaute Accro
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
 

Staple1600

XLDnaute Barbatruc
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

XLDnaute Barbatruc
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

XLDnaute Accro
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

XLDnaute Barbatruc
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é
NomsColonnes.PNG

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

XLDnaute Accro
@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
 

Pièces jointes

  • Image.jpg
    Image.jpg
    160.3 KB · Affichages: 8

Membres actuellement en ligne

Statistiques des forums

Discussions
313 322
Messages
2 097 141
Membres
106 850
dernier inscrit
benbeckman