Forum Discussion
Transforming mulitple sheets in multiple files when using 'Load from folder'
Hi,
I have a folder full of spreadsheets, each containing multiple ranges and sheets that I want to transform and then merge into a summary table.
Currently I'm having to load from folder, select the table or worksheet I want to transform, use the 'helper' to carry out the transform on all the excel files and then repeat the process, i.e. go back to loading all the files from the folder again.
This doesn't feel right.
How can I carry out the transforms I need for each table and sheet by only loading from folder once? Is there a way to duplicate and re-path the Transform Sample File helper?
Any help or pointers to tutorials gratefully received.
Cheers
K.
Hello KWilkinson02
Try to do as follows
- Query one Excelfile in order to have the result you want
- Create a new text parameter calles "FileName" with the complete path of your Excel-file
- enter advanced editor and substitute in the first line the path information with your newly created parameter
- right click on the query and select create function. Give it the name "GetData"
- Query your folder
- Add new column invoking your function like GetData([Path]&[Name])
- Combine the new created column to one table
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
15 Replies
Hello KWilkinson02
Try to do as follows
- Query one Excelfile in order to have the result you want
- Create a new text parameter calles "FileName" with the complete path of your Excel-file
- enter advanced editor and substitute in the first line the path information with your newly created parameter
- right click on the query and select create function. Give it the name "GetData"
- Query your folder
- Add new column invoking your function like GetData([Path]&[Name])
- Combine the new created column to one table
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
JimmyThanks Jimmy801 I will give this a go today and let you know how I get on. There some stuff here I don't quite understand but I'll do some digging and try and figure it out.
Thanks for taking the time to respond, much appreciated.
- Query one Excelfile in order to have the result you want
my Excel file needs about 7 queries to get what I need - these are all merged into one report and this is working okay (in principle at least)
- Create a new text parameter calles "FileName" with the complete path of your Excel-file
I've done this and it's worked fine.
- enter advanced editor and substitute in the first line the path information with your newly created parameter
I can't do this to my final combined query only the constituent queries - am I doing this correctly?
- right click on the query and select create function. Give it the name "GetData"
I can do this if I carry out the step above on one of the constituent queries.
- Query your folder
Get a bit lost here - do you mean 'Get data from folder' or something else?
- Add new column invoking your function like GetData([Path]&[Name])
this might make more sense if I can get the step previously to work but not sure where I'm supposed to be adding the column...
- Combine the new created column to one table
Again, not sure on this one. I know how to merge queries but not tryed anything specifically with columns..
Hello KWilkinson02
- enter advanced editor and substitute in the first line the path information with your newly created parameter
I can't do this to my final combined query only the constituent queries - am I doing this correctly?
--> you need to add the new parameter name in the report/query you created in the first step (where on Excel-file is completely formated)
- Query your folder
Get a bit lost here - do you mean 'Get data from folder' or something else?
--> as you told me your starting point is a folder with all Excel-files... so here is meant to query exactly this folder so that you have a table with all your files
Jimmy
