I have tried but no solution yet
reading excel sheets Dynamically in azure data factory
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.
2 answers
Sort by: Newest
-
-
MartinJaffer-MSFT 26,026 Reputation points
2020-09-30T15:53:09.563+00:00 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?