Getting started with EDI documents

EDI connector

The EDI connector in Etlworks Integrator contains components that convert messages between EDI and XML and vice versa. The connector supports two widely used EDI (electronic data interchange) formats: X12 and EDIFACT.

contact Etlworks support to enable the EDI connector for your account.

EDI standards

X12 is a standard for electronic data interchange (EDI) between trading partners over data networks. X12 is developed and maintained by the Accredited Standards Committee (ASC). For more information, see http://www.x12.org.

EDIFACT is an international electronic data interchange standard maintained by the United Nations. For more information, see http://www.unece.org/trade/untdid/welcome.htm.

X12 Message Structure

• Interchange: The outer layer that wraps the entire X12 message.
• Functional group: The middle layer that wraps one or more transaction sets.
• Transaction set: The inner layer that contains the data.

ST*109*0001~B4*15*0*2*19960515*1424*Statu*Equi*Equipment*D*XXXX*
Location Identifier*A*3~N9*01*Reference Identification*
Free-form Description*19960515*1424*01*01^Reference Identification^01^
Reference Identification^01^Reference Identification~Q2*X*XX*19960515*
19960515*19960515*10045*21392*A*Flight/Voy*01*Reference Identification*
B*Vessel Name*10691*B*E~V9*AAD*Event*19960515*1424*City Name*XX*XX*001*
XXXXXX*25946*Tr*Free-Form Message*01*25878*XXXXXX*717*437*272*2457*
12935~R4*1*A*Location Identifier*Port Name*XX*Terminal Name*Pier*XX~DTM*
001*19960515*1424*01*CC*Date Time Period~V9*AAD*Event*19960515*1424*
City Name*XX*XX*001*XXXXXX*4685*Tr*Free-Form Message*01*13647*XXXXXX*813*
605*52*20035*12104~N9*01*Reference Identification*Free-form Description*

XML of an X12 Message

The following example illustrates part of the XML output:

<interchange>
   <ISA>
      <R01>00</R01>
      <R02 />
      <R03>00</R03>
      <R04 />
      <R05>01</R05>
      <R06>003897733</R06>
      <R07>ZZ</R07>
      <R08>SLRGATEWAY</R08>
      <R09>050202</R09>
      <R10>1338</R10>
      <R11>U</R11>
      <R12>00401</R12>
      <R13>000000708</R13>

EDIFACT Message Structure

An EDIFACT message contains segments and fields which are separated by a hierarchy of delimiters characters such as:

UNA:+,? '
UNB+UNOA:2+BANESTO+TELEFONICA DE ESPAÑA S.A.+050321:1204+0030200503211'
UNH+20050321120412+BANSTA:098:96A:UN'
BGM+XZ8+20050321120412+9'
DTM+137:20050321:102'
RFF+ACW:20050321113331'
DTM+171:20050321:102'
LIN+1'
SEQ+YF2+1'
GIS+1'
DTM+140:20050321:102'
MOA+9:0,00'
CNT+2:1'
CNT+X27:1'
AUT+INTERV49 107835'
UNT+14+20050321120412'
UNZ+1+0030200503211'

XML of an EDIFACT Message

The EDIFACT connector processes XML structures such as the following example:

<interchange>
   <UNA>
      <R01>:</R01>
      <R02>+</R02>
      <R03>,</R03>
      <R04>?</R04>
      <R05 />
      <R06>'</R06>
   </UNA>
   <UNB>
      <R01>
      <R01>UNOA</R01>
      <R02>2</R02>
      </R01>
      <R02>
      <R01>BANESTO</R01>
      </R02>

Creating EDI format

In order to work with EDI messages is it required to create an EDI format. Setting a valid Transaction Set is important if you wish to include a field description in the filed name. Example:

• FTX03_TextReference - a filed name with a description
• FTX03 - a field name without a description

Saving EDI file as XML

The EDI connector can convert any X12 or EDIFACT message to the XML file. You can then use any available Integrator technique, for example, XSL transformations, to process XML.

Step 1. Create a source connection to read the EDI file from.

Step 2. Create EDI format. Set the Name of the XML file to convert EDI document to. You can use tokens such as {app.data} as a part of the file name. Optionally enable Do not parse EDI document.

Step 3. Create a Memory connection for the destination. 

Step 4. Create a new Any-to-Any flow.

Step 5. Create a new source-to-destination transformation where:

• The source connection is a connection created in step 1.
• The source format is a format created in step 2.
• The destination connection is a connection created in step 3.

Converting all EDI files which are matching wildcard to XML 

Step 1. Create a flow that converts a single EDI file to XML as in the example above.

Step 2. Create a nested flow. 

Step 3. Add flow created in step 1. Click the edit (pen) button and change Loop type to Files by wildcard, select source connection as a connection for the loop and enter the wildcard filename in the File path field.

Converting EDI files to any format

In Etlworks Integrator it possible to convert inbound EDI messages into any format, for example, JSON, or save attributes of the EDI message into the database.  

In this example, we will be using the X12 204 (tender) message as a source. Our destination JSON file will have the following structure. Note the attributes in the JSON file. We are going to be extracting them from the source X12 message. 

[
 {
    "transactionSetIdentifierCode": "204",
    "transactionSetControlNumber": "001",
    "date": "20190423",
    "time": "184914",
    "address": "960 NORTH POLE SUITE 123",
    "city": "NORTH POLE",
    "state": "NP",
    "zip": "11111",
    "contactName": "JOHN SMITH",
    "communicationNumberQualifier": "TE",
    "communicationNumber": "123723582648456",
    "equipmentNumber": "ZZZZ",
    "equipmentLength": "136",
    "height": "250",
    "width": "248"
 }
]

