reading excel sheets Dynamically in azure data factory

Question

I am working on one of datafactory pipeline where I need to read all the sheets of excel files dynamically and use them as source in copy activity. For now, I could put all the names in variable and access, but I want to get them dynamically and load. Any suggestion? does anyone tried this earlier? Appreciate the suggestions, thank you.

Answer 1

Hello @PurushothamChanda-2222 and welcome to Microsoft Q&A.

I do have an idea I'd like to share.

Suppose you give your excel files a "Cover sheet" or "table of contents", a sheet with a known, fixed name, and its contents are the names of all the other sheets.
Then you can do a lookup activity on this sheet, and use the lookup output in a ForEach activity. The ForEach activity iterates over the sheet names, passing them to a parameterized dataset (sheet is parameterized) to copy every other sheet in turn.

Right now, the copy activity only copies a single sheet at a time, so your solution will probably iterate over the names anyway. This just makes it more flexible.

Does this help?

Answer 2

I have tried but no solution yet