Working with X12

X12 is the dominant EDI standard in North America — widely used for purchase orders (850), invoices (810), shipment notices (856), healthcare claims (837), remittance advice (835), and many other business documents. Etlworks ships native X12 support: read X12, generate X12, send and receive over AS2, SFTP, HTTP, or email.

This article is the single reference for X12 in Etlworks. For the umbrella overview of EDI in general — including the EDI Format vs X12 Format connector decision — see Get started with EDI documents.

Supported versions and transactions

X12 versions 004010, 005010, and 006010 are supported, along with most earlier versions. The default schema (00401) is bundled with Etlworks; other versions download from the Etlworks EDI schema bucket on first use.

Common transactions you can read and write:

Pick the right pattern for your X12 work

The X12 format

An X12 format defines how Etlworks parses or builds X12 messages: which version, which transaction set, validation strictness, output shape, and delimiters. Create one in Connections → Formats and pick X12.

Key settings:

Read X12

Etlworks supports two first-class paths for reading X12. They're not "primary vs. shortcut" — they target different scenarios:

The two paths use the same X12 format on the source side; what differs is the flow type and whether a mapping step is involved.

Read X12 at scale: direct conversion (EDI to file, no mapping)

The EDI to file flow type reads X12 messages and writes them to XML, JSON, or CSV without going through a mapping step. This is the fastest path and the recommended choice when:

• You're processing very large X12 documents (the X12 connector handles up to ~100 MB per file).
• You're processing many X12 documents — the lower per-document CPU and memory footprint matters at volume.
• No transformation is needed — the goal is just to land the X12 data somewhere else in a different format.

Steps:

