Azure Data Factory merge 2 csv files with different schema

sachin gupta 376 Reputation points
2021-09-07T17:01:51.427+00:00

I am trying to merge the 2 csv files(in Azure data factory) which has different schema. Below is the scenario

CSV 1: 15 columns -> say 5 dimensions and 10 metrices(x1, x2,...x10) CSV 2: 15 columns -> 5 dimensions(same as above) and 10 metrices(different from above, y1, y2...y10) So my schema is different. Now I have to merge both CSV files so that only 5 dimensions comes with all 20 metrices.

I tried with Data Transformation using Select operation. That is giving me 2 rows in the merged file. One row with first 5 dimensions and 10 metrices and second row with next 5 dimensions and 10 metrices, which is incorrect as I am looking only for one row with 5 dimensions and all 20 metrics(x1,x2...x10, y1,y2...y10)

Any help is much appreciated on this issue

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

Accepted answer
  1. ShaikMaheer-MSFT 38,301 Reputation points Microsoft Employee
    2021-09-08T10:22:59.463+00:00

    Hi @sachin gupta ,

    Thank you for posting query in Microsoft Q&A Platform.

    You need to perform join using join transformation on both files and then use select transformation to select only desired rows.

    Please check below detailed example implementation.

    Step1: I have two csv files csv1(columns are dim1,x1,x2,x3) & csv2(columns are dim1,y1,y2,y3). I am going to generate file final with columns as dim1,x1,x2,x3,y1,y2,y3.

    130136-image.png

    Step2: Added both csv files as source transformations in dataflow.

    130174-source.gif

    Setp3: Join transformation to join both csv file columns. If you don't have unique column to join then add surrogate key transformation on both sources and then join based that key column.

    130137-join.gif

    Step4: Select transformation to consider only required columns.

    130215-selecttrans.gif

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

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

    • Please accept an answer if correct. 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.
    • If you are interested in joining the VM program and help shape the future of Q&A: Q&A Volunteer Moderators

0 additional answers

Sort by: Most helpful