File Content Conversion CSV to XML Using B2B Integration Cockpit

There are a lot of instances when you need to transform plain file formats such as .txt .csv .des to XML formats in integration scenarios. SAP PI/PO has capabilities to convert plain file formats to XML formats such as adapter specific content conversion techniques, Plain2XML adapter modules, Java Mapping, etc., but these methods have a few drawbacks.

File adapter has capabilities of plain to XML content conversions using Message Protocol “File Content Conversion”, but this involves a heavy parameter-based configuration. Also, deep structures that have two- or three-level hierarchies are hard to handle. For deep record structures (more than two levels hierarchy), you might have to rely on additional Java mappings to transform the plain text to the desired XML context.

With the introduction of B2B Integration Cockpit, SAP has launched several valuable functions to create custom content conversions using a graphical user interface. EDI Content manager has capabilities to convert custom EDI formats, text files, CSV files to XML. You need to define the structure and patter on the file using  B2B Integration Cockpit graphical user interface. If you want to learn how to install B2B add-on read my previous blog post.

SAP Versions used in the illustration:

• SAP S4 HANA Fashion 1709
• SAP PO 7.5

Let’s assume you have a comma-delimited CSV file format which contains Purchase Order information. It has header information, carton (handling unit) information, and articles quantity included in each handling unit.

Input CSV file:

ENV,8713537000000,2890005419
HDR,351,8LE1425844,4070272,20170201
PAC,8000026615
LIN,1,8718824245416,30
LIN,2,8718824245461,6
LIN,3,8718824253985,3
PAC,8000026616
LIN,4,8718824245348,18
LIN,5,8718824253800,3
LIN,6,8718824253961,3
LIN,7,8718824254203,3
TOT,7,66

LIN segment should be an unbound segment under PAC segment. PAC segment itself has unbound maximum occurrences.

Desired XML output:

<?xml version="1.0" encoding="UTF-8"?><ns0:PurchaseOrder xmlns:ns0="urn:sap.com/testtool">
  <Envelope>
    <ID>ENV</ID>
    <Sender>8713537000000</Sender>
    <Receiver>2890005419</Receiver>
  </Envelope>
  <Header>
    <ID>HDR</ID>
    <MessageType>351</MessageType>
    <MessageNumber>8LE1425844</MessageNumber>
    <OrderNumber>4070272</OrderNumber>
    <RequestedDelDate>20170201</RequestedDelDate>
  </Header>
  <Carton>
    <ID>PAC</ID>
    <CartonNumber>8000026615</CartonNumber>
    <Articles>
      <ID>LIN</ID>
      <LineNumber>1</LineNumber>
      <EAN>8718824245416</EAN>
      <Quantity>30</Quantity>
    </Articles>
    <Articles>
      <ID>LIN</ID>
      <LineNumber>2</LineNumber>
      <EAN>8718824245461</EAN>
      <Quantity>6</Quantity>
    </Articles>
    <Articles>
      <ID>LIN</ID>
      <LineNumber>3</LineNumber>
      <EAN>8718824253985</EAN>
      <Quantity>3</Quantity>
    </Articles>
  </Carton>
  <Carton>
    <ID>PAC</ID>
    <CartonNumber>8000026616</CartonNumber>
    <Articles>
      <ID>LIN</ID>
      <LineNumber>4</LineNumber>
      <EAN>8718824245348</EAN>
      <Quantity>18</Quantity>
    </Articles>
    <Articles>
      <ID>LIN</ID>
      <LineNumber>5</LineNumber>
      <EAN>8718824253800</EAN>
      <Quantity>3</Quantity>
    </Articles>
    <Articles>
      <ID>LIN</ID>
      <LineNumber>6</LineNumber>
      <EAN>8718824253961</EAN>
      <Quantity>3</Quantity>
    </Articles>
    <Articles>
      <ID>LIN</ID>
      <LineNumber>7</LineNumber>
      <EAN>8718824254203</EAN>
      <Quantity>3</Quantity>
    </Articles>
  </Carton>
<Total>
    <ID>TOT</ID>
    <TotalArticles>7</TotalArticles>
    <TotalQuantity>66</TotalQuantity>
 </Total>
