If you work with enrollment files, you know the request: "What changed between last month's 834 and this month's?" The files are 80 MB, 300 MB, sometimes more. Notepad++ freezes, Excel truncates or crashes, and the answer is due this afternoon.
This article walks through a method that works with any tool, then shows how I do it with a small free CLI I wrote. All examples use synthetic data.
Why the obvious approaches fail
Opening it in Excel. X12 is not a table. An 834 is a stream of segments separated by a terminator, often with no line breaks at all. Excel's import wizard can split on ~, but one member spans many segments, so you get a ragged mess. Past about a million rows it cuts the file off, and the warning is easy to miss.
A plain text diff. If both files are one long line, the diff says everything is different. If the carrier reorders members between runs, every move looks like a delete plus an add. Line-based diff has no notion of "member."
Notepad++ with a compare plugin. Fine at 20 MB. At 300 MB it is a long wait or a frozen window.
The method that works
Compare members, not lines.
-
Split each file into member records. In an 834, each member record starts at an
INSsegment and runs until the nextINSor the end of the transaction set (SE). That boundary is the same in every trading partner's file. - Build a key per member. Something stable across files: subscriber ID, or subscriber ID plus a dependent identifier. This is where the work is. Which segment carries the ID depends on your trading partner's companion guide, so decide it once and write it down. If two people in a family share a key, you will get wrong matches, so add something that separates them, such as date of birth.
- Index the old file by key. Store a hash of each member's full text (a 128-bit hash is plenty) and the file offset. You do not need to keep the whole file in memory.
- Stream the new file once. For each member, look up the key. No match: ADDED. Same hash: UNCHANGED. Different hash: go back to that one old member by offset and compare field by field.
- Whatever is left in the index was not seen in the new file. Those are candidates for TERMED.
Because you only hold one hash per member, memory grows with the number of members, not the file size. That is how a multi-hundred-megabyte file stays manageable on a laptop.
Two traps that cause wrong answers
Trap 1: full files versus change files
Carriers send two kinds of 834. A full (audit) file lists every active member. A change file lists only members who changed.
In a full file, a member missing from the new file usually means they were termed. In a change file, a member missing from the new file means nothing: they just did not change this week. If you diff two change files and treat absence as termination, you produce a long, believable list of terminations that is wrong. Check which kind of file you have (BGN08 carries it) before you report a term count. When in doubt, ask your carrier. x12diff reads BGN08 itself: in a change file a missing member is reported as NOT_IN_NEW, not as a term, it prints which mode it used, and --mode full or --mode change overrides the detection.
Trap 2: terms are not always absence
A member can be termed while still present in the file. Depending on the partner, that shows up as:
- a termination code in a maintenance reason element on the
INSsegment, or - a coverage end date in a
DTPsegment that was not there last month.
So a good diff checks both: member gone, and member newly carrying a term flag or date. Report them separately, because your downstream process may treat them differently.
A third trap: a truncated transfer. If the new file was cut off mid-way by a failed SFTP pull, every member after the cut looks missing. Always check envelope integrity first. The SE segment count should equal the real segment count of the set, and IEA should close the interchange. Mismatches mean stop and re-pull the file, not report. x12diff does this check for you: on a cut-off file it prints INPUT INCOMPLETE OR INCONSISTENT, lists the missing SE, GE or IEA, and exits with code 3 (--allow-incomplete overrides it). Real output on a synthetic file I cut in half:
$ x12diff diff old.834 new_cut.834 --show 0
...
INPUT INCOMPLETE OR INCONSISTENT: 3 envelope problem(s); the numbers in this report may be wrong
ENVELOPE: new_cut.834: File ended inside transaction set 0001 (no SE)
ENVELOPE: new_cut.834: File ended inside functional group 1 (no GE)
ENVELOPE: new_cut.834: File ended inside interchange 000000001 (no IEA)
error: input is incomplete or inconsistent (for example a truncated transfer); fix the file or rerun with --allow-incomplete to accept it
$ echo $?
3
What to output
A CSV with one row per change is the most useful shape. Something like:
change_type,member_key,segment,element,label,old_value,new_value,detail
CHANGED,SUB00000031|999000031|18,N3,N301,,100 TEST STREET UNIT 31,200 SAMPLE AVENUE APT 31,
TERMED,SUB00000073|999000073|18,DTP*349,,,,DTP*349*D8*20261231,segment added; termination flag: DTP*349:03 present
TERMED,SUB00000126|999000126|18,INS,INS03,action code,030,024,termination flag: INS:03=024
TERMED,SUB00000012|999000012|18,,,,,,not present in new file
Open that in Excel and filter. It is small, and it is a table.
One caution when you write CSV that Excel will open: values can come from a file you do not control. A cell starting with =, +, - or @ can be run as a formula. Prefix those with an apostrophe (and leave plain numbers alone) so a hostile name field cannot execute anything.
Doing it with x12diff
I wrote a free command-line tool, x12diff, that implements the method above. Disclosure: it is mine, it is MIT licensed, and there is a paid desktop version planned. The CLI is free and will stay that way.
x12diff parse enrollment.834
x12diff diff last_month.834 this_month.834 --csv changes.csv
Synthetic output:
$ x12diff diff old.834 new.834 --show 3 --csv changes.csv
CHANGED SUB00000031|999000031|18 N3 N301: '100 TEST STREET UNIT 31' -> '200 SAMPLE AVENUE APT 31'
CHANGED SUB00000067|999000067|18 N3 N301: '100 TEST STREET UNIT 67' -> '200 SAMPLE AVENUE APT 67'
TERMED SUB00000073|999000073|18 DTP*349 segment added; termination flag: DTP*349:03 present
Old: old.834 members: 300 (BGN08 4: full)
New: new.834 members: 298 (BGN08 4: full)
Mode: full (a member missing from the new file is termed)
ADDED: 3
TERMED: 13 (5 absent from new, 8 newly flagged)
CHANGED: 11
UNCHANGED: 276
CSV written: changes.csv
Notes on how it behaves:
- It ships as a Windows exe with its own runtime, or as a jar for Java 17 and newer.
- It runs entirely on your machine and has no network code. That matters when the files hold PHI.
- It ships no implementation-guide text. You tell it your partner's member key, termination codes and labels in a plain-text profile (
x12diff profile > my-profile.propertiesprints the defaults). - On synthetic data, two roughly 300 MB files diff in about 8 seconds on my machine. Treat that as a ballpark, not a promise.
- You can generate fake files to test with:
x12diff gen --members 300 --old old.834 --new new.834 --seed 3.
The CSV starts with a UTF-8 byte-order mark so Excel opens it correctly. It also does a few related jobs: flattening 835 remits to CSV with adjustment-code rollups and balance checks, and matching 837 claims to payments and to 999/277CA rejections. Those are separate topics.
Checklist before you send the numbers
- Which kind of files are these, full or change?
- Is each file complete (envelope counts match)?
- Does your member key actually separate dependents?
- Did you spot-check three ADDED, three TERMED and three CHANGED members by hand against the raw file?
- Is the CSV stored where PHI is allowed to live?
The spot-check is the one that saves you. Whatever tool you use, trust it only after it agrees with you on a small file you checked by eye.
Questions, bug reports (synthetic repro files only, never real data): hello@datawharftools.com. Source and downloads: https://github.com/datawharf-tools/x12diff. Details and the Pro waitlist: https://datawharftools.com/x12/
Top comments (0)