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 desired XML context.

With the introduction of B2B Integration Cockpit, SAP has launched several valuable functions to create custom content conversions using graphical user interface. EDI Content manger 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.

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:

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

Desired XML output:

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 Manger’.

Access 'B2B Content Manager' in B2B Cockpit home
Access ‘B2B Content Manager’ in B2B Cockpit home

 

Step 1: Create 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

Go to Plain->Message Editor
Go to Plain->Message Editor

 

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

Create control key in B2B plain content conversion
Create Control Key

 

Create Control Key in EDI Content Manager of B2B Cockpit
Create Control Key as ‘CSV_to_XML’

 

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.

Configure Message type.
Configure Message type

Configuration of Message parameters shown in the screen shot 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’.
Set Type of the message could be CSV, Fixed Length or Mixed
Set Type of the message
  • 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.

Create Set Types or Row Types of the Message
Create Set Types or Row Types of the Message

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

Make sure to add ‘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

Add Inner Row Type of a multilevel XML
Add Inner Row Type

 

Step 4: Add Fields to Sets.

Next step is to add Fields to each Set Type you defined in the Step 3. All 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 menu option ‘Update’.

Define Files of each Set (Row) type
Define Files of each Set (Row) type

 

Configure the filed
Configure the field

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 in the same level. ‘Carton’ segment has one child segment ‘Articles’ which was added as an Inner Set.

Complete Content Conversion for Purchase Order Message type
Complete Content Conversion for Purchase Order Message type

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.

B2B Content Conversion test tool
B2B Content Conversion test tool

 

B2B Content Conversion test tool
B2B Content Conversion test tool

I will write about Integration Directory and iFlow configuration in my next post. Stay tuned!

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

2 Comments

Add a Comment

Your email address will not be published. Required fields are marked *