question

PurushothamChanda-2222 avatar image
0 Votes"
PurushothamChanda-2222 asked ·

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.

azure-data-factory
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?

1 Vote 1 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered ·

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?

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @MartinJaffer-MSFT for reply. I have done this already by keeping all of sheet names in JSON and read through foreach. But I want to implement in such a way that it should read dynamically without knowing the sheet names. I am not sure if it can be done, but just checking. Thank you.

1 Vote 1 ·

@PurushothamChanda-2222 I'm afraid it is not possible at this time in ADF. I just threw my entire bag of tricks at it, and right now, name must be specified. I even tried offsets instead of names.

The Excel connector is still relatively new. With enough demand, more features may be added.

Please share your requirements in a feature request at the feedback forum, and / or upvote this related feature request about retrieving sheet names.

Sorry I wasn't able to make things easier for you.

0 Votes 0 ·
AnmolGanju-2818 avatar image AnmolGanju-2818 PurushothamChanda-2222 ·

Check this out https://debbiesmspowerbiazureblog.home.blog/2020/10/07/data-factory-moving-multiple-lookup-worksheets-from-excel-to-one-lookup-table-in-sql-server/

0 Votes 0 ·
RishabhRuwatia-1223 avatar image
0 Votes"
RishabhRuwatia-1223 answered ·

I have tried but no solution yet

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.