How to seperate out data with multiple row column headers and load in different SQL table.

Rohit Sawane 46 Reputation points
2022-10-10T11:16:44.033+00:00

Hi Team,
I've one .tsv source file with data and multiple row column headers (header1, header2, and header3 are representing column headers and those headers eg header1, header2 etc.. can be considered as tables for our understanding purposes).
That data needs to be dynamically separated, then loaded in three respective tables (eg header1 source data should be loaded in header1 target table similarly for header2 and header 3 also). Please find attached sample file.
How can I address this problem using the ADF and DF?248969-multiple-row-column-headers.jpg

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

Accepted answer
  1. ShaktiSingh-MSFT 13,911 Reputation points Microsoft Employee
    2022-10-11T07:10:22.687+00:00

    Hi @Rohit Sawane

    Thanks for posting this question in Microsoft Q&A platform and for using Azure Services.

    As mentioned in the question, you want to accomplish 3 different targets with tsv source on the basis of first column which contains header data.

    The pipeline would like below:

    249160-image.png

    Connect to the source tsv:

    249206-image.png

    Add Conditional Split transformation and divide data into 3 outputs based on the first column having header information as shown below:

    249281-image.png

    Add 3 Sinks with different tables in the database:

    249254-image.png

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Rohit Sawane 46 Reputation points
    2022-10-21T03:50:58.323+00:00

    Hi @ShaktiSingh-MSFT @GeethaThatipatri-MSFT @PRADEEPCHEEKATLA-MSFT

    I appreciate your support and follow up to my request. I have accepted the solutions. Please consider my rating as 5-star for both solutions(for separate out data & default column header). I'm really sorry for replying back to you so late. Thank you @ShaktiSingh-MSFT for your contribution to solve the issue!

    Best,
    Rohit Sawane

    0 comments No comments