In this article, we are going to demonstrate how to merge the content of two CSV files (that don’t have a common field) into a single CSV file in Oracle Integration.

For example:

To complete this article, you must have the below pre-requisites:

  • Oracle Integration Instance
  • FTP Server

You also need to create the FTP connection using the FTP adapter. Look at this blog to know how to create an FTP connection in Oracle Integration.

Let’s get started and see how to achieve this integration.

Creating an integration to merge files

Following are the steps to merge two CSV files into a single file:

  1. Create a scheduled orchestration and provide the name as MergeTwoFiles.
  1. Drag and drop the FTP adapter and read both the source files one by one. Look at this video to know, how to read file.
  1. Drag and drop the FTP adapter to write the final file. Look at this video to know, how to write the file.
    (Use your expected output format as schema here)
  1. Following is the XSLT code inside the Write File mapper to map the fields:
<xsl:template match="/" xml:id="id_11">
              <nstrgmpr:WriteFile xml:id="id_12">
                    <ns28:DetailSet>
                          <xsl:for-each select="$Readone/nsmpr2:SyncReadFileResponse/ns26:FileReadResponse/ns24:UserSet/ns24:User">
                                <xsl:variable name="i" select="position()"/>
                                <ns28:Detail>
                                      <ns28:First>
                                            <xsl:value-of select="ns24:FirstName"/>
                                      </ns28:First>
                                      <ns28:Last>
                                            <xsl:value-of select="ns24:LastName"/>
                                      </ns28:Last>
                                      <ns28:Email>
                                            <xsl:value-of select="$ReadTow/nsmpr1:SyncReadFileResponse/ns23:FileReadResponse/ns19:EmailSet/ns19:Email[$i]/ns19:EmailId"/>
                                      </ns28:Email>
                                </ns28:Detail>
                          </xsl:for-each>
                    </ns28:DetailSet>
              </nstrgmpr:WriteFile>
        </xsl:template>

Code Explanation:

  • for-each XSLT function to iterate over each record in File1.csv
  • xsl:variable “i” to hold the position of each iterating record.
    Note: On using xsl:variable we cannot open mapper in design mode.
  • Now pass the respective position captured by xsl:variable “i” to Email-ID XPath to get the corresponding EmailID from File2.csv.
$ReadTow/nsmpr1:SyncReadFileResponse/ns23:FileReadResponse/ns19:EmailSet/ns19:Email[$i]/ns19:EmailId

Finally, enable business identifiers, activate the integration, and test the flow which should give you the desired result.

You can also watch the below video that will guide you step by step process of the same requirement:

Also, please subscribe to our YouTube channel to get the latest updates.

Thank you Maheedhar for writing this article.

Further Readings

FTP adapter in Oracle Integration Cloud(OIC)

XSLT for-each Function

How to create a BIP report in Oracle SaaS

Form region to insert a record into a table in Oracle APEX

Master-Detail Page in Oracle APEX

Cascading List of Values in Oracle APEX

Oracle APEX Interactive Grid with CRUD operation