Version 22.0.8473

• Overview
• EDI Mapping in CData Arc
  • Converting To and From XML
  • Transforming XML
  • Flattening a Purchase Order
  • Summary
• Creating the Mapping Flow
  • Step 1: The X12 Connector
    • Configuring the X12 Connector
    • Upload a Test File
  • Step 2: The CSV Connector
    • Configuring the CSV Connector
    • Upload a Test File
  • Step 3: The XML Map Connector
    • Configuring the XML Map Connector
    • Understanding the XML Structures
• Creating the Mapping
  • Creating Foreach Loops
  • Mapping Leaf Nodes
    • Simple Leaf Node Mappings
    • Conditional Mappings with Lookahead Syntax
    • Modifying and Calculating with the Expression Editor
• Sample Documents
  • X12 850
  • CSV

Overview

CData Arc contains powerful data transformation tools that support mapping EDI documents (X12, EDIFACT, etc) to flat file structures like CSV files (Comma-Separated Values). This guide will focus on the process of mapping an X12 850 document (Purchase Order) to CSV format. The approach described in this article is also applicable to mapping other EDI documents, however the specific relationships between mapped values may be different.

This guide will begin with an overview of how Arc handles EDI mapping, then walk through the process of creating the mapping flow in the application. The sample input (X12 850) and output (CSV) documents are included at the bottom of this page.

EDI Mapping in CData Arc

Converting To and From XML

Arc uses XML as an intermediary format for data transformation and manipulation. The first step in most mapping projects is to convert the source file format (in this case X12) into XML, and the last step in most mapping projects is to convert XML into the destination format (in this case CSV).

Since translating to and from XML is such an important step in data transformation, Arc includes many dedicated connectors for translating files to and from XML. This guide will use an X12 Connector and a CSV Connector. These connectors automatically convert their respective file formats into XML and vice versa. Using these connectors, both the source format (an X12 850 Purchase Order) and the destination format (a CSV file with the desired columns) can be modeled as XML.

Transforming XML

Once the source format and the destination format have both been modeled as XML, the task is to convert one XML structure into another. This is accomplished by the powerful XML Map Connector.

The XML Map Connector requires a sample Source File and Destination File, which represent the starting XML structure and ending XML structure respectively. After these have been specified, the XML Map Connector’s visual designer is populated with the two XML structures.

Inside the XML Map Connector’s visual designer, the elements from the source structure can be dragged and dropped onto the elements in the destination structure to create a mapping relationship between them. This mapping step requires an understanding of the data within the source file so that the appropriate elements are included in the output file.

The XML Map Connector includes value formatters, conditional logic, and even custom scripting to manipulate and compute data during the mapping process.

Once the mapping relationship has been created, the XML Map Connector can automatically convert any files that match the Source XML structure into a file with the Destination XML structure. In this example, this means that the XML model of an X12 850 document will automatically be converted into an XML model of a CSV file. The final step is to take this XML model of a CSV file and convert it to an actual CSV file, which is easily accomplished with the CSV Connector.

Flattening a Purchase Order

A specific conceptual challenge when mapping an 850 Purchase Order to CSV is the need to ‘flatten’ the Purchase Order. A single 850 document may contain multiple orders, and each order contains varying numbers of line items. CSV files, in contrast, have a static number of columns with which to store this data.

Since the number of CSV columns cannot expand to accommodate the varying number of orders in an 850, an 850 cannot be mapped to a single record (row) in a CSV file. Similarly, since the number of CSV columns cannot expand to accommodate the varying number of line items in an order, an order cannot be mapped to a single record (row) in a CSV file. Each record in the CSV must store the data for a single line item within an order, since a single line item can be represented as a static set of values regardless of the number of line items and orders in an 850. ‘Flattening’ an 850 refers to taking its multi-layered and variable structure and reducing it to a set of static line items.

The XML Map Connector uses the concept of a Foreach looping relationship to flatten structures during a mapping. In this case, a new CSV record should be created “for each” line item in the 850. Establishing this relationship within an XML Map Connector is simply a matter of using the visual designer to drag one element (the ‘PO1’ element that represents a line item) onto another element (the element that represents a CSV record/row). Flattening structures with the XML Map Connector is thus very easy, but does require an understanding of the source and destination XML structures so that the appropriate Foreach relationship is established.

