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

Overview

Etlworks supports four widely used EDI (electronic data interchange) dialects: X12, EDIFACT, NCPDP, and VDA. Etlworks also supports HL7 2.x and HL7 FHIR EDI dialects used by healthcare providers and vendors. 

• 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 https://www.edibasics.com/edi-resources/document-standards/edifact/.
• NCPDP are consensus-based standards that connect healthcare electronically, streamline the system and safeguard patients. For more information, see https://standards.ncpdp.org/.
• VDA is a set of EDI standards developed for the automotive industry. For more information, see https://www.edibasics.com/edi-resources/document-standards/vda/.

What can you do with EDI messages in Etlworks

EDI messages

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*

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'

Create EDI Format

To work with EDI messages, it is required to create an EDI Format.

Difference between EDI connectors 

Etlworks is shipped with two EDI connectors:

Generic EDI format

• Generic EDI format supports four EDI dialects: X12, EDIFACT, NCPDP, and VDA. It is provided as a premium connector and must be enabled for your account. Users with trial accounts have full access to the EDI format (no need to enable anything). It also supports various HL7 versions, but we recommend using our specialized HL7 2.x and HL7 FHIR connectors.  
• EDI format requires access to our public EDI API. Host: https://etlworksedi.azurewebsites.net/api.
• EDI format supports reading and writing EDI messages in X12, EDIFACT, NCPDP, and VDA EDI dialects and creating acknowledgments for messages in X12 and EDIFACT EDI dialects. 
• EDI format internally transforms EDI messages in any dialect into JSON format. The JSON representation adheres to the open-source OpenEDI format based on OpenAPI 3, so it is easier to create EDI documents using the generic EDI format than the specialized X12 format (below) based on unpunished XML specification. 

X12 format

• The X12 format is free and included in every version of Etlworks. It supports reading and writing X12 documents, transforming X12 documents to XML, and generating 997 acknowledgments.
• X12 format internally transforms EDI messages in X12 dialect into XML format.
• X12 format requires a schema for different versions of the X12 dialect. Schema for X12 000401 is loaded by default. Other schemas must be installed by end user or Etlworks support. Read how to install the schema. 
• Setting the Transaction Set (for example 204) is highly recommended when configuring the X12 format.

ETL EDI messages into any destination

EDI to anything use case

As a user, I want to be able to parse EDI messages and then save the data to the database or a file.

Any flow which reads an EDI message converts it to a highly nested dataset. The structure of the message can be discovered in Explorer.

The typical ETL transformation will either need to convert the nested dataset to another nested format, for example, JSON or XML or flatten it to load into one or multiple tables in a relational database. 

There are several techniques available to developers when creating an ETL transformation, which reads the EDI message, transforms it, and loads it into any destination.

Mapping

This technique allows user to transform part of the EDI message (one segment, for example N1Loop->N1, at a time) into the flat dataset, then load it into any destination.   

Step 1. Create source and destination connections, EDI format (either generic EDI or X12), and optional destination format if EDI messages are transformed into the file in a different format. 

Step 2. Create a new Any to any Flow. 

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

• The source Connection is a Connection created in step 1.
• The source Format is an EDI format created in step 1.
• The FROM is a source EDI message.
• The destination Connection is a Connection created in step 1.
• The optional destination Format is a Format created in step 1.
• The TO is a destination table name.

Step 4. Click the Mapping button to open the Mapping editor.

Step 5. Click the Create Mapping button to generate the default mapping.

Step 6. Use this technique to map one segment in the EDI document to the flat dataset. 

Step 7. Click Test transformation at the bottom of the screen to verify that the ETL works as expected. 

Step 8. Repeat steps 3-7 for any number of segments. 

Step 9. Save and execute the flow. It should create flat tables in the destination database for each mapped segment. 

Create staging tables

This technique allows normalizing the super nested EDI message into the set of flat staging database tables, where each segment and subsegment is stored in its own table. 

Read more about this technique. 

Step 1. Create source and destination connections and EDI format (either generic EDI or X12). When creating a destination connection, it is recommended to use a built-in SQLite database. 

Step 2. Create a new flow using the flow type Extract nested dataset and create staging tables.

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

• The source Connection is a Connection created in step 1.
• The source Format is an EDI format created in step 1.
• The FROM is a source EDI message.
• The destination Connection is a Connection created in step 1.
• The TO is a prefix used when creating destination staging tables.

Step 4. Click the Mapping button to open the Mapping editor.

Step 5. Select Parameters tab and enable the following parameters:

Step 6. Save and run the flow.

Step 7. You can now check the staging tables created by the flow in Explorer.  

Step 8. Staging tables can be used to map data in the EDI message into the actual tables in any database. Once you are done with mapping, you can switch to the in-memory temp database, which is also based on SQLite.

Use JavaScipt or Python to transform the EDI message

In the Etlworks Integrator, it is possible to convert EDI messages into any Format, for example, JSON using a technique where a small program in JavaScript or Python is used to transform an EDI document (a nested dataset) into a valid JSON or XML document with the desired structure.  Read more about using JavaScript to create nested documents. 

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 EDI messages.

Step 2. Create a new X12 Format. Note that the technique for the generic EDI format is the same, but this example was explicitly created for X12 format, so the actual JavaScript code will look slightly different. 

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

We will use 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 use 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());

Generate acknowledgment

X12 and generic EDI formats can be configured to automatically generate an acknowledgment when reading the X12 and EDIFACT messages. 

To generate an acknowledgment each time the connector reads the EDI message, simply enter the Acknowledgment File Name when creating a new or editing an existing generic EDI or X12 format. 

The file will be created in the attached server storage.

You can use token {app.data}to point to the Home folder in the server storage: {app.data}/ack.x12.  The ack file name can be dynamic. Read about the parametrization of the connections and formats using global variables. 

For EDI format you can optionally configure the parameters of the acknowledgment.

Create EDI messages

Anything to EDI use case

As a user, I want to be able to read data from any source, transform it and create EDI messages.

To create an EDI file, you must create a dataset with a structure compatible with EDI to XML or EDI to JSON  transformation, then let the system automatically create the EDI.

Process

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 or EDI to JSON transformation.

Convert EDI message to XML or JSON

It is possible to convert any EDI message to XML or JSON document without configuring mapping. 

Step 1. Create source and destination connections, source EDI format (either generic EDI or X12), and destination JSON or XML format. 

Step 2. Create a new Any to any Flow. 

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

• The source Connection is a Connection created in step 1.
• The source Format is an EDI format created in step 1.
• The FROM is a source EDI message.
• The destination Connection is a Connection created in step 1.
• The destination Format is a Format created in step 1.
• The TO JSON or XML file name.

Step 4. Click Test transformation at the bottom of the screen to verify that the ETL works as expected. 

Step 5. Save and execute the flow. It should create XML or JSON files.  

Convert X12 message to XML file

The X12  connector can convert any X12 message to an XML file without executing source-to-destination transformation. 

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

Step 2. Create X12 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. 

The file will be created in the attached server storage.

You can use token {app.data}to point to the Home folder in the server storage: {app.data}/214.x12.  The ack file name can be dynamic. Read about the parametrization of the connections and formats using global variables. 

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 X12 files which are matching wildcard to XML 

Step 1. Create a Flow that converts a single X12 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.