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

Rohit Rajendra Sawane 96 Reputation points
2022-11-07T09:33:17.443+00:00

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!257861-header-issue.png

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

Accepted answer
  1. ShaktiSingh-MSFT 13,996 Reputation points Microsoft Employee
    2022-11-08T09:33:53.15+00:00

    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:

    258192-image.png

    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:

    258090-image.png

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

    258167-image.png

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

    258194-image.png

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

    258196-filter.gif

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

    258180-union.gif

    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

0 additional answers

Sort by: Most helpful