Forum Discussion
Batch Import multiple excel files with different column name from a folder
Hi
I have trouble to perform batch import from multiple excel files which are located in a particular folder where in some files column names differs for example in some file table column name mentioned as "Week ID" and in some files "week" and in some files "WEEK" in some some files just "ID" and they are not in the same column some files it is in "col1" in some files it is in "col5' etc. How to manage this? I need you guidelines on this Thanks in advance
Hello k_mathana
this should be your code
give it a try and let us know if its working.
let Source = Folder.Files("C:\Users\k_mathana\Desktop\Sample"), #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content], true)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}), ChangeColumnName =(tableint)=> let TransformTable = #table({"old", "new"},{{"Week ID2", "WEEK"}, {"Week ID", "WEEK"}}), FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(tableint), _[old]))), RenameWeek = try Table.RenameColumns(tableint, FilterForWeek) otherwise tableint in RenameWeek, #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table")), GetAllColumnNames = List.Distinct(List.Combine(Table.TransformColumns(RenameColumnsData, {{"Data", each Table.ColumnNames(_)}})[Data])), RenameColumnsData = Table.TransformColumns ( #"Filtered Rows", { { "Data", (tableint)=> ChangeColumnName(tableint) } } ), #"Removed Other Columns" = Table.SelectColumns(RenameColumnsData,{"Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", GetAllColumnNames, GetAllColumnNames) in #"Expanded Data"In this line you can maintain your names to be renamed
The first value specifies the old, the second the new one. Be aware that Power query is case sensitive
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHello @k_mathana
this should be your code
give it a try and let us know if its working.
let Source = Folder.Files("C:\Users\k_mathana\Desktop\Sample"), #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content], true)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}), ChangeColumnName =(tableint)=> let TransformTable = #table({"old", "new"},{{"Week ID2", "WEEK"}, {"Week ID", "WEEK"}}), FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(tableint), _[old]))), RenameWeek = try Table.RenameColumns(tableint, FilterForWeek) otherwise tableint in RenameWeek, #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table")), GetAllColumnNames = List.Distinct(List.Combine(Table.TransformColumns(RenameColumnsData, {{"Data", each Table.ColumnNames(_)}})[Data])), RenameColumnsData = Table.TransformColumns ( #"Filtered Rows", { { "Data", (tableint)=> ChangeColumnName(tableint) } } ), #"Removed Other Columns" = Table.SelectColumns(RenameColumnsData,{"Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", GetAllColumnNames, GetAllColumnNames) in #"Expanded Data"In this line you can maintain your names to be renamed
The first value specifies the old, the second the new one. Be aware that Power query is case sensitive
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
20 Replies
If they were in the same columns, you could just eliminate the "Use first row as column headers" step in the combine operation. But since they are not, I think you are going to have to import one at a time, or provide structure for your users to use. I suspect it is possible, but I cannot imagine the complexity and tediousness of the M code to find the data in random places (col1, col5, col8) then consolidate into a single usable table. And when you think you have it, someone will do something to their file to break it.
Power Query is quite powerful, but not magic. It does rely somewhat on some level of consistency and predictability in the source data.
Hello all,
k_mathana yesterday asked how to rename a column, that on multiple tables are called differently. And so I provided a possible solution to that. After that it turned out that there is also a need to read a folder, to read a Excel-file and afterwards to apply my solution. I asked to closed that threat and open a new one for reading folders and Excel-files. So k_mathana did, but asked again the same question. The question of this threat would be actually how to read a folder with multiple Excel-files.
This could be rather tricky, as you need to know how exactly your Excel-files are looking like (sheets, tables, printing areas?, all the same name?).
k_mathana try this
- Add a new folder source and select your folder
- Transform it
- Filter the table to the files you need (maybe all, then keep it, otherwise filter for .xlsx or name or whatever)
- Add a new custom column and use this formula "Excel.Workbook([Content], true)",
- Expand the column, by clicking on the right side of your newly created column and click okay. You will get now all objects (sheets, tables, etc) of all your Excel-file.
- Filter them (by the column "Kind") to sheet only or table or whatever you need to read.
After this you will have a list of tables, that contains your data.
Next please read one Excel-sheet how you need to and apply all transformation you need (promote headers, reformatting etc.). Post the code here and I will transform the solution of renaming the columns adapt to your needs. Then we can do the final step to apply the function to the filtered tables of your Excel-files created above
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyMr. Jimmy, Thanks for the response. Here the code for my 4 sample files import.
let
Source = Folder.Files("C:\Users\k_mathana\Desktop\Sample"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content], true)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Week ID", "Task", "A", "Date", "Week ID2", "B", "C", "ID", "WEEK"}, {"Week ID", "Task", "A", "Date", "Week ID2", "B", "C", "ID", "WEEK"})
in
#"Expanded Data"Hello k_mathana
this should be your code
give it a try and let us know if its working.
let Source = Folder.Files("C:\Users\k_mathana\Desktop\Sample"), #"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content], true)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}), ChangeColumnName =(tableint)=> let TransformTable = #table({"old", "new"},{{"Week ID2", "WEEK"}, {"Week ID", "WEEK"}}), FilterForWeek = Table.ToRows(Table.SelectRows(TransformTable, each List.Contains(Table.ColumnNames(tableint), _[old]))), RenameWeek = try Table.RenameColumns(tableint, FilterForWeek) otherwise tableint in RenameWeek, #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table")), GetAllColumnNames = List.Distinct(List.Combine(Table.TransformColumns(RenameColumnsData, {{"Data", each Table.ColumnNames(_)}})[Data])), RenameColumnsData = Table.TransformColumns ( #"Filtered Rows", { { "Data", (tableint)=> ChangeColumnName(tableint) } } ), #"Removed Other Columns" = Table.SelectColumns(RenameColumnsData,{"Data"}), #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", GetAllColumnNames, GetAllColumnNames) in #"Expanded Data"In this line you can maintain your names to be renamed
The first value specifies the old, the second the new one. Be aware that Power query is case sensitive
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
This situation is not without a solution, but it's best not to have the situation you describe, and I suggest you standardize the base file format at a later stage.
You can upload some files that have been cleared of sensitive information to the cloud drive and then share the link here.It is important to indicate which field names are used to express the same meaning and which field names are ultimately needed to present the data.Your final desired table effect is also simulated in the file.
Yes ziying35 I can understand what you are trying to say. But what if I can maintain the information some where for example what ever like "Week ID", "WeekID", "week", "WEEK", "ID" should read as "Week" and before transforming in to table the rename should happen. I can't correct this in the base files because they are maintained by different departments and more over it is over 1500 excel file which accumulated over the years.
I have a solution where you import 1500+ files from a folder, extract all of their field names, then de-duplicate them, use them to make a comparison table between the old and new names, and finally use this comparison table to implement file renaming and merge table operations in PQ with code.
I've done this myself
I have a solution where you import 1500+ files from a folder, extract all of their field names, then de-duplicate them, use them to make a comparison table between the old and new names, and finally use this comparison table to implement file renaming and merge table operations in PQ with code.
I've done this myself
