Hi All,

Requirement: I want to insert .xlsx files into database table column of type VARBINARY.

Step 1: For above requirement I have created a stored procedure in database with below parameters.

Step 2: To hold .xlsx file as input, I have taken string as a datatype in sender side.

Step 3: As per the stored procedure syntax we created target data type.

Step 4: I have implemented Java mapping to convert .xlsx file to hex format.

Note:  If we want to insert binary files into database column, first we need to convert binary file into hex format i.e, by using java mapping we can read .xlsx file as a binary then convert into hex format using below java code.

package com.mappingfilestream;
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import com.sap.aii.mapping.api.AbstractTransformation;
import com.sap.aii.mapping.api.DynamicConfiguration;
import com.sap.aii.mapping.api.DynamicConfigurationKey;
import com.sap.aii.mapping.api.StreamTransformationException;
import com.sap.aii.mapping.api.TransformationInput;
import com.sap.aii.mapping.api.TransformationOutput;

public class MapFileStream extends AbstractTransformation
private StringBuffer sb;
private String digits = “0123456789ABCDEF”;

public void transform(TransformationInput in, TransformationOutput out)
throws StreamTransformationException

sb = new StringBuffer();
BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(out.getOutputPayload().getOutputStream()));
InputStream is = in.getInputPayload().getInputStream();

// Access dynamic configuration for getting file name
DynamicConfigurationKey KEY_FILENAME =
DynamicConfiguration conf = in.getDynamicConfiguration();
String filename = conf.get(KEY_FILENAME);

// Open inbound XML message stream
sb.append(“<?xml version=\”1.0\” encoding=\”UTF-8\”?>”);
sb.append(“<ns0:Mt_file_In xmlns:ns0=\”http://file_insert.com\”>”);
sb.append(“<FILE><FILE_SP action=\”EXECUTE\”>\r\n”);
sb.append(“<TABLE>NWPS_SHIPPER_REPORT_INSERT</TABLE><FileName type=\”VARCHAR\”>”+filename+”</FileName>”);
sb.append(“<FileData type=\”VARBINARY\”>”);

// Read binary file as input stream and convert to hex
byte[] bytes = toByteArrayUsingJava(is);
for (int i = 0; i < bytes.length; i++)
if(i == 1)
sb.append(“50”);//Inserted “50” in the beginning of hexadecimal stream for Extra Byte
int c = bytes[i] & 0xFF;
sb.append(digits.charAt(c >> 4));
sb.append(digits.charAt(c & 0x0F));
catch (IOException e)
* Read bytes from inputStream and writes to OutputStream,
* later converts OutputStream to byte array in Java.
public byte[] toByteArrayUsingJava(InputStream is) throws IOException{
ByteArrayOutputStream baos = new ByteArrayOutputStream();
int reads = is.read();

while(reads != -1){
reads = is.read();

return baos.toByteArray();

Step 5: Convert .java file into .jar and import into PI mapping.

Step 6: Create operation mapping by using java class.

Hope this was useful for any type of binary file insert into database table column.

Thank you,
Narasaiah T

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply