EDI 810 to Excel Converter: Turn EDI 810 Invoice Files Into Spreadsheet Rows

What the BIG segment on an EDI 810 actually tells you

The BIG segment is the first thing a parser should read and the last thing most home grown parsers look at properly. It is mandatory, it appears once, and four of its ten elements decide how the rest of the document should be treated. Two of those four are optional, which is exactly why invoices post wrong.

Notice which fields the standard leaves optional. BIG04 is how the invoice finds its order, BIG07 is how you know it is a bill rather than a credit, and BIG08 is how you know it is not a resend, and all three can legitimately be absent. A trading partner specification will usually require them. The X12 standard does not, so a file that omits all three is technically valid and will pass a 997 without complaint.

The twenty BIG07 codes that change what an invoice means

BIG07 uses element 640, the transaction type code. Roughly twenty of its values describe invoices, and they are not interchangeable labels for the same thing. Four of them mean money flows toward you rather than away.

The drop shipment case deserves a moment because it quietly defeats a three way match. A DO invoice has no goods receipt at your location, so a matching rule that waits for a receipt will hold the invoice forever while the supplier chases payment. Our note on drop ship purchase orders covers how the order itself has to be flagged for this to work.

The IT1 loop is the invoice line, and the product ID is a pair

Every billed line is one IT1 segment, and everything about that line hangs below it. The part that surprises people writing their first parser is that the product identifier is not one field. It is a repeating qualifier and value pair, and there can be twelve of them.

Match lines on the product identifier rather than on IT101. Suppliers consolidate two of your PO lines into one invoice line when the part and price are identical, and they split one line into two when part of it shipped from a second warehouse. Position based matching breaks on both. The same discipline applies when reading the order side, which is why our purchase order line item extraction page returns one row per line with the part number attached rather than a header summary.

TDS, and the implied decimal that makes a total look wrong

TDS is the summary total, and all four of its elements are element 610, declared in the X12 dictionary as N2. N2 means two implied decimal places written with no decimal point in the file. This is the single most common cause of an 810 that reconciles to a number a hundred times too large or too small.

So TDS*152000*152000*148940*3060 is an invoice of $1,520.00 with a discount of $30.60 leaving $1,489.40. Every value in that segment carries the implied decimal. IT104 in the same file does not, because element 212 is declared R. A parser that applies one rule to the whole document gets one of the two wrong, and because both results look like plausible money, nothing alerts.

TDS01 is also defined as the total after allowances and charges. If you are reconciling line extensions to TDS01 and coming up short, look for SAC segments before you look for a missing line.

ITD: payment terms, written five different ways

ITD is where the discount and the due date live, and it is worth reading properly because early payment discounts are usually the cheapest yield in accounts payable. ITD01 is the terms type code, element 336, which has more than sixty values. These are the ones you will actually see.

The discount itself can arrive in ITD03 as a percent, in ITD04 as a discount due date, in ITD05 as a number of days, in ITD07 as net days, or in ITD08 as a flat amount, and every one of those is optional or conditional. Two suppliers can express identical two percent ten net thirty terms with completely different element combinations. Terms type 14 is the one that catches people out: it means the terms exist, but not in this document, so a routine that reads ITD and finds nothing concludes there is no discount when there is.

SAC allowances and charges, and why the sign matters

Freight, handling, promotional allowances and rebates arrive as SAC segments, at header level or line level or both. SAC01 is element 248, and it is one character that flips the sign of everything after it.

Summing SAC amounts without branching on SAC01 nets a freight charge against a volume rebate and produces a number that looks reasonable and is wrong. The request codes, Q and R, are worse: they are proposals, not settled amounts, and treating them as posted allowances understates the payable. When a line disputes back and forth, that conversation usually happens in an EDI 824 application advice rather than on the invoice itself.

CTT and what the hash total can and cannot prove

CTT01 is the number of line items, meaning the count of IT1 segments in the transaction set. Compare it to the number of rows your parser produced and a mismatch tells you a loop was dropped. CTT02 is the hash total, and the standard is explicit about how it is built: values are summed without regard to decimal points, explicit or implicit, and without regard to signs, and the result truncates from the leftmost digits if it overflows.

Two consequences follow. A hash mismatch means something changed, but it cannot tell you which line or whether the difference was a sign. And a hash match is weak evidence, because a credit and a charge of the same magnitude hash identically. Use CTT01 as the real integrity check and treat CTT02 as a transport level checksum, not as an accounting control. Note that CTT is optional in the 810 summary while TDS is mandatory, so an invoice with no line count at all is still a valid file and your parser has to cope with that rather than reject it. This is the same trap as reading a clean 997 functional acknowledgment and concluding the invoice was accepted. A 997 says the syntax parsed. It says nothing about whether the amounts were agreed.

Mapping an EDI 810 to spreadsheet columns

If the point of the exercise is a sheet you can reconcile, this is the flattening that works: repeat the header on every line so the file is a normal table rather than a nested structure.

Keep BIG07 and SAC01 as their own columns rather than folding them into the amounts. The moment you bake a sign into a number you lose the ability to explain a variance to the supplier, and explaining it is most of the work. The same rule applies to the order side, which is why our EDI 850 to Excel converter page keeps the change and status codes as visible columns rather than resolving them silently.

Three way matching an 810 against the order and the receipt

The invoice is one leg of the match. The order is the 850, possibly amended by an EDI 860 purchase order change, and the receipt comes from your warehouse or from an EDI 945 warehouse shipping advice. Three practical rules save most of the arguing.

First, match quantity against the receipt and price against the order. Quantity variances are a receiving problem and price variances are a contract problem, and they route to different approvers. Second, apply tolerances per line, not per invoice: a total that falls inside tolerance can hide one line billed at double and another at half. Our note on purchase order tolerance works through where the thresholds usually sit. Third, if the order was amended after the goods shipped, match against the revision that was live at ship date. BIG06, the change order sequence number, is the field that tells you which revision the supplier billed against, and it is the reason that element is worth carrying through to your sheet.

The invoice that never arrives is its own category. If an 810 is late and the order is closed, the accrual has to come from the receipt rather than the bill, which is the situation our purchase order accrual note deals with.

When the invoice arrives as a PDF instead

Every buying desk has two invoice channels. The mapped partners send X12 and their invoices flow. Everybody else emails a PDF, and on most desks that is the larger group by count even when it is the smaller group by dollars. Those invoices get keyed by hand, which is where the cost and the errors live.

PurchaseOrders reads that half. Upload the PDF, the scan or the email attachment and it returns the same fields an 810 gives you, header and every line, as Excel, CSV or JSON. There is no template to build per supplier, so a one page invoice from a machine shop and a twelve page distributor statement both come back in the same columns. Line those up with your translator export and the two channels reconcile in one sheet instead of two processes. If you would rather push the data into a system than a spreadsheet, the purchase order API returns the same structure as JSON, and bulk purchase order upload clears a backlog of a few hundred documents in one pass.