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

amikm 11 Reputation points
2021-09-28T16:44:28.563+00:00

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.
9,487 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 29,886 Reputation points MVP
    2021-09-29T06:09:39.003+00:00

    Hey,
    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

    https://stackoverflow.com/questions/67541195/adf-how-to-copy-an-excel-sheet-with-multiple-sheets-into-separate-csv-files