Export, paste, VLOOKUP, import
Every morning someone exports orders from the web shop into a CSV. They paste it into a master spreadsheet with a tab of lookups, some formulas and a macro somebody recorded years ago. They fix the rows that come out wrong, save a new CSV, and import it into the stock system. Then they do something similar for the accounts.
The spreadsheet has a name like Daily_Import_MASTER.xlsx. It has columns that are hidden because unhiding them makes it break. If it is Friday and the usual person is off, the job either waits or somebody does it nervously from a sticky note.
Why the spreadsheet became the integration
Nobody designed it this way. The spreadsheet appeared because it was the fastest way to move data between two systems that could not talk to each other, and it worked. Over time, it picked up jobs: converting product codes, adding VAT, splitting names, filtering out test orders, flagging odd ones for a look.
That is the key point. The spreadsheet is no longer just a pipe. It holds business rules, and those rules exist nowhere else. That is why simply connecting the two systems directly never quite happened: the connection would have had to know everything the spreadsheet knows.
What the spreadsheet layer costs
| Problem | How it hurts |
|---|---|
| Manual steps every day | Time, and work that waits when the person is off |
| Silent errors | A pasted range one row short, a formula dragged too far |
| Stale data | Systems are only as up to date as the last run |
| Hidden rules | Nobody else can explain why a column is calculated that way |
| Version confusion | Several copies, each slightly different |
| No audit trail | Nobody can tell what changed in yesterday's import |
The silent errors are the dangerous ones. When a formula goes wrong, the import still succeeds. It just loads the wrong numbers, and nobody finds out until a customer, supplier or accountant does.
There is also the question of timing. Because the spreadsheet step runs once a day, every system downstream is always a day behind. Stock shown on the website is yesterday's stock. Sales figures in the accounts are yesterday's sales. Decisions get made on numbers that are already out of date.
How we replace the glue
- Walk-through. We watch the person run the process start to finish and record every step, including the manual fixes they make without thinking.
- Rule extraction. Every formula, lookup, filter and macro in the spreadsheet is written out as a plain rule. We check each with the team, and drop the ones that no longer matter.
- Direct connection. We connect the source and destination systems through their APIs or supported import routes, for example Shopify to your stock system and on to Xero or QuickBooks.
- Mapping tables. Lookups like product code conversions move into a small, editable table that the business maintains, rather than a hidden tab.
- Validation. Each record is checked before it is written: required fields present, codes recognised, totals that add up. Failures go to a review list rather than being loaded.
- Scheduling and alerts. The flow runs on its own, at a frequency that suits the data, and tells a named person if anything fails.
Tools like Make, n8n or Zapier handle simple versions of this well. When the rules are many or the volumes are high, we write a small integration service instead. We tell you which fits your case.
What the mornings look like after
The export, paste and import routine stops. Data moves between the systems on its own, with the same rules the spreadsheet used to apply, now written down and testable. The person who used to run it spends that time on work that needs a person.
When something does not fit, it lands in a short review list with a reason, not buried in row 412 of a sheet. And the product code table that used to be a hidden tab can be updated by anyone with permission.
Recognise your spreadsheet?
- Data moves between systems by export, spreadsheet and import
- The spreadsheet has formulas or macros only one person understands
- The process stops when that person is away
- Errors in the import have reached customers or the accounts
- Several copies of the master file exist
- You are not sure the imports are complete