Tuesday, 23 April 2019

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.0000\icspackage\project\READCSVTEST_01.00.0000\resources\application_15\inbound_16\resourcegroup_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
  • Activate the integration and execute the same
  • Open the integration instance and see the Activity steam. The file should be read successfully

    Saturday, 13 April 2019

    Change date time format in Oracle Integration Cloud

    We got a request from multiple folks saying they are struggling to change the date time format in Oracle Integration Cloud.

    We are writing this article to see how to change the default date format(2019-04-13T05:09:05.049+00:00) to a different format.

    Oracle Integration Cloud Service provides an Out of the box function(xp20:format-dateTime) which allows us to change the date time format. We are on ICS version (18.4.3.0.3) and the function is available(xp20:format-dateTime)

    Let us see how to apply this function and change the date format as per the requirement

    The function(xp20:format-dateTime) takes below two arguments:
    1. dateTime: Provide the DateTime input parameter as a string which needs to be the format
    2. Format: Provide the format as a string as per the requirement
    Example-1: Parse the date time in the format "dd-MM-yyyy", below will be the syntax:

    xp20:format-dateTime(dateTime,'[D01]-[M01]-[Y0001]')

    Example-2: Parse the date time in the format "dd-MM-yyyy HH:mm:ss", below will be the syntax:

    xp20:format-dateTime(dateTime,'[D01]-[M01]-[Y0001] [H01]:[m01]:[s01]'

    Example-3: Parse the date time in the format "MM/dd/yyyy HH:mm:ss", below will be the syntax:

    xp20:format-dateTime(dateTime,'[M01]/[D01]/[Y0001] [H01]:[m01]:[s01]')

    Example-4: Parse the date time in the format "yyyy-MM-dd h:mm AM/PM", below will be the syntax:

    xp20:format-dateTime(dateTime,'[Y0001]-[M01]-[D01] [h1]:[m01] [P]')

    Example-5: Parse the date time in the format "HH:mm TimeZone", below will be the syntax:

    xp20:format-dateTime(dateTime,' [H01]:[m01] [z]')

    Example-6: Get the ordinal value of the day, below will be the syntax:

    xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [Dwo]')

    Example-7: Get the name of the Month in the capital, below will be the syntax:

    xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [MNn]')

    Example-8: Get the day of the week, below will be the syntax:

    xp20:format-dateTime(/nsmpr0:schedule/nsmpr0:startTime,' [FNn]')

    Below are the explanations for all the formatting codes in the example:

    Value
    Description
    D01
    Produces the two-digit day
    M01
    Produces the two-digit month
    Y0001
    Produces the four-digit year
    H01
    Produces the 2-digit hour in a 24-hour clock
    m01
    Produces the 2-digit minutes
    s01
    Produces the 2-digit seconds
    h1
    Produces the 1- or 2-digit hour in a 12-hour clock
    P
    Generates a.m. or p.m.
    z
    Produces the time zone
    MNn 
    Generates the capitalized name of the month.
    For example: If we are sitting in April month, it will produce “April
    FNn
    The capitalized name of the day of the week
    For example: If today is Saturday, it will produce “Saturday
    Dwo
    The word for the ordinal value of the day
    For example: If today is 13th April, it will produce “thirteenth
    • Let's go ahead and create a scheduler process in ICS and create 8 variable as per the example was given above

    • Print all 8 variables in the different logger. Activate the scheduler and execute
    • Open the activity stream and see the values of all 8 variables. Notice the start time(since we have given the start time as an input of the function) and accordingly the result of each format. 


    Saturday, 6 April 2019

    Download CSV file from REST service Response Content: Oracle Integration Cloud

    There are certain REST Services which provides the CSV file as part of the response and needs to consume in the Integration Cloud. In the article, we'll demonstrate how to catch the CSV file from REST service response leveraging Integration Cloud REST Adapter.

    In the below Screenshot, when we hit the REST Service in the browser URL box then it downloads a CSV file.



    Let's go step by step and see how to achieve this use case
    • Create a Scheduler with the name(DownlaodCSV)
    • Drop the REST service connection and configure the below option on the initial page
      • What do you want to call your endpoint: DownloadFile
      • What is the endpoint's relative resource URI?: /{path}
      • What action does the endpoint perform: GET
      • Check the checkbox Add and review parameters for this endpoint
      • Check the checkbox Configure this endpoint to receive the response 
    • Click the Next button on the next screen
    • Select Raw Radio button of the Select the response payload format. Click the Next button and finish the wizard
    • Open the edit mapper and configure the template parameter as per the REST service
    • Drop the Stage action just below the REST adapter and enter "WriteToStage" in What do you want to call your action? text box
    • Enter the below information and select the Next button
      • Choose Stage File Operation: Write File
      • Specify the File Name: test.csv
      • Specify the Output Directory: /stage
    • Select the "Select an existing schema from the File system" radio button
    • Past the below schema in notepad file, save it and select using the "Choose File" option
    <?xml version = '1.0' encoding = 'UTF-8'?>
    <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/"
                xmlns="http://www.w3.org/2001/XMLSchema" >
          <element name="opaqueElement" type="base64Binary" />
    </schema>

    • Edit the "WriteToStage" mapper, click on the "opaqueElement", drop the "encodeReferenceToBase64()" function and then drop the "streamReference" element from left panel on the function "encodeReferenceToBase64()"
    • Drop another "Stage" activity, and enter "ReadFromStage" in What do you want to call your action? text box
    • Enter the below information and select the Next button
      • Choose Stage File Operation: Read Entire File
      • Specify the File Name: test.csv
      • Specify the Output Directory: /stage
    • Select "Create a new schema from a CSV file" Radio button and click the Next button
    • Configure below option and click the Next button
      • Select a new Delimited Data File: Choose the sample CSV file which must be of the same type as REST service response
      • Enter the Record Name: Record
      • Enter the Recordset Name: Records
    • Finish the wizard. Integration is completed and ready to test now. Save and Close the integration and simply Submit
    Open the "DownloadCSV" instance and open the activity stream. The CSV data should be printed into the payload section