Read CSV file with Delimiters other than specified in the FTP adapter: Oracle Integration Cloud

In one of my article, we showcased how to read CSV file using FTP adapter which is comma separated. Integration Cloud FTP adapter allows below delimiters file to be processed out of the box

Single space
Comma(,)
Semicolon(;)
Tab
Pipe(|)

What else if the file is separated with other delimiters(like #, $, %, & etc..) as mentioned above.

In this article, we’ll tweak the integration little bit and show how to read a file which is hash(#) delimiter separated.

To complete the article, we should have an FTP connection in place. If this is not created already, create one with the help of this blog

Let’s see how to parse the file which is a hash(#) separated

Create a Scheduler integration with the name (ReadCSVTest)
Drop the FTP connection as an invoke activity
Enter Name and description and Click the Next button

Enter below information and click the Next button

Select Operation: Select Read File
Select a Transfer Mode: ASCII
Enter Input Directory: Enter directory location from where the file would be read
Enter File Name: Enter the file name

Select Yes radio button from Do you want to define a schema for this Endpoint and select Create a new schema from a CSV file option as shown in below screenshot and click the Next button

Enter below information and Select Next button

Select a new Delimited File: Select the sample Test.csv file which would have below content

FirstName,LastName

Ankur,Jain

Amit,Gupta

Enter the Record Name: Student
Enter the Recordset Name: Students
Select the Field Delimiter: Select Comma(,) for the time being
Character Set: Select ASCII
Optionally Enclosed By: “

Select the Done button from the next screen to finish the wizard
Save and Close the integration
Export the integration with the help of action menu parallel to the Integration name
Unzip the IAR file
Notice below two files in the folder hierarchy (READCSVTEST_01.00.0000icspackageprojectREADCSVTEST_01.00.0000resourcesapplication_15inbound_16resourcegroup_17)

ReadFile_REQUEST.jca
ReadFile_REQUEST.wsdl

Open the ReadFile_REQUEST.jca file and search the “RECORD_DELIMITER_KEY” and notice the comma(,) as a delimiter

Replace comma(,) with the delimiter hash(#). This can be any other delimiter specified in the file to be read
Open the ReadFile_REQUEST.wsdl and notice the “nxsd:terminatedBy” attribute of the elements

Replace comma(,) with the delimiter has(#) in all the elements except the last one (LastName). In our case we only have two elements(FirstName & LastName), so replace them(#) in the first element (FirstName) only. For example, if we would have three elements (FirstName, LastName & Age) then we would be requiring to replace comma in two elements (FirstName, LastName). Since the last element should always be terminated by “${eol}”
Once both the files are changed, save them in the IAR file and import IAR back into the Integration Cloud
Keep the Test.csv file with hash separated as below at FTP location

FirstName#LastName

Ankur#Jain

Nancy#Jain