How to combine multiple files from one folder to multiple sheets in one file ?

[JT0168]

I have several files from one folder below example and wanted to consolidate them in one file under different sheet names corresponding to the file name. Is there a way to do this? The file name is changing every month based on the week.

ABC-2022-09-30.csv

ABC-2022-10-07.csv

ABC-2022-10-14.csv

ABC123-2022-0930.csv

ABC123-2022-10-07.csv

Thank you!

[gautiergodard]

Hello @JT0168 

Yes, you could do something like this ! 

You'll just need to replace the folder paths in the input, formula and output tools to point to wherever your files are saved currently and wherever you want to save the combined workbook.

Hope this helps!

[IraWatt · Accepted solution]

Hey @JT0168,

Sure, this is possible.

First get all the paths to the CSV's using the directory tool:

Then use a dynamic input tool configured to also pass through the file name with the data for each file

In the dynamic input make sure to change the data source off the full path field:

Then use a formula to add on ||| to make the file name a sheet name:

Finally output the data using the output data tool changing the sheet name based on the sheet column we made:

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[JT0168]

What if I need to combine those ABC123 in one sheet and the rest separate sheets in one file? Thank you!

ABC-2022-09-30.csv

ABC-2022-10-07.csv

ABC-2022-10-14.csv

ABC123-2022-0930.csv

ABC123-2022-10-07.csv

[JT0168]

No need to reply I got the answer...thank you!

[gautiergodard · Accepted solution]

@JT0168 my earlier reply could help you do this as well without the need for a dynamic input tool :)