Enter your keyword


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
    • 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
  • Activate the integration and execute the same
  • Open the integration instance and see the Activity steam. The file should be read successfully

    Leave a Reply

    Your email address will not be published.