OIC | Merge two CSV files into one CSV file based on a common field or primary key

Use Case: Here,we will merge two CSV files into one final file based on a common primary key or common field values.

In the below example, we will use EmpId as primary key in both the CSV files for merging.

EmpDetail.csv

EmpId,FName,LName

101,sid,das

102,dip,chak

103,sri,das

EmpContact.csv

EmpId,Contact

101,101_123456

102,102_234567

101,102_987654

103,103_456789

Merged file:

EmpId,FName,LName,Contact

101,sid,das,101_123456 102_887654

102,dip,chak,102_234567

103,sri,das,103_456789

Highlevel steps:

1. Read both the CSV files using FTP adapter one by one.
2. For each emp record of 1st CSV file, we will Map both the files and write the Merge file using FTP adapter. We will use a condition to match the records of the both the file using the EmpId of both the files.

Steps in detail:

Integration flow:

Once both files read, we will loop for each emp record of the 1st file.

Map the current emp details and map the contact using following condition:

"$readEmployeeContact/nsmpr2:SyncReadFileResponse/ns24:FileReadResponse/ns22:EmpContactRecSet/ns22:EmpContactRec[ns22:Empid=$currentEmp/nsmpr1:EmpDataRec/nsmpr1:EmpId]/ns22:Contact"

Test:

Note: instead of taking a for each action, we can also do the same in xslt itself:

Xslt code:

<nstrgmpr:WriteFile xml:id="id_12">

<ns30:Employees>

<xsl:for-each select="$readEmployeeData/nsmpr2:SyncReadFileResponse/ns27:FileReadResponse/ns26:/EmpDataRecSet/ns26:/EmpDataRec">

<xsl:variable name="EmpId" select="ns26:/EmpId"/>

<ns30:Employee>

<ns30:C1>

<xsl:value-of select="ns26:EmpId"/>

</ns30:C1>

<ns30:C2>

<xsl:value-of select="ns26:FName"/>

</ns30:C2>

<ns30:C3>

<xsl:value-of select="ns26:LName"/>

</ns30:C3>

<ns30:C4>

<xsl:value-of select="$readEmployeeContact/nsmpr1:SyncReadFileResponse/ns23:FileReadResponse/ns20:/EmpContactRecSet/ns20:/EmpContactRec[ns20:EmpId=$EmpId]/ns20:Contact"/>

</ns30:C4>

</ns30:Employee>

</xsl:for-each>

</ns30:Employees>

</nstrgmpr:WriteFile>