An easy way to put your Excel file data into an SA...

kirill_smirnov · ‎12-25-2012

Introduction

As the overall maturity of external non-SAP systems grows, an average SAP PI consultant seldom meets a challenge of putting non-XML or non-structured data into an SAP system. However, as some of the external counterparts are still using conventional IT-systems (or even only MS Office-based solutions) and are unfamiliar with such popular terms as "SOAP" or "Web-Service", suitable workarounds should be found.

In this blog post I will describe an easy way to put data represented in "flat" table of Microsoft Excel 2003 file into your SAP system using SAP Netweaver Process Integration using Apache Software Foundation's libraries (refer to http://poi.apache.org for details).

Prerequisites

An external counterpart sends us an attached Excel file via e-mail. We assume that we know the following:

· The number of an Excel sheet(s) with the contents we need

· The number of a row which has the descriptions of our fields

· And, the last, but not least, that the data in Excel is represented as a flat table

Here is an example of an Excel file I am expecting:

	A	B	C
1	File provided by: ACME corp.
2	Date of file: 2012-12-12
3	ProductCode	ProductText	ProductUoM
4	131-A	Door frame type 131	Pieces
5	123-A	Door frame type 123	Pieces
6	123-B	Door type 123	Dozens

In this example I would like to bypass the description of creation of the Service Interface on the SAP ERP side.

Things to do: Java IDE

One of the most important parts of this conversion is the creation of Java-based mapping program. I strongly recommend you to use some Java-related IDE (such as Eclipse), however, seasoned Java experts might prefer some less monstrous development environment such as Windows Notepad J.

The aim is to create a Java mapping program which transforms an Excel file into an XML file of the following structure:

<item>

<ProductText>Door frame type 123</ProductText>

<ProductUoM>Pieces</ProductUoM>

</item>

<item>

…

</item>

</DataType>

1. At first, download binaries from http://poi.apache.org. Create a Java Class (for example, MapExcelToInternalTable), which extends com.sap.aii.mapping.api.AbstractTransformation.

Start your Java Coding by specifying packages that will be used:

import java.io.IOException;

import java.io.UnsupportedEncodingException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.List;

import com.sap.aii.mapping.api.StreamTransformationException;

import com.sap.aii.mapping.api.TransformationInput;

import com.sap.aii.mapping.api.TransformationOutput;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

Next, specify your global variables using in the (public void transform(TransformationInput in, TransformationOutput out) throws StreamTransformationException) method. I suggest that you use:

a. StringBuffer type variable for the contents of the output

b. Some constants which will help to form an XML message output

c. An ArrayList for the field names in an Excel file (and in XML message)

d. Variables for the following Java mapping program parameters. The first two of them will be used to specify parameters of an Excel file reading, the other ones should be used to form an output XML message correctly.

HeaderRowNumber = in.getInputParameters().getInt("HEADER_ROW");

SheetNumber = in.getInputParameters().getInt("SHEET");

TargetNamespace = in.getInputParameters().getString("NAMESPACE");

TargetDataType = in.getInputParameters().getString("TARGET_DATA_TYPE");

e. Variables for the processing of an Excel file using POI

When you are done with all the method-wide variables, it's time to start working with an Excel file itself. Start with the following:

try {

ExcelWorkBook = WorkbookFactory.create(in.getInputPayload().getInputStream());

}

catch (Exception e) {

// Implement suitable exception handling here

}

If your ExcelWorkBook object is “up and running”, start the analysis. Don’t forget to form your future XML file correctly:

Integer CurrentRow = null, CurrentColumn = null;

//Worksheet:

Sheet sheet = ExcelWorkBook.getSheetAt(SheetNumber);

// Going by rows:

Iterator rows = sheet.rowIterator();

CurrentRow = 0;

