Exporting an EDI file to Excel
You have an EDI file and you want its line items in a spreadsheet — perhaps to send to accounting, perhaps to check prices. EDI is a line-based structure, and turning it into a table takes a little more care than it appears.
Why does it not just open?
Drag a .edi file into Excel and every segment lands in a single cell. The reason is that EDI is hierarchical: the information about one item does not sit on one line but across several consecutive ones.
LIN+1++5410013101234:EN'
IMD+F++:::DARK CHOCOLATE BAR 100G'
QTY+21:480:PCE'
PRI+AAA:1.85'
MOA+203:888.00'
Those five lines are one row of a table: product code, description, quantity, price, amount. Converting to a table means starting a new row when you see LIN and writing the following segments into that row's columns.
Which segment becomes which column?
For a typical order or invoice the mapping is:
| Segment | Column | Taken from |
|---|---|---|
LIN | Product code (GTIN) | First component of element 3 |
IMD | Product description | Last component of element 3 |
QTY | Quantity and unit | 21, 12 or 47 by qualifier |
PRI | Unit price | AAA net price |
MOA | Line amount | Qualifier 203 |
Do not skip the qualifiers: an item can carry several QTY segments and you have to decide which one you want.
Things to watch
Get the item boundary right. Everything read until the next LIN belongs to the current item. The summary section (after UNS) does not belong to any item; mistaking its MOA+79 line for a line amount is a common error.
Keep header data separate. The order number, date and party details sit at document level, not item level. Copying them onto every row flattens the table — usually what you want, but it should be a deliberate choice.
Preserve the decimal mark. If Excel's regional setting expects a comma, a value written as 1.85 may be read as text and your sums will not work. Consider the target system's settings when you build the CSV.
Do not lose leading zeros. Codes such as GTINs are text, not numbers. If 05410013101234 is converted to a number in Excel, the leading zero disappears and the code no longer matches. The column needs to be formatted as text.
Is every file suited to a table?
No. Messages that carry a list of items — orders, invoices, sales reports, price catalogues — map naturally onto rows. But turning a CONTRL acknowledgement or a transport instruction into a table makes little sense; their structure is not a list.
Sales reports (SLSRPT) and price catalogues (PRICAT) are the two best suited: both work on a "one row per product" basis already.
A practical route
Before writing your own parser, open the file in a viewer and try exporting it straight to CSV. Seeing which columns come out also clarifies what you need when you write your own mapping.
You can do this without uploading the file anywhere; the conversion can run inside the browser, so your commercial data never leaves your device.