How to load multiple excel files with multiple sheets in Azure Data Factory

Question

My requirement in Azure data factory is to load multiple excel files which contains multiple excel sheets.

Like Excel workbook1 contains sheet1, sheet2, sheet3, sheet4...and excel workbook2 contains sheet1, sheet2 and so on. Like that I have around 50 excel workbooks.Is there a way, I can get all the excel workbook into adf.

Answer 1

Currently, there is no direct way to pull the sheet names dynamically from excel files using Azure Data Factory.

As a workaround, you can implement using one of the below methods.

Method 1:

1. If you have the same sheet names in all excel files, you can create a variable of type array with sheets names in the value in the ADF pipeline.

2. Parameterize the sheet name in the dataset and loop each value of a variable using ForEach activity and pass the current item to the copy data activity dataset properties.

Method 2:

1. If you have different sheet names in each excel file, you can create a file to add the file names and sheet names to it.

2. Using the lookup activity, you can pull the data from the file and pass the file name and sheet name to the ForEach activity by parameterizing the sheet name in the dataset.

You can raise a feature suggestion from the azure data factory home page under feedback.