EDI, APIs and spreadsheets: a practical integration guide for mid-size manufacturers and shippers

A mid-size manufacturer or shipper usually ends up with all three: an EDI link to the one big customer who insists on it, a few carrier or supplier APIs, and a set of spreadsheets that move data between everything else. That is normal. The problem starts when each connection is its own one-off.

This guide explains what each option is good for, how to choose, and how to keep the whole thing maintainable.

What is the difference between EDI, an API and a spreadsheet?

EDI (electronic data interchange) is a long-standing standard for exchanging business documents between companies. In North America that mostly means ANSI X12, with documents like the 850 purchase order, 856 advance ship notice (ASN), 810 invoice and 214 shipment status. In other regions EDIFACT is common. Messages are structured text, usually exchanged in batches through a VAN (value-added network) or directly via AS2 or SFTP.

An API is a live request and response over the internet, typically JSON over HTTPS. You ask “what is the status of this load” or “create this shipment” and get an answer in seconds.

A spreadsheet or file drop is a CSV or Excel file emailed, uploaded or placed on an SFTP server, then loaded by someone or something at the other end.

	EDI	API	Spreadsheet or file drop
Speed	Batch, minutes to hours	Real time	Manual or scheduled
Who sets the format	The trading partner or a standard	The provider	Whoever wrote the file
Setup effort	Medium to high, per partner	Medium	Low
Handles volume	Yes	Yes	Poorly
Error handling	Acknowledgments (997/999)	Status codes, retries	Often a phone call
Typical use	Large retailer, OEM or carrier requirements	Rates, tracking, order status, portals	Low-volume partners, one-time loads

When do you have to use EDI?

When someone with the bigger purchasing power requires it. Large retailers, automotive and aerospace OEMs, and many big carriers and 3PLs will not change their process for a supplier. If they send you 850s and expect 856s and 810s back, you build to their spec, and failures can bring chargebacks or lost business.

The practical advice is to do EDI properly once. Use a provider or a mapping engine, document each partner’s requirements, and build a test routine, since most partners require you to pass a certification round before going live.

When is an API the better choice?

When you need an answer now, or when the process is conversational rather than batch.

• Getting a freight rate or booking a pickup while a customer is on the phone
• Tracking a load without waiting for the next status file
• Checking inventory or order status from a customer portal
• Pushing a confirmed order into your ERP the moment it is placed

APIs also fail differently. They can be down, rate-limited or changed by the provider without much notice. Build retries, logging and alerts from the start, and expect to maintain them.

Are spreadsheets ever the right answer?

Sometimes, yes. If a partner sends you ten orders a week in an Excel file, a full EDI project is overkill. A structured template, validation on the way in, and a clear error message when a row is wrong can serve for years.

The trouble comes when the spreadsheet is the integration for something that has grown. Signs it is time to move on:

• Someone spends hours each week copying data between systems
• Errors are found by a customer, not by the process
• Only one person knows how the process works
• Volume doubled and the process did not change

Often the spreadsheet is a good prototype of what the real integration should do, because it captures the rules people actually apply.

How do I choose for a given trading partner?

Work through these questions in order.

1. Does the partner require a specific method? If yes, that settles it.
2. What is the volume and how often does it change? High and steady points to EDI or API. Low and occasional points to a file drop.
3. Does anyone need an answer in real time? If so, an API.
4. What does an error cost? A missed ASN with a chargeback attached deserves more rigor than an internal report.
5. Who will maintain it? A connection nobody owns will fail quietly.

How do you avoid building a pile of one-offs?

The key idea is a single integration layer between your systems and the outside world. Instead of connecting every partner directly to your ERP in its own format, you translate everything into one internal shape.

In practice:

• Define a canonical version of your core documents: order, shipment, invoice, inventory. Your ERP and your other systems only know that shape.
• Build adapters at the edge that translate each partner’s EDI map, API or spreadsheet into the canonical form, and back.
• Log every message in and out with its status, so you can answer “what happened to PO 4471” in one place.
• Keep partner-specific rules in configuration where possible, not scattered through code.

When a new customer arrives, you add one adapter. When you replace your ERP, you change one connection, not forty.

What about legacy systems that cannot call an API?

This is common with older ERPs and green-screen systems. A few workable patterns:

• A scheduled job that exports flat files, picked up by the integration layer
• Database-level reads (carefully, read-only) to feed outbound documents
• A thin API wrapper written around the legacy system’s key functions
• Screen-level automation as a last resort, since it is fragile

None of these require replacing the ERP first. Integration is frequently the cheapest way to get value from an old system.

What should you monitor?

Integrations fail silently unless you look. At a minimum:

• Messages that were sent but never acknowledged
• Documents that failed validation, with the reason
• Volumes that drop to zero from a partner who normally sends daily
• Mismatches between what you shipped and what you invoiced

Send these to a person who owns them, daily, in plain language. A dashboard nobody opens is not monitoring.

What does it cost to get this wrong?

Not a dollar figure we can quote in general, but the pattern is a common one. Chargebacks for late or inaccurate ASNs. Invoices that do not match POs and sit unpaid. Staff who spend their day retyping data. Customers who see your company as hard to do business with. Fixing these is rarely a technology problem alone. It is a matter of picking the right method per partner and giving the connection an owner.