Exporting an EDI file to Excel
Most people who want to see an EDI file in Excel want one thing: the line items side by side in a table. But EDI is not a table — it is a nested tree, and flattening it takes a deliberate decision.
The problem: hierarchy does not flatten itself
An order is structured like this:
Message
├── Header (BGM, DTM, NAD)
└── Line items
├── LIN → IMD, QTY, PRI, MOA
└── LIN → IMD, QTY, PRI, MOA
In Excel one row should be one line item. So LIN opens a row, and the IMD, QTY and PRI that follow fill that row's columns — until the next LIN appears.
Dump the file into Excel line by line without that logic and every segment becomes its own row, leaving a table nobody can use.
Which columns?
The set that proves sufficient in practice for an order:
| Column | Source |
|---|---|
| Product code | LIN element 3 |
| Description | IMD element 3, last component |
| Quantity | QTY qualifier 21 |
| Unit | QTY third component |
| Unit price | PRI qualifier AAA |
| Line amount | MOA qualifier 203 |
|---|
Header information — order number, date, supplier — belongs above the table or on a separate sheet, not in the rows.
The encoding trap
Opening a CSV directly in Excel can corrupt accented characters, because Excel does not assume UTF-8. The fix is a BOM (byte order mark) at the start of the file; ediviewer's Excel export writes one already.
Choosing a separator
Excel on many European locales expects a semicolon. A comma-separated file opens in a single column there. Using ; is the safer default outside the US.
The quick route
Opening the file in ediviewer and choosing Export to Excel applies the mapping above for you: line items as rows, in the right columns, with the BOM and semicolons already handled.