How to copy multiple unknown number of excel worksheets in azure data factory

Question

We have schedule weekly excel files coming into blob storage.
We want to copy that excel into azure data base using azure data factory.
Excel file sometimes contains 5 worksheets and sometime 6 or 7. Is there any way adf copies worksheets dynamically.

Answer 1

Thanks @Nandan Hegde ,

Hello @Amrinder singh ,

Thanks for the ask and using the Microsoft Q&A platform .

May be simpler solution ( i have not implemented this till now )
At this time ADF assumes that we know what are the worksheets we have in the spreadsheet . In your case its not , but i suggest you create 3 WS in excel , in case when you have data in WS 1 only the data in other WS will be blank . It should work out .

Please do let me know how it goes .
Thanks
Himanshu

Answer 2

Hey,
Based on my understanding ADF doesnt have in built functionality to provide the sheet names.

So you would have to create a custom logic via Python/Powershell leveraging Azure function/ Batch to get the sheet names :

https://stackoverflow.com/questions/12250024/how-to-obtain-sheet-names-from-xls-files-without-loading-the-whole-file

And then leverage For loop iteration to copy those sheets .