while (rows.hasNext()) {

// Put an opening <item> tag here

…..

//

XSSFRow row = (XSSFRow) rows.next();

Iterator cells = row.cellIterator();

CurrentColumn = 0;

CurrentRow++;

while (cells.hasNext()) {

// New cell starts:

XSSFCell cell = (XSSFCell) cells.next();

CurrentColumn++;

// Implement suitable data processing and output StringBuffer filling

…..

// Check whether current row contains field names:

// Headers is declared in this way: List<String> Headers = new ArrayList<String>();

if (CurrentRow == HeaderRowNumber)

{

if (CellContents != null){

Headers.add("");

StringBuffer ColName = new StringBuffer();

ColName.append(CellContents.toString());// Please note that this will work

// only if your field names contain only “XML legal” symbols. Otherwise,

// consider implementing some static method to transform the field names

// into a more suitable form (for example, replace spaces with underscores)

Headers.set(CurrentColumn-1, ColName.toString());

}

// And here’s how we can get the contents of a single Excel cell:

// CellContents is of the StringBuffer type

if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC)

{

if(DateUtil.isCellDateFormatted(cell)) // Date found

{

SimpleDateFormat DateFormatter = new SimpleDateFormat("dd.MM.yyyy"); // Specify the

// date format you need here

CellContents.append(DateFormatter.format(cell.getDateCellValue()));

}

else{

try{ // Try to get numeric value

CellContents.append(cell.getNumericCellValue());

}

catch(Exception exc)

{
CellContents.append(cell.toString());

}

else

{ // Neither numeric nor date. Simple string-like data:

CellContents.append(cell.toString());

};

}

//

…..

// Some more conversions if desired

// Add some new data to the output buffer

// Put a closing </item> tag here

…..

//

When practically all the job is done, you need to pass your StringBuffer contents to the out parameter of the transform method. Do the following, it’s really simple:

// Transporting OutputBuffer to out:

try { out.getOutputPayload().getOutputStream().write(OutputBuffer.toString().getBytes("UTF-8"));

} catch (UnsupportedEncodingException e) {

getTrace().addInfo("Exception while writing OutputPayload:");

} catch (IOException e) {

getTrace().addInfo("Exception while writing OutputPayload: IOException");

}

Things to do: Enterprise Service Repository

In SLD, and, later in ESR we have the following SWCV created and properly maintained:

MY_ERP of company.com – for SAP ERP-related Service Interfaces, Message Types etc.
MY_COM of company.com – for Operation Mappings, Message Mappings etc.

Note that in this scenario we do not use any SWCV for an external IT-system, and fairly soon you’ll see that we do not really need it.

After creating a suitable inbound Service Interface in MY_ERP SWCV, do the following:

1. In MY_COM SWCV create a data type and a message type to represent a data from an Excel file. The order of the fields does not make sense:

ExcelFileContents
--item	Occurrence: 0…n
---ProductCode	Type xsd:string
---ProductText	Type xsd:string
---ProductUoM	Type xsd:string

2. Create a Message Mapping to transfer data from one message (created in (1)) to another (message type specified in the created inbound Service Interface in MY_ERP)

3. Import the mapping program you’ve developed (as an imported archive). Don’t forget to import all the dependencies.

4. Create the operation mapping. Specify:

1. Source operation: use an imaginary Service Interface name (for example, “dummy_interface”). Do not use searchhelps, just insert your own data. Specify a namespace.

2. Target operation: use the Service Interface created in MY_ERP SWCV

3. Specify your freshly-imported Java program as the first step of the mapping, Message Mapping – as the second step

4. Don’t forget to specify the parameters from the Java program, and their bindings

5. Save and activate

You can now test the Operation Mapping by loading an Excel file as “Source XML” . Ignore errors, just import the file as it is.

Things to do: Integration Directory

Perform the standard operations to create Communication Channels, Receiver Agreements, etc. Do not forget to specify parameters for your Operation Mapping, as ignoring this, would lead to a malfunction of your integration process.

Résumé

In this blog post I have described quite simple transformation from an Excel to an XML file. However, more complicated techniques may be required if the file is not as “neat and tidy” as in my example.

Feel free to comment on this.