How to read all the sheets from an Excel file and push into Data Lake using Azure Data Factory
Tags: azure, datascience, softwaredevelopment, cloud
Published: 2023-09-03 00:14:15

Azure Data Factory doesn't have an in-built function to read all the sheets from an Excel file but it supports reading data from an Excel file using various methods, including Azure Data Factory Mapping Data Flows. Below are the general steps to read all the sheets from an Excel file in Azure Data Factory:-

**Create an Azure Data Factory Service in Microsoft Azure Portal**
If you haven't already, create an Azure Data Factory instance.

**Create Linked Services for Source and Destination Files**
In Azure Data Factory, create two Linked Services for your source (Excel File) and destination (Azure Data Lake) files. This is the connection information needed to access and store the data.

**Create Datasets for Source and Destination Files**
In Azure Data Factory, create two Datasets for your source (Excel File) and destination (Azure Data Lake) files. Set the Linked Services to the one you created in the previous step. 

Let us name these two Datasets as follows:-
Source -> _source_ds_
Destination -> _destination_ds_

In _source_ds_ create a new parameter under Parameters. Name the parameter as _source_sheet_names_. Under Connection send the newly created parameter as an input to the Sheet name. This action can be done by adding in the dynamic content under the Sheet name. Use the following expression to add -> _@dataset().source_sheet_names_. 

```
Source Dataset -> source_ds
Parameters -> New -> source_sheet_names (Type: String)
Connection -> Sheet name -> Dynamic Content -> @dataset().source_sheet_names

``` 
Thereby, the sheet names will be sent as a Parameter from the Pipeline.

In _destination_ds_ create a new parameter under Parameters. Name the parameter as _destination_file_name_. Under Connection send the newly created parameter as an input to the File path. This functionality will create a new folder for each sheet name and name it with the respective sheet name. This action can be done by adding in the dynamic content under the File path. Use the following expression to add -> _destination/final_file/@{dataset().destination_file_name}/@dataset().destination_file_name_.

```
Destination Dataset -> destination_ds
Parameters -> New -> destination_file_name (Type: String)
Connection -> File path -> Dynamic Content -> destination/final_file/@{dataset().destination_file_name}/@dataset().destination_file_name 
 
```
Thereby, the file names will be sent as a Parameter from the Pipeline.

**Create a Pipeline**
Create a new Pipeline in Azure Data Factory. 

Let us name the above created Pipeline as follows:-
Pipeline -> _adl_pipeline_

Create a new Variable under Variables and name it as _sheet_names_. Select the type as Array and under Default value send the list of sheet names as a default value -> _["sheet 1", "sheet 2", "sheet 3", "sheet 4"]..._

```
Pipeline -> adl_pipeline
Variables -> New -> sheet_names -> ["sheet 1", "sheet 2", "sheet 3", "sheet 4"]...

```
Thereby, the sheet names will be sent during the runtime.

**Add an Activity**
In your Pipeline, add a ForEach activity and send the _sheet_names_ as input to the ForEach activity. This can be achieved by navigating to Settings -> Items -> Dynamic Content -> _@variables('sheet_names')_.

```
ForEach Activity -> Settings -> Items -> Dynamic Content -> @variables('sheet_names')

```

**Add an Activity within the ForEach Activity to copy the data from Source to Destination**

In your ForEach Activity, add a Copy data Activity and map the Dataset properties with the _sheet_names_. This can be achieved by navigating to Source -> Name -> source_sheet_names -> _@item()_ and Sink -> Name -> destination_file_name -> _@item()_.

```
ForEach Activity -> Copy data Activity -> 
Source -> Name -> source_sheet_names -> @item() 
Sink -> Name -> destination_file_name -> @item()

```
Thereby, the ForEach Activity will iterate through each of the sheet and copies the data to the destination folder.

**Save and Execute the Pipeline**

The entire workflow in a Nutshell:-
```
Source Dataset -> source_ds
Parameters -> New -> source_sheet_names (Type: String)
Connection -> Sheet name -> Dynamic Content -> @dataset().source_sheet_names

Destination Dataset -> destination_ds
Parameters -> New -> destination_file_name (Type: String)
Connection -> File path -> Dynamic Content -> destination/final_file/@{dataset().destination_file_name}/@dataset().destination_file_name

Pipeline -> adl_pipeline
Variables -> New -> sheet_names -> ["sheet 1", "sheet 2", "sheet 3", "sheet 4"]...

ForEach Activity -> Settings -> Items -> Dynamic Content -> @variables('sheet_names')

ForEach Activity -> Copy data Activity -> 
Source -> Name -> source_sheet_names -> @item() 
Sink -> Name -> destination_file_name -> @item()

```

**Done**