question

amikm avatar image
0 Votes"
amikm asked ShaikMaheer-MSFT commented

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

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @amikm ,

Just checking in to see if the above answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hi @amikm ,

Following up to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

1 Answer

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered ShaikMaheer-MSFT commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NandanHegde-7720 @ShaikMaheer-MSFT
When I wrote the code in my local, I am able to get Sheet info ( ["Sheet1", "Sheet2"]) but I am not much aware on Azure functions side, how we can write the same piece of code there, the only change would be, my excel file will be present on adls gen 2.

0 Votes 0 ·

Hi @amikm,

You should use Azure SDK for ADLS Gen2 to interact with ADLS Gen2 from program and do required task.
Below link has all SDKs for Azure, Kindly explore those libraries to write your code accordingly.
https://azure.microsoft.com/en-in/downloads/

0 Votes 0 ·