Starter
Business
Enterprise
On-Premise
Add-on

Overview

Etlworks provides comprehensive support for most EDI (Electronic Data Interchange) dialects, including X12, EDIFACT, NCPDP, and VDA. Additionally, Etlworks supports healthcare-specific standards like HL7 2.x and HL7 FHIR, widely 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

Create EDI Formats To work with EDI messages, you first need to create an EDI Format in Etlworks. Read more	ETL EDI messages into any destination With Etlworks, it’s possible to parse EDI messages and save the extracted data directly to a database or a file format of choice. Read more
Create EDI messages To create an EDI file, you must create a dataset with a JSON structure compatible OpenEDI format Read more	Generate acknowledgment X12 and generic EDI formats can be configured to automatically generate an acknowledgment when reading the X12 and EDIFACT messages. Read more
Convert EDI messages to a diffrent formats such as XML, JSON, Parquet or Avro Create a Flow that converts an EDI message to a different format without requiring mapping. Read more	Convert X12 message to XML or JSON file The X12 connector allows for rapid conversion of any X12 message to an XML or JSON file without the need for a full source-to-destination transformation Read more

Related case study

Xsolis case study Convert X12 messages to JSON and Parquet, load them into Redshift, and integrate Salesforce.	Xsolis, a leader in healthcare data analytics, leverages Etlworks to manage their complex data integration needs. Etlworks powers XSOLIS to seamlessly process hundreds of thousands of massive X12 messages—each tens of megabytes in size—by converting them into JSON and Parquet formats and loading them into Amazon Redshift. Additionally, XSOLIS uses Etlworks to streamline integrations with Salesforce, enabling a unified data ecosystem.

EDI Format

To work with EDI messages, you first need to create an EDI Format in Etlworks.

Differences Between EDI Connectors

Etlworks offers two connectors for EDI processing: EDI Format and X12 Format. Each is designed for different use cases, depending on the file size, EDI dialect, and required transformations.

EDI Format Connector

The EDI Format connector is ideal for small to medium-sized EDI files (up to 100 MB). This connector is optimized for workflows that involve manageable file sizes and simpler transformations, especially with the drag-and-drop UI to streamline data mapping. For larger files or batch processing, consider using the X12 Format connector for better performance.

Supported EDI Dialects: The EDI Format connector supports X12, EDIFACT, NCPDP, and VDA. It also accommodates HL7 versions, though for HL7 data, it’s recommended to use the dedicated HL7 2.x and HL7 FHIR connectors.

Usage and Access: The EDI Format connector is free to use but requires access to the Etlworks EDI API (Host: https://etlworksedi.azurewebsites.net/api).

Supported Operations:

Read and write messages in X12, EDIFACT, NCPDP, and VDA formats.
Generate acknowledgments for messages in X12 and EDIFACT formats.
Transform EDI messages in any dialect into JSON format, leveraging the open-source OpenEDI format (based on OpenAPI 3). This JSON representation simplifies creating EDI documents in a generic format, making it easier than using the X12 Format, which relies on an XML specification.

X12 Format Connector

The X12 Format connector is designed for batch processing of large X12 documents (100 MB and up). It’s optimized for high-volume, complex data flows that require robust performance and reliable batch processing.

Usage and Access: The X12 Format connector is free and available in all Etlworks versions.

Supported Operations:

Read and write X12 documents, transform them to XML, and generate 997 acknowledgments.
Internally, the X12 connector converts EDI messages in the X12 dialect to XML format.

Schema Requirements: A schema is required for each version of the X12 dialect. By default, the schema for X12 version 000401 is loaded. Additional schemas can be installed by the user or with assistance from Etlworks support (see documentation on installing schemas).

Configuration Tip: For optimal performance, set the Transaction Set (e.g., 204) when configuring the X12 format.

ETL EDI messages into any destination

ETL EDI message to any destination

With Etlworks, it’s possible to parse EDI messages and save the extracted data directly to a database or a file format of choice.

Each flow that reads an EDI message converts it into a highly nested dataset, capturing the full structure of the message.

This structure can be easily explored and understood in the Explorer, enabling precise data mapping and transformation.

A typical ETL transformation may either convert the nested dataset into another nested format, such as JSON or XML, or flatten it for loading into one or multiple tables within a relational database.

Developers have several techniques available for creating ETL transformations that read, transform, and load EDI messages into any target destination, ensuring flexibility and control over the data integration process.

Mapping

Nested mapping

This technique allows the user to transform the entire EDI message into any supported data exchange format (JSON, XML, Parquet, Avro, etc.) or flat data set that can be loaded into the relational database.

Read about nested mapping.

Flat mapping

This technique allows the 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 nested EDI message into the set of flat staging database tables, where each segment and subsegment is stored in its own table.

Use JavaScript or Python to transform the EDI message

In Etlworks, 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.

// 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

Extract data from any source, create an EDI message

To create an EDI file, you must create a dataset with a JSON structure compatible OpenEDI format

specs, then let the system automatically create the EDI message.

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. Open https://edination.com/edi-translator.html#tab_ediJson in any browser. Paste and example of the EDI message that you want to generate into EDI Data field. Click JSON button.

Step 7. Use this technique to construct the dataset which will generated the JSON as in step 6. Use Test transformation button to check the output.

Step 8. Once you are satisfied with the output, run the flow and verify that it correctly generates the EDI message.

Convert EDI messages to XML, JSON, Parquet, or Avro

It is possible to convert any EDI message to XML, JSON, Parquet, or Avro documents without configuring mapping.

Step 1. Create source and destination connections, source EDI format (either generic EDI or X12), and destination XML, JSON, Parquet, or Avro 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 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 or JSON file

The X12 connector allows for rapid conversion of any X12 message to an XML or JSON file without the need for a full source-to-destination transformation. This streamlined process is optimized for speed and can efficiently handle very large X12 documents.

Step 1. Set up a Source Connection to read the EDI file.

Step 2. Create an X12 Format and configure the file name using the Output File Name for Converted EDI Document property. You can include global variables, referenced as {var_name}, in the filename to create dynamic file paths. If the file extension is .json, it will generate a JSON file; otherwise, it will default to XML.

Enable the Skip Parsing EDI Document setting for direct conversion without parsing.

The file will be saved in the attached server storage. For example, {app.data}/214.json saves the file in the home folder, and dynamic file naming is possible through 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.

Batch Convert Matching X12 Files to XML or JSON

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. Set FROM to {loop_file_name}.

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.

To enable parallel file processing, set Loop Threads to a value greater than 1. Higher values allocate more threads for file creation, increasing processing speed.

Articles in this section

Get started with EDI documents

Overview

What can you do with EDI messages in Etlworks

Related case study

EDI Format