Data Factory - Multiple sheets into CSV file

Question

I am attempting to create some derived columns & flatten transformations on multiple excel sheets. My file has about 20 sheets and I need to work on about 15 of them.

This is my current data flow:

Is there a way to create a loop, so I can select the sheets I want and run them through that data flow, then at the end join them all into one dataset and save them as a CSV file back to Blob Storage?

Answer 1

Unfortunately, Azure Data Factory does not have the capability to know sheet names inside excel during run time.

Best way is you can save your sheet names in some file or table and then use lookup activity to get that sheet names.

Once you get sheet names use foreach activity to pass that sheet names and loop through each sheet to implement your logic.

Note: You should have a data sheet for your excel with parameterized sheet names.

You can have your data flow also parameterized and pass sheet names from the loop into data flow.