Step 1. Create a source connection for inbound EDI messages.

Step 2. Create a new EDI format. Make sure you set the valid Transaction Set, for example, 204.

At this point, we can explore the structure of the EDI message and see the data in Explorer:

We will be using Explorer to run the SQL queries against the EDI file which extracts various attributes from the X12 message.

Step 3. Create a destination connection.

Step 4. Create a new JSON format. Keep all default settings. 

Step 5. Create a new file to file flow.

Step 6.  Add a new source to destination transformation where:

• The source is a connection created in step 1 and the X12 format created in step 2.
• The destination is a connection created in step 3 and the JSON format created in step 4.

Step 7. Click the MAPPING button and select the Additional transformations tab. Copy and paste the following JavaScript code into the After Extract field. We will be using JavaScript to map the fields in the source X12 message to the fields in the destination JSON file. The technique is explained in the following article. IMPORTANT: Notice that values for all fields in the JSON are populated using SQL running on the dataset created by parsing the source X12 message. 

importPackage(com.toolsverse.etl.common);

// create a staging dataset
var staging = new DataSet();

// create fields and data in the staging dataset

// transaction set

var transactionSet = CommonEtlUtils.executeSql(dataSet, 
'select ST01_TransactionSetIdentifierCode,ST02_TransactionSetControlNumber 
from Interchange.FunctionalGroup.TransactionSet.TX00401204.Meta');

staging.addField("transactionSetIdentifierCode", 
transactionSet.getFieldValue(transactionSet.getRecord(0), 
"ST01_TransactionSetIdentifierCode"));

staging.addField("transactionSetControlNumber", 
transactionSet.getFieldValue(transactionSet.getRecord(0), 
"ST02_TransactionSetControlNumber"));

// date and time

var functionalGroupHeader = CommonEtlUtils.executeSql(dataSet, 
'select GS04_Date,GS05_Time from Interchange.FunctionalGroup.Meta');

staging.addField("date", 
functionalGroupHeader.getFieldValue(functionalGroupHeader.getRecord(0), 
"GS04_Date"));

staging.addField("time", 
functionalGroupHeader.getFieldValue(functionalGroupHeader.getRecord(0), 
"GS05_Time"));

// address

var address = CommonEtlUtils.executeSql(dataSet, 
'select N301_AddressInformation 
from Interchange.FunctionalGroup.TransactionSet.TX00401204.N1Loop1.N3');

staging.addField("address", 
address.getFieldValue(address.getRecord(0), "N301_AddressInformation"));

// location

var location = CommonEtlUtils.executeSql(dataSet, 
'select N401_CityName,N402_StateorProvinceCode,N403_PostalCode 
from Interchange.FunctionalGroup.TransactionSet.TX00401204.N1Loop1.N4');

staging.addField("city", 
location.getFieldValue(location.getRecord(0), "N401_CityName"));

staging.addField("state", 
location.getFieldValue(location.getRecord(0), "N402_StateorProvinceCode"));

staging.addField("zip", 
location.getFieldValue(location.getRecord(0), "N403_PostalCode"));

// communications

var contact = CommonEtlUtils.executeSql(dataSet, 
'select G6102_Name, G6103_CommunicationNumberQualifier,G6104_CommunicationNumber 
from Interchange.FunctionalGroup.TransactionSet.TX00401204.N1Loop1.G61');

staging.addField("contactName", 
contact.getFieldValue(contact.getRecord(0), "G6102_Name"));

staging.addField("communicationNumberQualifier", 
contact.getFieldValue(contact.getRecord(0), 
"G6103_CommunicationNumberQualifier"));

staging.addField("communicationNumber", 
contact.getFieldValue(contact.getRecord(0), 
"G6104_CommunicationNumber"));

// equipment details

var equipmentDetails = CommonEtlUtils.executeSql(dataSet, 
'select N702_EquipmentNumber,N715_EquipmentLength,N720_Height,N721_Width 
from Interchange.FunctionalGroup.TransactionSet.TX00401204.N7Loop1.N7');

staging.addField("equipmentNumber", 
equipmentDetails.getFieldValue(equipmentDetails.getRecord(0), 
"N702_EquipmentNumber"));

staging.addField("equipmentLength", 
equipmentDetails.getFieldValue(equipmentDetails.getRecord(0), 
"N715_EquipmentLength"));

staging.addField("height", 
equipmentDetails.getFieldValue(equipmentDetails.getRecord(0), "N720_Height"));

staging.addField("width", 
equipmentDetails.getFieldValue(equipmentDetails.getRecord(0), "N721_Width"));

// update the dataset, which will be used to create a final documemnt, 
// by copying data and fields from the staging dataset
dataSet.setFields(staging.getFields());
dataSet.setData(staging.getData());

Creating EDI files

To create an EDI file you will need to create a dataset with the structure that is compatible with EDI to XML transformation, then let the system automatically create the EDI.

Step 1. Create a source connection.

Step 2. Create a source format if needed.

Step 3. Create a destination connection.

Step 4. Create EDI format.

Step 5. Create a flow where the source is a connection/format created in steps 1-2 and the destination is a connection/format created in steps 3-4.

Step 6. Use this technique to construct the dataset compatible with EDI to XML transformation.