Merging multiple CSV files into one CSV file.

[Jayakrishna1]

Hi All,

we have requirement, we need to pick 20 CSV files from SFTP folder to CPI and Combine these files into one CSV file and perform mapping and send the output file to Target SFTP folder.

Question: How can i pick 20 CSV files at once and how to combine them into one file?

If anyone worked on this case please share the pointer to how to achieve this.

Regards,

Jayakrishna.

[Ahmad1]

Hi Jaya Krishna,

Please use this Groovy Script.

import com.sap.gateway.ip.core.customdev.util.Message

import java.nio.charset.StandardCharsets

def processData(Message message) {

def sftpAdapterNamespace = "http://sap.com/xi/XI/System/FileAdapter"

def sftpAdapterPrefix = "sftp"

// Define the folder path where the CSV files are located in the SFTP server

def folderPath = "/path/to/csv/folder"

// Define the output file name and location in the SFTP server

def outputFileName = "/path/to/output/merged.csv"

// Get the SFTP adapter parameters from the message header

def sftpParameters = message.getProperty(sftpAdapterNamespace, sftpAdapterPrefix)

// Retrieve the file name from the SFTP adapter parameters

def fileName = sftpParameters.get("FileName").toString()

// Retrieve the file content from the message body

def fileContent = message.getBody(byte[])

// Convert the file content to a string

def fileContentString = new String(fileContent, StandardCharsets.UTF_8)

// Write the file content to the output file in the SFTP server

def outputStream = sftpParameters.get("outputStream")

outputStream << fileContentString

// Loop through the remaining files in the folder

def sftpServer = sftpParameters.get("Server")

def sftpPort = sftpParameters.get("Port")

def sftpUsername = sftpParameters.get("UserName")

def sftpPassword = sftpParameters.get("Password")

def jsch = new com.jcraft.jsch.JSch()

def session = jsch.getSession(sftpUsername, sftpServer, sftpPort)

session.setPassword(sftpPassword)

session.setConfig("StrictHostKeyChecking", "no")

session.connect()

def channel = session.openChannel("sftp")

channel.connect()

def sftpChannel = (com.jcraft.jsch.ChannelSftp) channel

sftpChannel.cd(folderPath)

sftpChannel.ls(folderPath).each { file ->

// Skip if the file is the output file or not a CSV file

if (file.getFilename() == outputFileName || !file.getFilename().endsWith(".csv")) {

return

}

// Read the file content

def inputStream = sftpChannel.get(file.getFilename())

def fileContentBytes = new byte[inputStream.available()]

inputStream.read(fileContentBytes)

inputStream.close()

// Convert the file content to a string

def fileContentString = new String(fileContentBytes, StandardCharsets.UTF_8)

// Append the file content to the output file in the SFTP server

outputStream << "\n" << fileContentString

// Delete the processed file (optional)

sftpChannel.rm(file.getFilename())

}

// Set the output file name as the file name in the SFTP adapter parameters

sftpParameters.put("FileName", outputFileName)

// Set the merged file content as the message body

message.setBody(outputFileName)

// Disconnect the SFTP channel and session

sftpChannel.disconnect()

session.disconnect()

}

Thanks & Regards,

Ahmad

[Jayakrishna1]

Hi Ahmad,

Thanks for your response.

I have a question. After merging the files the output file will be placed in the sftp server right. can it also store in ${in.body} can i use it in further steps in iflow?

Thanks & Regards,

Jayakrishna.