How to read file using ICS file adapter

In this blog, we are gonna to show you, how to use Oracle Integration cloud service file adapter to read a file from FTP location.

In my previous blog, I have explained how to configure file adapter in Oracle Integration Cloud Service.

Write file to FTP location

After this blog, we have learned how to use file adapter to read CSV file from FTP location.

Use Case: Develop a scheduled process that will read a CSV file from FTP location and will insert the CSV data into database table.

This Use case requires only simple 4 steps to complete:

1. Create FTP connection
2. Create DB connection
3. Create a Scheduled Integration
4. Activate & Run the Integration

Let's go step by step:

1. Create FTP connection: FTP connection will work as an Invoke to read data from CSV file. In one of my blog, I have already showcased how to create FTP connection. Please check it out before moving forward.
2. Create DB connection: DB connection will work as a Target point. In one of my blog, I have showcased how to create DB connection. Please check it out before moving forward.
3. Create a Scheduled Integration

• Login into ICS console
• Click on the Integration tile from ICS home page
• Click on Create button from upper right corner
• Select Orchestration pattern from the dialog box
• Enter Below information and click on Create button
• Select Scheduled radio button
• Enter Integration Name in What do you want to call your integration text box
• An Identifier would be picked up automatically from Integration name however you can edit it
• Let the Version as it is
• Enter the description in What does this integration do box
• Leave the package box as it is

• Click Invokes from right navigation, Select TEST_FTP_Conn, drag and drop the TEST_FTP_Conn just below Scheduled activity

• Enter Name and optionally the description. Click Next button

• Enter below information and click Next button
• Select Operation: In our case select Read File
• Select a Transfer Mode: Select ASCII
• Enter Input Directory: Enter directory location from where the file would be read
• Enter File Name: Enter 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 screen shot and click Next button

• Enter below information and Select Next button
• Select a new Delimited File: Select the sample Test.csv file(download from here)
• Enter the Record Name: Employees
• Enter the Recordset Name: Employee
• Select the Field Delimiter: Select Comma(,) as the CSV file is comma separated
• Character Set: Select ASCII
• Optionally Enclosed By: "

• Select Done button from Summary page
• Drag & Drop For Each activity from the Actions tab just below the ReadFile activity. This For Each will be used to iterate over the CSV file records

• Enter below information from the opened popup of For Each and click Done button
• Name: Enter name of the ForEach
• Repeating Element:  Expand $ReadFile -> SyncReadFileResponse -> FileReadResponse -> Employee from left panel and Drop the Employees element in Repeating Element box
• Current Element Name: Enter EmployeeRecord

• Click Invokes from right navigation, Select Oracle Database, drag and drop the TEST_DB_Conn under ForEach

• Enter below information and Click Next
• Enter endpoint name in What do you want to call your endpoint
• Select Run a SQL Statement from What operation do you want to perform drop down

• Enter below insert query in SQL query box and Click Validate SQL query to validate the entered query and Click Next button

INSERT INTO apps.xx_employee_info(employee_id, first_name,last_name,qualification,designation) values(#employee_id,#first_name,#last_name,#qualification,#designation)

• Click Done
• Open InsertDetails mapper and map the fields from left panel(EmployeeRecord -> Employee) to right panel(InsertDetailsInput). Click Validate and Close button 

• Click on the Action menu and Tracking button

• Drop startTime to the Tracking Field

This completes our integration. Close Save and Close button

    4. Activate the Integration

• Click on Activate button

• Select Enable Tracking and Include Payload check boxes and click Activate button
• Click on Action Menu and Click on Submit Now button

As soon as we click on the Submit Now button, integration will be submitted. Now see your database tables. Whatever record exist in the CSV file, it must be inserted into database table.

This is how we can use File adapter to read the CSV file.