OIC | Split a CSV file into multiple CSV files based on a column distinct values in Oracle integration cloud

Use Case : We have a Source CSV file containing rows for different BU values. Based on the BU column, we will create separate CSV files for each unique Business Unit values. For instance, for "N" unique BU values, we will segregate them into "N" of CSV files.

Example,

Source file:

CompanyName,CompanyID,BU,Location

abc,101,BU1,US

cde,105,BU2,IND

efg,501,BU3,PH

ghi,102,BU1,US

ijk,101,BU2,IND

Outputs:

We have 3 Unique BUs= BU1,BU2,BU3 so we should have 3 files segregation.

Output files:

BU1.csv

abc,101,BU1,US

ghi,102,BU1,US

BU2.csv

cde,105,BU2,IND

ijk,101,BU2,IND

BU3.csv

efg,501,BU3,PH

Highlevel steps:

1. Create a scheduled orchestration and provide a name.
2. Drag and drop the FTP adapter and use Read a file operation to poll the source file.
3. Drag and drop the Stage file action just after the FTP adapter and configure a Write a file operation. Use for-each-group and group by element to fetch unique BU values in the map.
4. Drag and drop the Stage file action and Read a file operation to read distinct BU file.
5. Drop a for-each action and use the repeating element from the stage file read action.
6. Drag and drop an FTP connection inside this loop to write the records of each BU independently.

Steps in detail:

Integration flow:

Read file using FTP connection and using a sample CSV source file.

Take a stage and write Distinct BUs in a file using for-each-group and group by elements.

Map codes:

<xsl:template match="/" xml:id="id_12">

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

<ns23:BUs>

<xsl:for-each-group select="$readFile/nsmpr1:SyncReadFileResponse/ns18:FileReadResponse/ns18:Companies/ns18:Company" group-by="ns18:BU">

<ns23:BU>

<ns23:BUColumn>

<xsl:value-of select="ns18:BU"/>

</ns23:BUColumn>

</ns23:BU>

</xsl:for-each-group>

</ns23:BUs>

</nstrgmpr:Write>

</xsl:template>

Take a Stage and read the BUs file.

Take a for-each and for each unique BUs

Write Split files using FTP adapter 

Map the following 3 things:

1. File name generation: cutrentBU >> BU>> C1 to OutboundFTPHeaderType >> fileName

Concat($currentBU/nsmpr1:BU/nsmpr1:C1,".csv")

2. Repeat the company element and for 1st company , hardcode header values.

3. For ReadFile Response (FTP) , map each company elements to target comlany element with the following condition:

$readFile/nsmpr3:SyncReadFileResponse/ns25:FileReadResponse/ns23:Companies/ns23:Company[$currentBU/nsmpr1:BU/nsmpr1:C1=ns23:BU]