Processing a single sheet Excel file into a CSV file using Azure Data Factory

In this tutorial, I’ll walk you through a practical hands-on exercise to convert a single-sheet Excel file into a CSV file using Azure Data Factory (ADF).

Blob-Excel-CSV-Blob

🔧 Azure Resources Used

Azure Blob Storage (for storing source and target files)

Azure Data Factory (for pipeline orchestration)

User-Assigned Managed Identity (for secure access to storage)

Source Data and Container Setup:

Download the Excel file and upload it to your Azure Blob Storage container.

Website name: Excelx.com

Download the excel file from ExcelX.com

Structure: Create the storage account using the below structure,

Storage Account

└── input-excel-container

    └── Employee

        └── Filename.xlsx

You can follow this structure or use your own. Just make sure ADF has access to the excel file.

Target Container Setup:

Create a second container (or reuse the same one) to store the converted CSV file. For consistency, I used:

Same Storage Account

└── input-excel-container

    └── Employee

        └── [CSV output will be placed here]

The target file is inserted when we process the pipeline using ADF.

Step 1: Create Linked Service in ADF

Set up a Linked Service in ADF to connect to your Blob Storage using User-Assigned Managed Identity. This ensures secure, credential-free access.

Step 2: Create Excel Dataset

Source: Azure Blob Storage

File Format: Excel

Parameters to define:

Container name

Directory path

File name (with .xlsx extension)

Sheet name (e.g., Sheet1)

Step 3: Create CSV Dataset

Source: Azure Blob Storage

File Format: CSV

Parameters to define:

Container name

Directory path

File name (with .csv extension)

Step 4: Build the Pipeline

Create a new pipeline in ADF.

Add a Copy Activity to the canvas.

Configure:

Source dataset → pass Excel parameters

Sink dataset → pass CSV parameters

Save and debug the pipeline.

Once the pipeline runs successfully, check your Blob Storage container to see a CSV file to be generated.

Output of Copy activity

Details tab of Output activity

Monitor the Copy Activity

Once the pipeline executes successfully, navigate to your Blob Storage container to verify that the CSV file has been created.

To see how the pipeline is built in ADF, including the configured Datasets and Linked Service, you can raise an access request for reader and developer access directly from my homepage. 

This will allow you to run the pipeline with your own hands and explore the setup interactively for 30 days of collaborative access.

Try It Yourself with a 30-Day Collaborative Access

Want to explore the full pipeline setup interactively? You can sign up for 30-day collaborative access to my Azure Portal and run the pipeline yourself. See how it's built and explore many exercises like this at Cloudnerchuko.in