Azure Data Factory - copy data from Excel file with nested headers into Azure SQL table

Pratim Das, Partha C 346 Reputation points
2023-12-14T05:05:21.7433333+00:00

Hi,

I'm facing a problem with ADF copy activity.

Problem Scenario:

I'm receiving an xlsx file from a HTTP linked service

This xlsx file has nested header as below

xlsx_content

Using Copy data activity I'm trying to store this data in Azure SQL where the table structure is as below and content should look like

AzuresqlTable

Could you please guide me how I can accomplish this?

Thanking you in advance for treating it as urgent.

Regards,

Partha

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,226 questions
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 34,011 Reputation points Microsoft Employee
    2023-12-19T09:01:26.8433333+00:00

    Hi Pratim Das, Partha C ,

    Thankyou for using Microsoft Q&A platform.

    I understand that you want to perform column mapping between excel sheet which is the source in your scenario to the SQL DB which is the sink using copy activity.

    You can navigate to the mapping tab and define the column mapping by unchecking the 'auto-mapping' option and mapping the columns manually , assuming that the sink table is already created with the defined schema that you have mentioned above.

    Reference resource: Schema Mapping in Copy activity of ADF pipeline

    If you want to copy multiple files to multiple tables, then you can go for dynamic column mapping as well.

    Here is more details on it: Dynamic Column mapping in Copy Activity in Azure Data Factory

    Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Nandan Hegde 34,021 Reputation points MVP
    2023-12-14T05:13:49.0366667+00:00

    Hey,

    You can use the advanced property within the excel dataset range function

    wherein you can start the range from A2. :

    https://stackoverflow.com/questions/70786748/azure-data-factory-import-excel-file-with-dynamic-range2


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.