Summary

In summary, creating a mapping in Arc requires these steps:

• Convert a sample input file (e.g. an X12 850) to XML using the connector specific to the input format (e.g. the X12 Connector)
• Convert a sample output file (e.g. a CSV file with the desired columns) to XML using the connector specific to the output format (e.g. the CSV Connector)
• Use the resulting two XML files as the Source File and Destination File respectively for an XML Map Connector
• Drag and drop elements in the XML Map Connector’s visual designer to establish a relationship between the source XML structure and destination XML structure

The result is an automated flow that accepts X12 documents as input and returns CSV files as output.

Creating the Mapping Flow

This section contains each step to create a Flow in Arc to map an example 850 Purchase Order into a flat CSV file.

Step 1: The X12 Connector

The first step of a mapping flow is to convert an example source document (an X12 850) into XML.

This is accomplished with the X12 Connector, which can convert X12 documents into XML and optionally validate X12 interchange headers during the conversion.

In the Flows page, drag an instance of the X12 Connector into the canvas. X12 Connector instance names should typically include the name of the trading partner sending X12 documents (such as Amazon):

Configuring the X12 Connector

Within the Settings Tab of the X12 Connector, the Translation Type property should be set to ‘X12-to-XML’. When operating in this mode, the X12 Connector can validate X12 interchange headers (to ensure that the X12 documents were sent to and received by the expected parties) as it converts the X12 document into XML format. Validation is not performed if the Usage Indicator property is set to ‘T-Test Data’.

If interchange header validation should be performed, configure the Sender Identifier and Receiver Identifier properties in both the Interchange Settings section and Functional Group Settings section. Sender Id Qualifier and Receiver Id Qualifier may additionally be specified to provide context to the identifier values.

No further configuration is necessary for the scope of this article, but may be required for your specific use case. The full documentation for the X12 Connector can be found here.

Upload a Test File

Navigate to the Input Tab of the X12 Connector, and find the More dropdown. This dropdown includes an Upload Test File option:

Use this option to browse on disk for an example 850 document that contains the X12 segments necessary for the mapping. It may be necessary to contact your trading partner to acquire an example X12 document if one is not already available. The sample 850 document used to make this guide is provided at the bottom of this article.

The Upload Test File option instructs the X12 Connector to save an XML model of the uploaded X12 document internally (instead of pushing the XML to the Output Tab as an output file). This XML model will be used later by the XML Map Connector.

Step 2: The CSV Connector

The next step in the mapping flow is to convert an example destination document (a CSV file with the appropriate columns) into XML. This is accomplished with the CSV Connector, which translates files between XML and CSV.

Drag an instance of the CSV Connector into the Flow. This connector should not be connected to the X12 Connector from the previous step.

Configuring the CSV Connector

The only CSV Connector configuration property required for this mapping flow is Column headers present. This should be enabled if the CSV files produced by the mapping flow should have a row of column header names at the top of the file. The full documentation for CSV Connector configuration can be found here.

Upload a Test File

Navigate to the Input Tab of the CSV Connector, and find the same Upload Test File option described in the X12 Connector section. Use this option to browse on disk for an example CSV file that contains the appropriate columns (and column headers if Column headers present is enabled). The sample CSV file used to make this guide is provided at the bottom of this article.

After uploading a test file, the CSV Connector will save an XML model of the uploaded file. This XML model will be used in the next step.

Step 3: The XML Map Connector

Now that the X12 Connector and CSV Connectors have generated XML models of the source and destination documents, the XML Map Connector is required to transform one XML structure into the other.

Drag an instance of the XML Map Connector onto the flow, then connect the X12 Connector to the XML Map Connector and the XML Map Connector to the CSV Connector.

Note: Remember to save the changes to the Flow in the bottom-right corner of the Flows canvas (close any open connector configuration panels to see the blue save icon).

Configuring the XML Map Connector

Inside the XML Map Connector configuration panel, find the dropdown for Source File and Destination File. The XML Map Connector should detect the XML model from the X12 Connector test file as an available Source File, and the XML model from the CSV Connector test file as an available Destination File. If either option is not available, verify that the test files were appropriately uploaded, that the connectors are connected in the Flow canvas, and that the Flow changes were saved via the blue save icon in the bottom-right of the Flows page.

