Adf Copy task with Excel in a foreach loop

Question

within Azure Adf, I have an excel file with 4 tabs named Sheet1-Sheet4

I would like to loop through the excel creating a CSV per tab

I have created a SheetsNames parameter in the pipeline with a default value of ["Sheet1","Sheet2","Sheet3","Sheet4"]

How do I use this with the copy task to loop with the tabs?

please visit reddit.com/r/AZURE/comments/nd0crr/… and check if it is useful. — Utkarsh Pal
– Utkarsh Pal, Commented Jun 23, 2021 at 18:21

Steve Johnson · Accepted Answer · 2021-06-24 07:32:58Z

1

Please try this:

Create a SheetsNames parameter in the pipeline with a default value of ["Sheet1","Sheet2","Sheet3","Sheet4"].
Add a For Each activity and type @pipeline().parameters. SheetsNames in the Items option.
Within the For Each activity, add a copy activity.
Create Source dataset and create a parameter named sheetName with empty default value.

Navigate to the Connection setting of the Source dataset and check Edit in the Sheet name option. Then type @dataset().sheetName in it.

Navigate to the Source setting of the Copy data activity and pass @item() to the sheetName.

answered Jun 24, 2021 at 1:27

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Perfect! I was trying to do something similar but veered off the beaten path