1. Create the source connection that holds the X12 files (server storage, S3, SFTP, etc.).
2. Create an X12 format. Set EDI Version and Transaction Set — both are required.
3. Create a flow of type EDI to file.
4. Add a transformation:
   • FROM Connection: the connection from step 1.
   • FROM Format: the X12 format from step 2.
   • FROM: the source filename or a wildcard (*.x12).
   • TO Connection: any file-based connection (S3, server storage, …).
   • TO Format: CSV, JSON, or XML.
   • TO: the destination filename. Wildcards work — out/*.csv emits one output file per input.
5. No mapping needed — that's the point.
6. For batches, enable Use Parallel Threads when processing sources by a wildcard under Flow > Configure > Parameters to fan out across multiple files concurrently.
7. Save and execute. See Handling processed files for what to do with the inputs after conversion.

Conversion to CSV: structure

When the destination format is CSV, Etlworks emits one CSV file per segment type, per loop, per Transaction Set, per Functional Group, and per Interchange. Each CSV captures the columns for that segment's elements and includes Parent_ID / Parent_Name columns so the original hierarchy can be reconstructed later.

You can tune the CSV output in the X12 format settings:

• Enable or disable separate CSV files for the Interchange and FunctionalGroup levels.
• Exclude specific segments by listing them comma-separated.
• Optionally prepend the segment name to filenames for clarity (e.g., N1_Party_Identification.csv).

Read X12 with visual mapping (regular ETL flow + nested mapping)

When you need transformation — reshape the message, filter records, enrich with reference data, calculate fields, write to multiple linked destination tables — use a regular source-to-destination ETL flow with the X12 format on the source side and nested mapping to shape the output.

This is the no-code, drag-and-drop path. It's slower than the direct conversion above (mapping adds CPU and memory overhead per record), but the trade-off is worth it when transformation is what you need. For modest volumes — the typical X12 trading-partner workload — the overhead is not a problem.

What you can do here that you can't do with the direct path:

• Reshape the X12 message into any flat destination — a single database table, a CSV file, a JSON array — with column mapping.
• Reshape into any nested destination — nested JSON, XML, Parquet, Avro, MongoDB documents — with parent-child mapping.
• Apply calculated fields, filters, and conditional logic.
• Resolve reference data with UI-driven lookups — customer details, tax codes, partner-specific qualifier values.
• Load multiple destination tables in one pass when the source X12 message contains parent / child structures.
• Get AI-assisted mapping suggestions from Simba.

Steps:

1. Create the source connection that holds (or receives) the X12 files — server storage, S3, SFTP, AS2 listener, inbound email, whatever the source is.
2. Create an X12 format.
3. Create the destination connection (database, file storage, API, NoSQL, queue, anything).
4. Create a flow that matches the source-to-destination shape — for example, File to database when the destination is a relational database; File to file when the destination is file storage in a different format; File to NoSQL when the destination is MongoDB; format-to-format flows when both sides are structured formats.
5. Add a transformation:
   • FROM Connection: the source connection.
   • FROM Format: the X12 format from step 2.
   • FROM: the X12 filename or wildcard.
   • TO Connection: the destination connection.
   • TO Format: whatever shape you want the output in (CSV, JSON, XML, Parquet, …) or no format when writing to a database.
   • TO: the destination table or file name.
6. Click MAPPING to open the nested mapping editor. The source tree is X12-aware — every segment, loop (with its minOccurs / maxOccurs), and element appears with its position and human-readable description.
7. Drag source elements onto the destination structure. Repeating X12 loops feed repeating arrays or child rows. For reference data, use UI-driven lookups. For AI-assisted mapping, open Simba chat from the editor.
8. Save and execute.

Variant: read one segment at a time (flat mapping)

When you only want one segment type at a time as flat rows — e.g., load every N1 party-identification segment from a batch of 850s into one parties table — use the visual mapping path but add a source query under MAPPING > Source query:

SELECT * FROM N1Loop->N1

The X12 parser exposes the parsed message as an in-memory dataset queryable with SQL; the source query targets one loop / segment at a time. Map the resulting flat row to destination columns as usual.

Variant: read into staging tables (one table per segment)

For full normalization — one staging table per segment / subsegment, with parent-child keys — use Automatically create staging tables from any data source. The flow type creates and populates a relational staging schema that mirrors the X12 hierarchy.

Variant: transform with JavaScript or Python

For transformations that don't fit nested mapping — complex per-record calculations, conditional segment routing, multi-message dispatch — transform the parsed X12 dataset in a JavaScript / Python flow. The parsed message is exposed as a nested dataset; small scripts can drive arbitrary downstream behavior.

Generate X12 from any source

To build an X12 message from a database query, a file, an API response, or any combination, set the X12 format as the destination format. Etlworks emits a valid interchange (ISA / IEA), functional group (GS / GE), and transaction set from the mapping you provide.

Recommended: the X12 Message Designer

The X12 Message Designer is the primary path for creating X12 messages in Etlworks. When the destination format is X12, the Create Mapping button in the mapping editor includes a Message Designer option. The Message Designer is a tree-driven editor focused on the X12 schema: pick the segments you need, mark the loops that should repeat, and bind each leaf element to a source field or a lookup query.

UI-driven lookups are how you create loops and repeating segments

In the Message Designer, UI-driven lookups are the mechanism for populating loops and repeating segments — not an optional feature, the core building block. When you mark a loop as repeating, you bind it to a lookup query (a DataSet lookup): the query returns one row per loop iteration, and the elements inside the loop are bound to the columns of the lookup result.

Example: an 850 purchase-order message has an N1 loop for party identification (Buyer, Seller, Ship-To, Bill-To) that may repeat. To populate it from a flat source:

1. Mark the N1 loop as repeating in the Designer.
2. Bind it to a lookup query — e.g., select party_role, party_name, party_id from partners where order_id = {ORDER_ID}.
3. Map N1-01 (Entity Identifier Code) to the party_role column, N1-02 (Name) to party_name, N1-04 (Identification Code) to party_id.
4. The token {ORDER_ID} is substituted per source row from the parent context.

The lookup result-set drives the loop — one segment per row. The same pattern populates line-item loops (e.g., PO1 loop on an 850), SVC loops on an 835, and any other repeating structure.

Why the Designer is the primary path

• Starts from the X12 transaction-set schema — you only build what you need, not the whole tree.
• Knows the cardinality of every loop and segment — minOccurs, maxOccurs, qualifier codes — so misconfiguration is caught at design time.
• Handles repeating loops naturally through the UI lookup mechanism described above.
• Produces a mapping that shows up in the regular nested-mapping table once you're done — you can review, edit, and save like any other mapping.

Use the Message Designer when:

• The source dataset is flat (rows from a database, lines from a CSV) and the X12 hierarchy is driven by the X12 schema.
• You're working from an X12 spec rather than from a complex source schema.
• The X12 message has many repeating loops that need to be populated via lookups (almost always the case).

Secondary: nested mapping

Use plain nested mapping when the source is already deeply nested (an HL7 message, a Salesforce response, a nested JSON document) and you want to map source nesting onto X12 segments without going through the Message Designer's schema-first flow.

When the X12 format is the destination, the nested mapping editor's destination tree is X12-aware. Drag source fields onto X12 elements:

• Flat source fields drop onto top-level segments such as BEG or N1.
• Repeating source structures (a customer with many orders) drop onto repeatable loops — the loop fires once per source record.
• Calculated fields populate elements that need formatting, type coercion, or partner-specific qualifier codes.

For X12 elements that need reference data (partner IDs, tax codes, address lookups), use UI-driven lookups.

AI-assisted: ask Simba

Open Simba chat from inside the mapping editor and ask Simba to propose an X12 mapping from your source schema. Simba sees the source schema and the X12 transaction-set schema and proposes matches by name, type, and qualifier. Iterate conversationally, then ask Simba to "apply this mapping" — the result lands in the editor as a draft. See AI-assisted mapping with Simba for the full workflow.

Scripted: JavaScript or Python

For X12 generation logic that doesn't fit either the Message Designer or nested mapping — complex per-record calculations, conditional segment inclusion, generating multiple transactions in one interchange — transform the source dataset into an OpenEDI-compatible JSON structure in a JavaScript / Python flow, then let the X12 format serialize it. The OpenEDI structure mirrors the X12 hierarchy as JSON, so a small script can drive arbitrary X12 output.

Envelope, control numbers, and partner IDs

Etlworks fills in the ISA and GS envelopes automatically based on the X12 format's Sender ID and Receiver ID settings. Control numbers (ISA13, GS06, ST02) are generated per execution from a counter held in the flow's context.

Persistent control-number sequencing across flow executions is not built in. If your trading partner requires strictly monotonic control numbers across runs (and gaps trigger errors / MDNs), persist the last number to a small lookup table and pre-set it on the format's Initial control number field at the start of each run.

Acknowledgments (997)

The X12 format can generate a 997 functional acknowledgment automatically when Etlworks reads an inbound X12 message. Enable it by setting Acknowledgment File Name on the format.

• The acknowledgment file is written to the connection attached to the flow.
• Use the {app.data} token to point at the server-storage home folder, e.g., {app.data}/ack/997_{filename}.x12.
• The name can be dynamic via global variables.
• The generic EDI format additionally exposes per-acknowledgment parameters; see the format reference.

For the 999 implementation acknowledgment, build a small follow-up flow — automatic 999 generation is not yet built in.

Transport: AS2, SFTP, HTTP, email

Etlworks decouples X12 from transport — the same X12 format works whatever the message is travelling on. Pick the transport based on the trading-partner relationship.

AS2 send and receive

AS2 (Applicability Statement 2) wraps EDI payloads in S/MIME for signing and encryption and uses Message Disposition Notifications (MDNs) as receipts.

Send X12 over AS2

1. In Connections, click + and pick AS2.
2. Configure the AS2 connection:
   • URL: the partner's AS2 endpoint (HTTPS).
   • AS2-From / AS2-To: the AS2 IDs agreed with the partner.
   • Signing algorithm: default SHA-256 with RSA.
   • Signing certificate (local): your signing certificate + private key.
   • Encryption algorithm: default AES-256-CBC.
   • Encryption certificate (partner): the partner's public certificate.
   • Request MDN: on by default. Mark it signed if your partner expects signed MDNs.
3. Create a source connection and an X12 format.
4. Create a flow that matches the source type (e.g., Database to web service).
5. Set TO Connection to the AS2 connection from step 2. Set TO Format to the X12 format.
6. Map the source to the X12 destination — see Generate X12 from any source.
7. Save and execute. Etlworks signs and encrypts the payload, POSTs it to the partner URL, and waits for the synchronous MDN.

Receive X12 over AS2

1. In Connections > Listeners, click + and pick AS2 listener.
2. Configure the listener:
   • URL pattern: the path the partner POSTs to (e.g., /as2/inbound/partner-x). Combined with your instance base URL, this is the value you give the partner.
   • AS2-From: the partner's AS2 ID (optional validation).
   • AS2-To: your AS2 ID.
   • Decryption certificate: your private key.
   • Partner signing certificate: the partner's public certificate (for signature verification).
   • MDN type: unsigned or signed. For signed MDNs, supply your signing certificate + private key.
3. Create a flow where the source is the AS2 listener. Set the source format to the X12 format that matches the inbound transaction.
4. Map the X12 source into your destination (database / file / API).
5. Schedule the flow with a continuous-execution schedule so the listener is always live.

The listener accepts both synchronous and asynchronous MDN request modes from the sender. Synchronous MDN is generated and returned on the same HTTP response. For asynchronous MDN requests, the listener accepts the message but Etlworks does not currently dispatch the MDN to the sender's MDN URL — see the limitation in the AS2 capability table above.

Certificates

• Your local certificate + private key — signs outbound messages and decrypts inbound. Trading partners verify your signature with your public certificate (shared out-of-band).
• Partner certificate — encrypts outbound to your partner; decrypted on their side with their private key.
• Partner signing certificate (inbound) — verifies signed messages your partner sends to you.

SFTP, FTPS, FTP, HTTP, email

For non-AS2 transports the flow shape is the same as any file-based flow:

• Outbound: source is your data, destination is the file-storage / HTTP / outbound-email connection, destination format is the X12 format.
• Inbound: source is the file-storage / HTTP-listener / inbound-email connection, source format is the X12 format, destination is your database / file / API.

Handling processed files

By default Etlworks leaves inbound files in the source folder after processing. To change that behavior, use one of these settings under MAPPING > Parameters:

Troubleshooting AS2

Current limitations

• Asynchronous MDN dispatch is not yet built in. The AS2 listener accepts inbound messages from senders that request asynchronous MDN, but Etlworks does not actively POST the MDN to the sender's MDN URL. Synchronous MDN works fully.
• S/MIME compression on AS2 is not yet exposed in the UI.
• Trading partner management is per-connection — there's no separate Partner entity. Each partner gets its own AS2 connection / listener pair.
• Control-number persistence across flow executions is manual; persist the last number to a lookup table if your partner enforces monotonicity.
• 999 acknowledgments are not generated automatically; build a follow-up flow if your partner requires them.

Related articles

• Get started with EDI documents — the dialect-agnostic umbrella.
• Working with HL7 — HL7 2.x and FHIR.
• Nested mapping — the mapping mechanism X12 work uses end-to-end.
• Working with the Mapping Editor — UI-driven lookups and AI-assisted mapping.
• AI-assisted mapping with Simba.