Get started with EDI documents

• Starter
• Business
• Enterprise
• On-Premise
• Add-on

Overview

Etlworks Integrator supports X12 and EDIFACT formats.

What can you do with EDI in Etlworks Integrator

Enable EDI connector

The EDI connector in the 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 EDIFACT connector for your account. X12 connector is already enabled. 

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

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>

Create EDI Format

To work with EDI messages, it is 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.

Read how to install schemas for different versions of EDI formats.

Save EDI file as XML

The EDI connector can convert any X12 or EDIFACT message to the XML file. You can then use any available Etlworks 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.

Convert 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 pen button and change Loop Type to Files bt wildcard, select source Connection as a Connection for the loop and enter the wildcard filename in the File path field.

Convert EDI files to any Format

In the Etlworks Integrator, it is 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 the Etlworks Explorer:

We will be using the Etlworks 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 MAPPING 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());

Create 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 and 2 and the destination is a Connection/Format created in steps 3 and 4.

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