EDI 940 to Excel Converter: Turn EDI 945 Warehouse Shipping Advice Files Into Spreadsheet Rows

What the W05 header on an EDI 940 actually tells you

A 940 is the instruction a depositor sends to a warehouse or third party logistics provider: pick these items, pack them, and ship them to this address. Everything a parser needs to identify the order sits in one mandatory segment at the top, W05, and two of its eight elements are the ones that decide how you file the document.

Note which one is mandatory. The depositor order number in W0502 is required; the customer purchase order number in W0503 is not. That ordering is deliberate, because the warehouse works for the depositor and has no relationship with the end customer. It also means the join key between a 940 and the 945 that answers it is W0502, not the PO number, and a system that keys warehouse traffic on the customer PO will collide the moment one order ships in two releases.

All 14 W0501 order status codes

W0501 is X12 element 473 and it is a single character. It says whether this 940 is a new order, an amendment to one the warehouse already has, or a cancellation. Miss it and you can create a duplicate shipment out of a document that was meant to replace one.

Two of these are worth a rule in your code. A of Informational Only is not an instruction to ship anything, and warehouses that treat it as one create phantom picks. X of Simulation Exercise is a test document, and it has reached production more than once in the history of this transaction set because it validates perfectly.

Where a 940 keeps its line items

The detail loop of a 940 is LX wrapping W01, not PO1. This is the single reason an 850 parser aimed at a 940 returns an empty sheet and raises no error at all: it walks the file looking for a segment that is never going to be there and reports zero lines rather than a failure.

W76 is optional. That matters more than it sounds, because unlike CTT on an 850 or an 860 you cannot rely on a control total being present in a 940. If W76 is absent, and it legitimately can be, your only integrity check is your own count of LX loops against what your parser wrote.

The W06 header on a 945, and the one element that changes everything

A 945 is the warehouse answering the 940: here is what actually left the building. The header segment is W06 and it has twelve elements, but the first one governs how you are allowed to read every row underneath it.

Everything except W0601 is optional in the standard, which in practice means your trading partner guide decides what you actually receive. Do not assume W0602 is populated just because it is the natural join key.

All 14 W0601 reporting codes, and the two that break naive parsers

W0601 is X12 element 514. It states the reporting basis of the document, and it is the most consequential single character in the whole 945.

Here is the trap. A 945 with W0601 set to X carries only the lines that had an exception. Every line that shipped exactly as ordered is simply not in the file. Load that document as though it were a full shipping advice and you will conclude that most of the order never shipped, or, worse, overwrite an open order file with a partial extract. The mirror image is W0601 set to N, No Exceptions, which is a valid and complete 945 that can be almost empty because there is nothing to report. Parsers routinely flag an N document as broken when the warehouse is telling you the shipment went perfectly. Branch on W0601 before you read a single W12.

W12, the segment that carries what actually shipped

The detail loop of a 945 is LX wrapping W12. W12 is where the ordered quantity, the shipped quantity and the difference between them all live, in three separate elements that a lot of integrations map to one column.

Two corrections to guidance that gets copied around the web. The change reason on a 945 line is W1219 and it uses X12 element 892, not W1212, and not element 371, which is the generic change reason code used elsewhere in X12. And W1204 is a quantity difference, nothing more. The standard does not say the missing units will follow later. Whether they will follow is decided by W1201, not by W1204.

The W1201 status codes that actually appear in warehouse traffic

W1201 is X12 element 368 and the full code list runs past a hundred values, most of which belong to order status reporting rather than to a warehouse shipping advice. These are the ones a 3PL feed realistically sends, and the ones worth translating into words before anyone in operations sees the sheet.

BP and CP are the pair to get right. They look almost identical in a spec and they mean opposite things to your open order file: BP leaves the balance open, CP closes it. A system that treats every short line as a backorder will carry phantom open quantities for months.

All 28 W1219 line item change reason codes

When a line ships short, W1219 is where the warehouse says why. It is optional, so it is often absent, but when your 3PL does populate it this is the field that turns a variance report into something you can act on with the supplier rather than just a list of numbers that did not match.

There is no code 18 or 19 in the published list, which surprises people writing validation and makes a contiguous range check reject valid files. Codes 01, 07, 08, 17 and 23 are the inventory family and together they cover most real short ships. Code 09, Substitution, is the one that needs a separate report, because the line looks fulfilled on quantity while the product that arrived is not the product that was ordered.

Flattening a 940 or a 945 into rows you can reconcile

The layout that works downstream is one row per detail loop, with the header values repeated on every row so the sheet stands on its own. For a 945 that means one row per W12 segment.

Then run two checks rather than one. Recompute W1202 minus W1203 per line and compare it to W1204, because a mismatch means the warehouse system and your own arithmetic disagree about what shipped. Then compare the sum of your W1203 values to W0301 in the W03 summary segment when it is present. W03 itself is optional, but if it is there the number of units shipped in W0301 is mandatory, so it is a genuine control total on the rows.

How 940 and 945 sit next to 943, 944, 947 and 856

The warehouse transaction sets pair off, and searching for one of them usually means you are about to meet its partner. Getting the direction right is most of the work.

The 940 and 945 pair is the outbound flow and the 943 and 944 pair is the inbound flow, which is the answer to the question people usually mean when they ask about 940 versus 943. The relationship with the 856 is different again: a 945 and an 856 can describe the same physical truck from two vantage points, the warehouse reporting to its depositor and the depositor announcing to its customer. In plenty of 3PL setups the 856 is generated from the 945, so they are sequential rather than alternatives. Our reference on the EDI 856 advance ship notice covers the hierarchical loop that makes the ASN a different shape from everything on this page.

Where pick tickets and warehouse releases that are not EDI fit in

Almost no warehouse operation runs one channel. The 3PL on a VAN sends a 945 and the overflow warehouse, the co packer and the contract manufacturer email a PDF pick ticket, a packing list or a shipment confirmation. That second stream carries exactly the same commercial risk, and it is the stream where somebody keys quantities into a spreadsheet by hand at the end of the week. The useful move is to make both channels write the same columns: order number, shipment reference, ship date, then one row per line with quantity ordered, quantity shipped, unit of measure and part number. Agree that layout once and the EDI feed and the document feed land in the same variance report. That is the half of the problem PurchaseOrders solves, and it is deliberately the half an EDI translator does not. For the order that starts the whole exchange see the EDI 850 to Excel converter, for the supplier acknowledgment see the EDI 855 to Excel converter, and for amendments to an order already placed see the EDI 860 to Excel converter. If your files arrive as raw X12 and you need the general rules for delimiters and envelopes first, start with the EDI to Excel converter guide, and if the acknowledgment layer is where things go quiet, our reference on EDI 997 error codes explains why a clean 997 is a syntax pass rather than a business confirmation.