How to consider 2nd or any other row as column header using dataflow

Question

Hi Team,

I have a .csv file with two rows: a column header in the second row, and data in rows 1 and 2 for that column header as shown in the figure below. I want to map this column header to a SQL table. Is there a method to use data flow to consider the second row to be the column header?

Could you kindly help me out with this.

Thanks in advance!

Answer 1

Hi @Anonymous ,

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

In order to achieve result mentioned in the dataset, making second row as Header, we could use "Skip Line Count" setting of Source Dataset of the input csv.

One way to achieve result could like below as overall mapping:

Take source dataset, skip line count=1, then Map Drifted columns in the next step, remove extra columns using Select transformation, result of Select should look like below:

Filter out ID=2 that is ID!='2' which comes in the above flow so that only Header(second row) is left:

Take Source Dataset again with no Skip Line count and First Row as Header unchecked:

Next, Filter out ID so that Header column (second row of original data) is removed:

Finally, Union data from both streams using "Union by Position" to get the desired result:

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