Share via

how to read excel file data (stored in adls gen2 ) in Azure Data Factory pipeline

Bathini, Shirish 20 Reputation points
2024-07-15T06:39:57.58+00:00

I have source excel file which is coming as showing in the format . Please help how to read excel file with that specified format . Please help . Thank You.

User's image

Azure Data Lake Storage
Azure Data Lake Storage
An Azure service that provides an enterprise-wide hyper-scale repository for big data analytic workloads and is integrated with Azure Blob Storage.
1,424 questions
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,665 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,110 questions
Accepted answer
  1. Nandan Hegde 31,111 Reputation points MVP
    2024-07-15T08:00:54.7233333+00:00

    You can connect to excel file as source dataset format with ADLS as the source.

    Within excel file, there is property called Range which helps you specify from which row number and column number you need to start reading the excel file data from.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Pinaki Ghatak 3,110 Reputation points Microsoft Employee
    2024-07-15T10:38:05.4666667+00:00

    Hello @Bathini, Shirish

    To read an Excel file stored in ADLS Gen2 in an Azure Data Factory pipeline, you can use the Excel connector in mapping data flows. The Excel format is supported for the ADLS Gen2 connector in mapping data flows.

    You can point to Excel files either using an Excel dataset or using an inline dataset. When using an inline dataset, you will see additional file settings, which are the same as the properties described in the dataset properties section of the Excel format in Azure Data Factory documentation.

    Here is an example of how to read an Excel file stored in ADLS Gen2 using an Excel dataset:

    1. Create a new dataset in your Azure Data Factory pipeline.
    2. Select the ADLS Gen2 connector as the source.
    3. In the dataset properties, set the file path to the location of your Excel file in ADLS Gen2.
    4. Set the file format to "Excel".
    5. Set the sheet name or index to the sheet you want to read.
    6. Save and publish the dataset.
    7. Use the dataset as the source in your mapping data flow.

    This should get you set up

    I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.

    0 comments