- Starter
- Business
- Enterprise
- On-Premise
- Add-on
Overview
Etlworks Integrator supports X12 and EDIFACT formats.
What can you do with EDI in Etlworks Integrator
X12 and EDIFACT Formats The EDI connector in Etlworks Integrator contains components that convert messages between EDI and XML and vice versa. |
Save EDI file as XML The EDI connector can convert any X12 or EDIFACT message to the XML file. |
Convert all EDI files which are matching wildcard to XML Create a Flow that converts a single EDI file to XML. |
Convert EDI files to any Format Convert inbound EDI messages into any Format, for example, JSON, or save attributes of the EDI message into the database. |
Create EDI files You will need to create a dataset with the structure that is compatible with EDI to XML transformation. |
|
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.
~
) and data elements are delimited by asterisks (*
).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.
Comments
0 comments
Please sign in to leave a comment.