Once the Source File and Destination File are specified, the XML Map visual designer will automatically populate with the XML structures (and test values) of the X12 850 and the CSV file.

Understanding the XML Structures

Creating the right mapping relationships in the XML Map Connector requires understanding how the source and destination structures are represented in XML.

First, there are two types of XML elements (nodes) in the structures. Parent nodes are nodes with children but no value. Leaf nodes are nodes with values but no children. A Parent node can be expanded (to show its children) or contracted (to hide its children) with the + or - signs next to it.

In the 850 XML, each EDI loop and segment is a Parent node. Each individual EDI element is a Leaf node.

In the CSV XML, each direct child of the root Items element is called a record element: it represents a distinct row/record in the CSV. Each record element is a Parent node, and the children of record elements are all column elements: they each represent a single field/column of a record. Each column element is a Leaf node.

In the XML Map Connector, Parent nodes can only be mapped onto other Parent nodes, and Leaf nodes can only be mapped onto other Leaf nodes. The primary challenge when mapping EDI to CSV is to choose the correct loop or segment element (from the EDI source) to drag onto the record element (in the CSV destination). Then, the EDI element nodes can be dragged onto the column nodes to populate the CSV with specific values from the EDI document.

Creating the Mapping

Once the XML Map Connector has been configured with the Source and Destination structures, the mapping can be created by dragging and dropping nodes in the visual designer.

Mappings are created in two primary steps:

1. Creating the Foreach looping relationships between Parent nodes
2. Mapping values between Leaf nodes within the established Foreach loops.

Creating Foreach Loops

Foreach relationships mean that each time an element appears in the source, a new XML structure should be created in the destination. If ElementA in the source is mapped to ElementB in the destination, each occurrence of ElementA in an input file will result in an instance of ElementB (and all of ElementB’s children) in the output file.

In this case (as described in the Flattening a Purchase Order section), each line item in the source 850 should result in a new record in the destination CSV. To create this relationship, drag the Source element that represents a Line Item onto the Destination element that represents a CSV record. In this case, the Source element is Orders, and the Destination element is Fulfillment:

As shown in the above image, the Fulfillment destination element displays (in green) the xpath from the source that maps to it. Every time the XML parser finds an element that matches the green xpath in an input file, a new Fulfillment element is added to the output file (including all of the Fulfillment element’s children, if any).

This Foreach loop is the only loop required for this mapping. Other mapping projects may require multiple loops, nested or otherwise.

Mapping Leaf Nodes

Once the Foreach relationships have been established, the values from Leaf nodes can be mapped within the loops. Mapping Leaf nodes determine the values that populate the XML structures created by Foreach loops.

Simple Leaf Node Mappings

When a value from the source should be directly inserted in the resulting output, simply drag a Leaf node from the source onto a Leaf node in the destination to map the values. For example, the PO2 element holds the value for Available, which corresponds to the Available attribute in the destination:

Note that the xpaths for these elements are relative to the Foreach xpath.

Some values are not specific to a particular line item and are mapped from outside the PO2 element:

• the PO1/RestockDate element holds the date for the restock date
• the PO1/FulfillmentLatency element holds the value for the fulfillment latency

Note that the xpaths for these elements might begin with ‘..’ to indicate that the relative xpath “exits” the PO1Loop1 element from the Foreach mapping.

Conditional Mappings with Lookahead Syntax

The 850 document contains multiple N1Loop1 elements representing a party in the exchange and the role of that party (ship-to party, bill-to party, etc). For each N1Loop1, the N1Loop1/N1/N102 element holds the value that identifies the party (i.e. the party name or ID), so these values must be mapped to the output CSV. However, the xpath for each N102 element is the same (regardless of the party’s role), so the xpath to the N102 element is not enough to map the correct values.

The role of the party is held in N1Loop1/N1/N101, so the mapping needs to “look into” the N101 element before mapping the N102 element value to the output CSV. In other words, the BillToPartyCode needs to be mapped to:

N1Loop1/N1/N102 WHERE N1Loop1/N1/N101 = 'BT'

This conditional mapping can be accomplished with Lookahead syntax.