</ns0:PurchaseOrder>
<!--Converted in 25754304 nanoseconds-->

Input CSV row types and output XML segments correspond to each other as follows:

ENV – <Envelope>

HDR – <Header>

PAC – <Carton>

LIN – <Article>

TOT – <Total>

Prerequisite for Plain to XML content Conversion using B2B Integration Cockpit.

1. B2B add-on should be installed in your current PI server.
2. B2B add on is only compatible with PI/PO versions 7.1 or higher.
3. Authorization to access B2B cockpit.

If B2B cockpit is yet to be set up, follow my B2B Cockpit installation guide.

Access B2B Cockpit home using URL http://<host>:<port>/b2bic

Different Content Conversions and message types related to ANSI X.12, EDIFACT, EANCOM, VDA, Odette, and Plain formats are defined in ‘EDI Content Manager’.

Step 1: Create a Control Key Association.

Message types are bundled under Control Key/Version associations. All standard content delivered by SAP are available under Control Key ‘SAP’. For custom requirements always create your own custom Control Key.

To define custom Control Key, Go to Editor > Message Editor

Depending on the scenario, create Control Key with a meaningful name. This is a logical place holder to bundle your scenarios.

Step 2: Create Message as “Purchase Order”.

Next step is to create the message type of the content. In this example I am receiving the Purchase Order information in the file. Therefore, I am naming the message as ‘Purchase Order’.

Always assign a meaningful name (such as Business Object name) to the message  instead of a technical name.

The configuration of the Message parameters shown in the screenshot above is very important. These parameters define the file pattern, file format, and how B2B cockpit identifies the content.

• Starts With is the parameter that controls which content conversion scenario or Control Key association should be activated from the integration flow. Any file extracted through the B2B sender adapters with file content starting with ‘ENV’ will be identified as a file type ‘Purchase Order’. ‘ENV’ are the first three characters of the file content. Therefore, make sure to configure a unique value for each “starts with” parameter.
• Set Type is the parameter that identifies line types or row types in the file. It could be either a delimited file, fixed-length file, or a mixed file. If a plain file is a character-delimited file, use ‘CSV’ as ‘Set Type’, else ‘Fixed Length’ or ‘Mixed’.

• Field Separator parameter is activated only for ‘CSV’ or ‘Mixed’ file types. Purchase Order file is a comma-separated CSV file.
• New Line parameter defines how a new line feed is identified by the EDI Content Conversion modules in B2B.
• Document Start and Document End parameters define the start and the end tags of the target XML file.

Step 3: Create a Set (Row Types) of the Message.

Sets define the Row Types of the file. Purchase Order file has 5 different row types. I have configured the Row identifier and Set name as below.

• ENV – Envelope
• HDR – Header
• PAC – Carton
• LIN – Articles
• TOT – Total

To create new Set Types, Go to Create > Set from main menu area.

Similarly create other Set Types ‘Header’, ‘Carton’, ‘Articles, and ‘Total’.

Make sure to add the ‘Articles’ Set type as an Inner Set of ‘Cartons’ Set Type.

To add sub-level Row Types (Set Types), make use of menu option Update > Add Inner Set

Step 4: Add Fields to Sets.

The next step is to add Fields to each Set Type you defined in Step 3. All fields of each Row Type of the CSV file to define the structure of the Message.

Select the Set type and define fields of each row type using the menu option ‘Update’.

Length defines the maximum length of the field.

Repeat the step for all Set Types.

Step 5: Test Content Conversion

Once all Set Types and fields of Set Types are configured, the Message structures should display as below. All the fields are assigned to Row (Set) Types and complete structure of the CSV file and XML are defined.

‘Envelope’, ‘Header’, ‘Carton’ and ‘Total’ segments are on the same level. ‘Carton’ segment has one child segment ‘Articles’ which was added as an Inner Set.

Go to Plain to XML Converter and select  ‘Plain to XML’ to test the Control Key. Always upload the text files using ‘Import’ option to avoid formatting changes of the file due to copy-paste.

The next step of the B2B interface development using B2B Integration Cockpit is provided in my latest article.

If you have any questions about Plain to XML content conversion using B2B Integration Cockpit, please leave a comment below.