ADF xlsx file Dynamic sheet name

Aravind Sampath 1 Reputation point
2022-03-10T14:33:44.117+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,711 questions
{count} votes

2 answers

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,126 Reputation points Microsoft Employee
    2022-03-11T15:35:40.713+00:00

    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.

    0 comments No comments

  2. Nasreen Akter 10,761 Reputation points
    2022-03-14T12:17:47.64+00:00

    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! :)

    182787-image.png