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

Question

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?

Answer 1

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:

Connect to the source tsv:

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

Add 3 Sinks with different tables in the database:

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

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

• Please don't forget to click on or upvote 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

Answer 2

Hi @ShaktiSingh-MSFT @GeethaThatipatri-MSFT @PRADEEPCHEEKATLA

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