First, drag the element containing the value from the source onto the destination element (BillToPartyCode):

Then, hover over the mapped BillToPartyCode element and click the tablet icon to open the Expression Editor:

Note that the current mapped xpath is N1Loop1/N1/N102 and the xpath to “look into” is N1Loop1/N1/N101. Lookahead syntax is specified as follows:

• Find the element in the current xpath that is the closest parent of the element to “look into” (in this case it is the N1 element)
• Specify in square brackets ‘[]’ the xpath to the element to “look into” (in this case it is simply [N101])
• Add an equals expression ‘=’ to indicate the value the check this element against (in this case it is ‘BT’)
• Escape the square brackets with backslashes ‘\’ so that they are not evaluated as part of the xpath expression

The resulting Lookahead expression is as follows:

The same process can be repeated for the rest of the destination elements. The resulting mapping is almost complete:

Modifying and Calculating with the Expression Editor

In addition to xpath manipulation, the Expression Editor supports formatters that modify or manipulate values during a mapping. The ItemTotalCost element needs to be mapped to the total cost for the line item, which is not directly available in an source element but is easily calculated by multiplying the item quantity and the item price.

As a starting point, map an item price to the ItemTotalCost element and open the expression editor:

The multiply formatter can be used to multiply this starting value (the price per item) with the number of items. Formatter syntax works as follows:

• Formatter sequences are contained within square brackets (as is already the case in the current ItemTotalCost expression)
• Formatter sequences are evaluated left-to-right
• Formatter sequences are delimited by vertical pipe characters (e.g. ‘[value | formatter1() | formatter2() | formatter3()]’)

Note: the xpath() syntax that results from dragging a source node onto a destination node is itself a special kind of formatter.

Add a vertical pipe separating character at the right end of the xpath expression (and within the closing square bracket) to create a new space for a formatter. In this space, either manually type in the multiply() formatter or navigate to the Formatters tab in the Expression Editor, search for ‘multiply’, and click the appropriate formatter to add it to the expression:

In order to multiply the starting value (the item price) with the item quantity, the item quantity value needs to be specified as the parameter to the multiply formatter. Since this value needs to be pulled from the input 850, another xpath expression is required, with the xpath that corresponds to the item quantity (PO1/PO2):

The resulting expression correctly calculates the total line item cost by multiplying the cost-per-item with the item quantity.

After saving the expression, the mapping is fully completed:

Save the changes in the XML Map Connector and the full flow has been configured.

Sample Documents

The following sample data was used to make this guide.

X12 850

ISA*00*          *00*          *ZZ*AMAZONDS       *01*003025392      *160623*1448*U*00401*000000013*0*P*+~
GS*PO*AMAZONDS*ATFDS*20070911*2001*95*X*004010~
ST*850*0003~
BEG*00*DS*TesT0008516**20070805~
CUR*BT*USD~
N1*BT*Amazon.com.kydc,Inc.*92*KYDC~
N1*SF*WHSE*92*WHSE~
N1*ST*Charlie Dinkins~
N3*11254 Main St*Suite 112~
N4*Seattle*WA*98104*US*CC*United States~
TD5**92*UPS_GR_RES****ZZ*RES~
N1*LW*Amber Baker~
N3*123 Anderson Avenue~
N4*Seattle*WA*98103*US~
PO1*1*3*EA*18.04*NT*SK*1617*****BL*1*ZZ*Amazon.com~
PO1*2*2*EA*54.42*NT*SK*4927*****BL*1*ZZ*Amazon.com~
PO1*3*1*EA*18*NT*SK*9876*****BL*1*ZZ*Amazon.com~
CTT*3*6~
SE*15*0003~
GE*1*40~
IEA*1*000000040~

CSV

CustomerId,PONumber,PODate,BillToPartyCode,ShipFromCode,ShipToAddress,ShipToCity,ShipToZIP,LineItemNumber,ItemID,ItemQuantity,ItemPrice,ItemTotalCost
Sir Charles,128F195,20190805,AbRes,W22,2408 Creek St,Springfield,52213,1,BouncyRubberBall,4,2.49,9.96
Sir Charles,128F195,20190805,AbRes,W22,2408 Creek St,Springfield,52213,2,SquishyNerfBall,2,4.29,8.58