Azure Data Factory merge 2 csv files with different schema

Question

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

Answer 1

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.

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

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.

Step4: Select transformation to consider only required columns.

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