oguruma
XLDnaute Occasionnel
Ce poste fait suite à celui-ci : Combiner des .csv
Ici nous allons combiner des fichiers Excel. Le principe est quasi identique à celui concernant des fichiers .csv.
L'assistant est aussi bien pratique pour les 1ers galops PowerQuery sauf qu'il en met un peu trop à mon gout
.
L'environnement de travail et de paramétrage
Environnement PowerQuery
Je ferai abstraction sur l'assistant. Pour autant son code source est dans le fichier joint.
Import unitaire d'un fichier Excel
On remarque que le code est assez simple et facile à reproduire avec un autre fichier. Pour autant cela peut se transformer en fonction paramétrable.
Fonction fnGetEXCEL
Utilisation de la fonction fnGetEXCEL
On invoque soit une table soit un onglet. La distinction s'effectue via le paramètre Kind et Item le nom de l'objet que l'on souhaite récupérer :
- Source = fnGetEXCEL ("Ventes_2020.xlsx","TB_VENTES", "Table"), (pour une table)
- Source = fnGetEXCEL ("Ventes_2020.xlsx","Query Data", "Sheet"), (pour un onglet)
Combinaison manuelle des fichiers Excel - via une table
Combinaison manuelle des fichiers Excel - via un onglet
Combinaison manuelle en précisant le dossier
Si on souhaite obtenir un fichier particulier du dossier
Appel à une fonction fnGetFolderExcelFiles personnalisée pour importer un dossier Excel
Code de la fonction fnCombineExcelFiles
Fonction personnalisée mixte capable soit d'importer un dossier .csv soit un dossier Excel
Fonction fnCombineCSVExcelByList
De la manière qu'un fichier CSV cette fonction permet d'importer des fichiers au cas par cas qu'il soit CSV ou qu'il soit EXCEL
Fonction fnCombineExcelByList d'importation spécifique uniquement pour les fichiers Excel
Et appel de la fonction fnCombineExcelByList
Ici nous allons combiner des fichiers Excel. Le principe est quasi identique à celui concernant des fichiers .csv.
L'assistant est aussi bien pratique pour les 1ers galops PowerQuery sauf qu'il en met un peu trop à mon gout
.L'environnement de travail et de paramétrage
Environnement PowerQuery
Je ferai abstraction sur l'assistant. Pour autant son code source est dans le fichier joint.
Import unitaire d'un fichier Excel
PowerQuery:
let
//------------------------------------------------------------------------------------------------
// On importe le tableau structuré du fichier Excel
//------------------------------------------------------------------------------------------------
Source = Excel.Workbook(File.Contents("D:\DATA\98__SOURCES__DONNEES\Data_Ventes\Ventes\EXCEL\Ventes_2020.xlsx"), null, true),
TB_VENTES_Table = Source{[Item="TB_VENTES",Kind="Table"]}[Data],
#"Type modifié" = Table.TransformColumnTypes(TB_VENTES_Table,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
#"Type modifié"
let
//------------------------------------------------------------------------------------------------
// On importe le tableau structuré du fichier Excel
//------------------------------------------------------------------------------------------------
Source = Excel.Workbook(File.Contents("D:\DATA\98__SOURCES__DONNEES\Data_Ventes\Ventes\EXCEL\Ventes_2021.xlsx"), null, true),
TB_VENTES_Table = Source{[Item="TB_VENTES",Kind="Table"]}[Data],
#"Type modifié" = Table.TransformColumnTypes(TB_VENTES_Table,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
#"Type modifié"On remarque que le code est assez simple et facile à reproduire avec un autre fichier. Pour autant cela peut se transformer en fonction paramétrable.
Fonction fnGetEXCEL
PowerQuery:
let fnGetEXCEL = (pFile as text, optional ppItem as text, optional ppKind as text) as table =>
//---------------------------------------------------------------------------------
// Imporatation d'un fichier Excel à partir d'un dossier
//---------------------------------------------------------------------------------
let
//---------------------------------------------------------------------------------
// Lecture des paramètres
//---------------------------------------------------------------------------------
pKind=if ppKind is null then "Sheet" else ppKind,
pItem=if ppItem is null then "Feuil1" else ppItem,
pFolder=fnGetParameter("TB_PARAMS","CHEMIN_EXCEL"),
//---------------------------------------------------------------------------------
// Le combine nécessite une liste de table à combiner
//---------------------------------------------------------------------------------
pPath=pFolder & "\" & pFile,
//---------------------------------------------------------------------------------
// Importation du fichier
//---------------------------------------------------------------------------------
Source = Excel.Workbook(File.Contents(pPath), null, true),
ToTable=Source{[Item=pItem,Kind=Text.Proper(pKind)]}[Data],
Promote=if Text.Upper(pKind)="SHEET" then
Table.PromoteHeaders(ToTable, [PromoteAllScalars=true])
else if Text.Upper(pKind)="TABLE" then
ToTable
else Source
in
Promote
in
try fnGetEXCEL otherwise nullUtilisation de la fonction fnGetEXCEL
On invoque soit une table soit un onglet. La distinction s'effectue via le paramètre Kind et Item le nom de l'objet que l'on souhaite récupérer :
- Source = fnGetEXCEL ("Ventes_2020.xlsx","TB_VENTES", "Table"), (pour une table)
- Source = fnGetEXCEL ("Ventes_2020.xlsx","Query Data", "Sheet"), (pour un onglet)
PowerQuery:
let
//------------------------------------------------------------------------------------------------
// On importe le tableau structuré du fichier Excel
//------------------------------------------------------------------------------------------------
Source = fnGetEXCEL ("Ventes_2020.xlsx","TB_VENTES", "Table"),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
#"Type modifié"
let
//------------------------------------------------------------------------------------------------
// On importe le tableau structuré du fichier Excel
//------------------------------------------------------------------------------------------------
Source = fnGetEXCEL ("Ventes_2020.xlsx","Query Data", "Sheet"),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
#"Type modifié"Combinaison manuelle des fichiers Excel - via une table
PowerQuery:
let
//---------------------------------------------------------------------------------
// On appel directement les tables présentes dan l'environnement powerquery
// Tables sources présentes dans le fichier Excel
//---------------------------------------------------------------------------------
Source = Table.Combine({T_EXCEL_1, T_EXCEL_2})
in
SourceCombinaison manuelle des fichiers Excel - via un onglet
PowerQuery:
let
//---------------------------------------------------------------------------------
// On appel directement les tables présentes dan l'environnement powerquery
// Combine sur les feuilles (onglets) présents dans les fichiers Excel
//---------------------------------------------------------------------------------
Source = Table.Combine({T_QueryData_1, T_QueryData_2})
in
SourceCombinaison manuelle en précisant le dossier
PowerQuery:
let
FIELD_CUSTOM="ContentField",
pFolder="D:\DATA\98__SOURCES__DONNEES\Data_Ventes\Ventes\EXCEL",
pFilter=null,
Source = Folder.Files(pFolder),
Filter = if pFilter is null then
Source
else Table.SelectRows(Source, each Text.StartsWith([Name], pFilter)),
Lst_1={"Name", "Data", "Item", "Kind", "Hidden"},
Lst_2={"Name.bis", "Data", "Item", "Kind", "Hidden"},
AddCustomContentField = Table.AddColumn(Filter, FIELD_CUSTOM, each Excel.Workbook([Content], true)),
ExpandCustomContentField = Table.ExpandTableColumn(AddCustomContentField, FIELD_CUSTOM, Lst_1, Lst_2),
ExpandToTable=Table.Combine(ExpandCustomContentField[Data])
in
ExpandToTableSi on souhaite obtenir un fichier particulier du dossier
PowerQuery:
let
//------------------------------------------------------------------------------------------------
// On importe le tableau structuré du fichier Excel
//------------------------------------------------------------------------------------------------
Source = fnGetEXCEL ("xVentes.xlsx"),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Client", type text}, {"Produit", type text}, {"Vendeur", type text}, {"Montant", Int64.Type}})
in
#"Type modifié"Appel à une fonction fnGetFolderExcelFiles personnalisée pour importer un dossier Excel
PowerQuery:
let
pPath=fnGetParameter("TB_PARAMS","CHEMIN_EXCEL"),
pFilter=fnGetParameter("TB_PARAMS","FILTRE"),
Source = fnGetFolderExcelFiles(pPath,pFilter)
in
SourceCode de la fonction fnCombineExcelFiles
PowerQuery:
let fnCombineExcelFiles=(pFolder as text, optional pFilter as text) as table =>
//-----------------------------------------------------------------------------------
// Importation de fichiers Excel contenus dans un dossier
//-----------------------------------------------------------------------------------
let
FIELD_CUSTOM="ContentField",
//-----------------------------------------------------------------------------------
// Dossier des fichiers Excel
//-----------------------------------------------------------------------------------
Source = Folder.Files(pFolder),
//-----------------------------------------------------------------------------------
// Eventuellement on peut filtrer sur les noms de fichiers
//-----------------------------------------------------------------------------------
Filter = if pFilter is null then
Source
else Table.SelectRows(Source, each Text.StartsWith([Name], pFilter)),
//-----------------------------------------------------------------------------------
// Pour le renommage des noms de colonnes techniques
//-----------------------------------------------------------------------------------
Lst_1={"Name", "Data", "Item", "Kind", "Hidden"},
Lst_2={"Name.bis", "Data", "Item", "Kind", "Hidden"},
//-----------------------------------------------------------------------------------
// On récupère les données binaires dans une colonne spécifique
//-----------------------------------------------------------------------------------
AddCustomContentField = Table.AddColumn(Filter, FIELD_CUSTOM, each Excel.Workbook([Content], true)),
//-----------------------------------------------------------------------------------
// Renommage des colonnes techniques
//-----------------------------------------------------------------------------------
ExpandCustomContentField = Table.ExpandTableColumn(AddCustomContentField, FIELD_CUSTOM, Lst_1, Lst_2),
//-----------------------------------------------------------------------------------
// On déploie les données binaires des fichiers Excel
//-----------------------------------------------------------------------------------
ExpandToTable=Table.Combine(ExpandCustomContentField[Data])
in
ExpandToTable
in
fnCombineExcelFilesFonction personnalisée mixte capable soit d'importer un dossier .csv soit un dossier Excel
PowerQuery:
let
//---------------------------------------------------------------------------------
// Appel de la fonction permettant de combiner plusieurs fichiers spécifiques
// Ces fichiers sont définis dans la table des paramètres
//---------------------------------------------------------------------------------
Source=fnCombineCSVExcelByList("TB_PARAMS", "LISTE_FICHIERS_EXCEL", "EXCEL", "TB_VENTES", "Table")
in
Source
PowerQuery:
let
//---------------------------------------------------------------------------------
// Appel de la fonction permettant de combiner plusieurs fichiers spécifiques
// Ces fichiers sont définis dans la table des paramètres
//---------------------------------------------------------------------------------
Source=fnCombineCSVExcelByList("TB_PARAMS", "LISTE_FICHIERS")
in
SourceFonction fnCombineCSVExcelByList
De la manière qu'un fichier CSV cette fonction permet d'importer des fichiers au cas par cas qu'il soit CSV ou qu'il soit EXCEL
PowerQuery:
let
//---------------------------------------------------------------------------------
// Fonction permettant de combiner plusieurs fichiers spécifiques dans un dossier
// Ce dossier et ces fichiers sont définis dans une table paramètres
// Soit EXCEL
// Soit CSV
//---------------------------------------------------------------------------------
// La table des paramètres et le paramètre contenu les fichiers à combiner
fnCombineExcelByList = (pTableParams as text, pParam as text, optional ppType as text, optional ppItem as text, optional ppKind as text) as table =>
let
//-------------------------------------------------------------------
// Permet de gérer soit un import CSV soit un import EXCEL
//-------------------------------------------------------------------
pType=if ppType is null then "CSV" else ppType,
LstFiles=Text.Split(fnGetParameter(pTableParams,pParam),";"), // Liste des fichiers à combiner
LstCombine=List.Accumulate(
LstFiles, // Initialisation de la boucle sur la liste à traiter
#table({},{}), // Initialisation de l'accumulateur sur une table vide
(state,current) => // Boucle de traitement pour combiner les fichiers un à un
let
//------------------------------------------------------
// Aiguillage soit EXCEL soit CSV
//------------------------------------------------------
Source = if Text.Upper(pType)="EXCEL" then
fnGetEXCEL(current, ppItem, ppKind) // On va chercher un fichier Excel
else if Text.Upper(pType)="CSV" then
fnGetCSV(current) // On va chercher un fichier CSV
else null, // Sinon on prend le parti de ne rien renvoyer
Combine = Table.Combine( {state , Source}) // Combinaison du fichier en cours avec ceux déja combinés dans state
in
Combine // Ensemble des fichiers combinés
)
in
LstCombine
in
fnCombineExcelByListFonction fnCombineExcelByList d'importation spécifique uniquement pour les fichiers Excel
PowerQuery:
let
//---------------------------------------------------------------------------------
// Fonction permettant de combiner plusieurs fichiers spécifiques dans un dossier
// Ce dossier et ces fichiers sont définis dans une table paramètres
//---------------------------------------------------------------------------------
// La table des paramètres et le paramètre contenu les fichiers à combiner
fnCombineExcelByList = (pTableParams as text, pParam as text, optional ppItem as text, optional ppKind as text) as table =>
let
LstFiles=Text.Split(fnGetParameter(pTableParams,pParam),";"), // Liste des fichiers à combiner
LstCombine=List.Accumulate(
LstFiles, // Initialisation de la boucle sur la liste à traiter
#table({},{}), // Initialisation de l'accumulateur sur une table vide
(state,current) => // Boucle de traitement pour combiner les fichiers un à un
let
Source = fnGetEXCEL(current, ppItem, ppKind), // Importation du fichier en cours
Combine = Table.Combine( {state , Source}) // Combinaison du fichier en cours avec ceux déja combinés dans state
in
Combine // Ensemble des fichiers combinés
)
in
LstCombine
in
fnCombineExcelByListEt appel de la fonction fnCombineExcelByList
PowerQuery:
let
//---------------------------------------------------------------------------------
// Appel de la fonction permettant de combiner plusieurs fichiers spécifiques
// Ces fichiers sont définis dans la table des paramètres
//---------------------------------------------------------------------------------
Source=fnCombineExcelByList("TB_PARAMS","LISTE_FICHIERS_EXCEL","TB_VENTES", "Table")
in
Source
Dernière édition:
