Articles in this section

Get started with EDI documents

  • Updated
Follow
  • 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.

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 any format

Create ETL Flow that converts an EDI message to a different format without mapping.

Read more

Rapid conversion to XML, JSON or CSV

The X12 connector allows for rapid conversion of any X12 message to an XML, JSON or CSV without the need for a full source-to-destination transformation

Read more

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 KB). 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 (up to 100 MB). It is optimized for high-volume data flows requiring performance.

Usage and Access:The X12 Format connector is free and available in all Etlworks versions. For downloading schemas it requires access to the Etlworks CDN (host: https://etlworks-edi-schemas.s3.amazonaws.com)

IMPORTANT: X12 connector is free but it requires installing a license. If you are using self-hosted Etlworks please contact support@etlworks.com for assistance with installing a license. 

Supported Operations:

  • Read and write X12 documents.
  • Directly convert X12 messages into XML, JSON, or CSV formats.
  • Generate 997 acknowledgment messages.

Schema Requirements:

A schema is required for each version of the X12 dialect. If the schema file does not already exist in the cache under {app.data}/metadata/edi, the connector automatically downloads it from the CDN and stores it in the cache. While users can override the default schema location and install schemas manually (see documentation), this is optional and typically only necessary if the Etlworks instance cannot perform outbound HTTPS requests to AWS S3.

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. 

mceclip1.png

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. 

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

mceclip1.png

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.

mceclip2.png

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.

mceclip3.png

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 any format

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. 

mceclip1.png

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.  

Rapid conversion of X12 to XML, JSON or CSV

The X12 connector enables rapid conversion of any X12 message into XML, JSON, or CSV formats without requiring an ETL source-to-destination transformation. This streamlined process is optimized for performance and efficiently handles large X12 documents.

Typical use cases include flattening complex, nested X12 messages for easy ingestion into databases, data warehouses, or analytics platforms. By converting X12 documents directly into CSV, JSON, or XML, data engineers can rapidly integrate transactional EDI data into relational databases, load large volumes of EDI transactions into data lakes, or enable real-time analysis and reporting on structured EDI information.

Step-by-step process

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

Step 2: Create an X12 Format.

IMPORTANT: When creating a format make sure correct EDI Version is selected and Transaction Set is set:

Step 3: Create a new flow using EDI to file flow type.

Step 6: Configure a new source-to-destination transformation:

  • Source Connection: The connection created in step 1.

  • Source Format: The format created in step 2.

  • FROM: the file name or a wildcard file name matching the source files to convert.

  • Destination Connection:  any file based connection such as Server Storage, S3, etc.

  • Destination format: select CSV, JSON or XML format. 
  • TO: the destination file name. If enter the wildcard, the wildcard part of the file (*) will be replaced with a source file name. NOTE: TO can contain a relative folder, for example out/*.csv.

Step 7: Optionally configure parallel processing for each file matching a wildcard.

If there are multiple source files matching a wildcard you can configure flow to process multiple files in parallel threads by enabling option Use Parallel Threads when processing sources by a wildcard under Flow->Configure->Parameters:

Step 7: Optionally add multiple source to destination transformations

If you are converting files by multiple wildcards or files in different formats, for example X12 835 and X12 837 add multiple source-to-destination transformations.

Optionally enable parallel processing for each of the transformations:

Handling Processed Files

 default, after processing, files remain in the source folder. However, you can configure the Flow to either delete, move, or mark files as processed.

Delete processed files

Enable Delete loaded source files under MAPPING > Parameters.

You can also enable Delete source files on error to remove files if an error occurs during processing.

Skip Already Processed Files

Instead of deleting files, the Flow can mark files as processed and keep them in the folder. This prevents reprocessing without physically removing files.

To enable this feature, use the following UI controls under MAPPING > Parameters:

• Skip Previously Processed Files – Ensures only new or modified files are processed.

• File Retention in Cache (ms) – Controls how long processed file information is stored in cache.

• Custom Cache File Name – Specifies a custom file for storing processed file records.

Move Processed Files

Instead of deleting or marking files, you can move them to another location.

To configure this:

• Go to Flow > Connections tab and set Move processed files to.

• This moves processed files to a different location instead of leaving them in the source folder.

• This option is ignored if Delete loaded source files or mark files as processed is enabled.

Conversion to CSV specifics

When converting X12 messages to CSV, the connector parses the hierarchical structure of the X12 document into multiple CSV files—one per each segment type, loop, Transaction Set, Functional Group, and Interchange. Each CSV file includes:

  • Columns for each data element in a segment or metadata fields for loops and Transaction Sets.

  • Automatic handling of varying numbers of data elements across segments, ensuring the resulting CSV files are normalized with null values where appropriate.

  • Parent-child relationships captured through columns like Parent_ID and Parent_Name.

To convert to CSV set destination format to any existing CSV format:

You can further customize CSV generation:

  • Enable or disable creating separate CSV files for Interchange and FunctionalGroup levels.

  • Exclude specific segments from the conversion by listing them in a comma-separated list.

  • Optionally prepend segment names to filenames for clarity (e.g., N1_Party_Identification.csv).

Related articles