How to iterare all the excel sheets present in a excel file in azure data factory

amikm 1 Reputation point

I have an Excel file with 5 sheets: Sheet1, Sheet2, Sheet3, Sheet4, Sheet 5. In the future, the user can add Sheet6, Sheet7 as well.

I want to create a pipeline to copy all the sheet data into a single table. I want to iterate all the sheets in excel and copy the data from Sheet to a single table.

As per my approach, I have created an Array variable and assigned ["Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5"] and I am using a Foreach loop and Inside Foreach, I am copying the Sheet data to a single table.

In my second approach, I am using a Lookup activity to fetch the sheet info from a SQL table and then using foreach loop to copy the sheet's data into the table.

But, in both the approach, whenever a user adds a new sheet, either, I need to update my ADF pipeline (approach 1) or I need to update my SQL table where Sheet info is present.

I don't want to update either the pipeline or SQL table to fetch data from the new additional sheet. It should iterate dynamically and loads all the sheets' data to a single table. It will do always truncate and load.

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

1 answer

Sort by: Most helpful
  1. Nandan Hegde 22,721 Reputation points

    Currently getting the sheetnames dynamically in ADF is not possible.
    So you would have to write a custom logic to get the list of sheet names and then iterate it over foreach.

    For that you can leverage Azure automation/Azure function etc and call them in ADF