ADF xlsx file Dynamic sheet name

Question

Hi, I have an excel file stored in Blob, i am calling this excel via folder input as there will always be only 1 file in this particular folder but the problem is sheet name would change. I am creating a Data source in ADF and connecting via folder input. I want to know a way around if the sheet name changes. I dont want to change the model everytime there is new file.

Also, the said folder consists only one excel file and this excel file consists only one sheet but this sheet name would change periodically

Answer 1

Hi @Aravind Sampath ,

Thank you for posting query in Microsoft Q&A Platform.

To summarize ask here, you would like to get excel sheet names dynamically in ADF, so that you can take that sheet name in to your dataset and read data accordingly. Please correct me if I am wrong.

Currently, reading excel sheet names dynamically in ADF is not possible directly. But there are work arounds you can write your code in Azure Functions which get sheet names from excel. Call that Azure function using Azure function activity in Azure data factory.

Below link has detailed explanation about same. Kindly check it.
https://stackoverflow.com/questions/67541195/adf-how-to-copy-an-excel-sheet-with-multiple-sheets-into-separate-csv-files

Hope this helps. Please let us know if any further queries. Thank you.

-------------

Please consider hitting Accept Answer. Accepted answers helps community as well.

Answer 2

Hi @Aravind Sampath ,

In the Excel Dataset --> Worksheet mode , you can try Index option instead of Name (please see the screenshot below). Hope this helps. Thanks! :)