Problem Description

Convert Excel to CSV Customers/Users face issues with loading Excel files from SFTP remote server to local machine and convert the excel file to CSV file in mapping on IICS.

Challenges to User/Developer

In IICS, the above-mentioned problem can be solved through Java Transformation.
The Excel file needs to be transferred to the local machine (where the security agent is installed).
The locally available Excel file needs to be converted to a CSV file.
Move the original Excel file which we got from SFTP remote server to another location.

Solution:

Approach –

Excel to CSV using SFTP In Informatica Intelligent Cloud Services (IICS)

Convert Using Java Transformation, we can achieve the solution for the above-mentioned challenges.

Java transformation is used to add custom integration logic into mappings using a built-in developer-friendly editor or by plugging in custom JAR files.

Technical Solution –

Develop Java code that performs Excel files transfer from SFTP to Local machine and converts Excel file to CSV file using IDE (ex. Eclipse)
Create an Executable jar file for the above-developed java code.
Place the above-created jar file at any directory at the local machine (where the security agent is installed).
Copy the above directory path including the jar name with extension.
Create the CLASSPATH Environment variable if not available.
Paste the above-copied directory to the CLASSPATH Environmental variable.

Restart the secure agent.
Create the mapping using source and target with java transformation in between them.

Edit java transformation to make a call to the specific method of the class available in a jar which is responsible for transferring the file through SFTP to Local machine and converts Excel file to CSV file.