Read large CSV file in Integration Cloud Service

I was trying to achieve one use case in which we are required to read large CSV file from FTP which is more than 1 MB. We was using FTP adapter to read file but integration got failed as there is FTP adapter limitation. FTP adapter is capable to read only file 1MB or less in ICS version 17.3.3.0.0

It might be possible the limit of reading file may increase in future release of  ICS versions.

Featured blogs

1. FTP Adapter
2. How to read file from SFTP
3. How to write file in SFTP

Let me demonstrate how to read large file in ICS.

ICS FTP adapter gives one nice feature Download to ICS which allows us to download large file from FTP location and save it on ICS server. Then read that file in chunks using Stage action and play with the data as you want.

Use Case: Develop a scheduled process that will read a large CSV file( > 1 mb) from FTP location and print the data into logs.

This Use case requires only 3 simple steps to complete:

• Create FTP connection
• Create a Scheduled Integration
• 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 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 Download to ICS. We have chossen this option as we want to download the large file from FTP location and save it to the ICS server for futher processing
• Select a Transfer Mode: Select ASCII
• Input Directory: Enter directory location from where the file would be read
• Specify File Name: Enter file name to be read
• Download Directory Path: Enter the path where you want to save your file at ICS server. In our case we have entered TestFile folder. If this folder doesn't exist on the server, folder will be created with this name.

• Click Done button
• Expand Actions section from right panel and drag & drop Stage action just below the ReadFile

Note: Stage action is beautiful component in ICS that allows us to read/write/list/zip/unzip files on ICS server. In this particular use, we will use Read File in Segments operation. We can't use Read Entire File operation as again there is limitation to read only <=1 MB file.

• Enter Name and click Next button

• Enter below information
• Choose Stage File Operation: Enter Read File in Segments
• Specify the File Name: Select pencil(Select Expression) icon and expand ReadFile -> DownloadFileToICSResponse -> DownloadResponse -> ICSFiles -> ICSFile -> Properties from right panel and drop filename element on the Expression input box

• Specify the Directory to read from: Select pencil(Select Expression) icon and expand ReadFile -> DownloadFileToICSResponse -> DownloadResponse -> ICSFiles -> ICSFile -> Properties from right panel and drop directory element on the Expression input box
• Segment Size: Enter integer value. Specifiy segment size of the file we want to read in chunks. Allows between 10 to 200 MB. Let's keep it 10 MB

• Select Create a new schema from a CSV file and click Next button
• Enter below information and click Next button
• Select the Delimited Data File: Choose the sample CSV file of the same structure that has been downloaded to ICS server
• Enter the Record Name: Enter Student
• Enter the Recordset Name: Enter Students

• Click Done button
• Expand Actions section from right panel and drag & drop For Each action under the ReadFileInChunk

• Enter below information from opened dialog box and click on Done button
• Enter Name: Name of the For Each action
• Repeating Element: Expand ReadFileInChunk -> ReadResponse -> Students -> Student and drop on Repeating Element
• Current Element Name: Enter current record name(CurrentStudent)

• Expand Actions section from right panel and drag & drop Logger action under the ReadStudent

• Enter below expression in the logger activity to print User Id and First Name

concat('User id ', $CurrentStudent/nsmpr1:Student/nsmpr1:UserId, ' First Name ' ,$CurrentStudent/nsmpr1:Student/nsmpr1:FirstName)

 

This completes the integration. Activate the integration from the Integrations page. Now keep the CSV file at FTP location and run the integration.

Download the logs file and open ics_server-diagnostic.log file. You will  notice that all user id and first name will be printed in the log file.