The short answer
A spreadsheet order is ready for automated import when every line can be read, checked and accounted for without guessing. Your ERP is the business system that holds orders and stock. Test outside its live records. Compare extracted rows with an agreed answer, hold unclear lines and check that resending a file cannot create another order. This pack tests one layout, not every spreadsheet in the mailbox.
The five traps, in under three minutes
In under three minutes you will know the five traps one spreadsheet order can hide, and how to test any software on them before it touches a real order.
Every example in the video is made up to show the method.
The words of the video
Do your customers send their orders as spreadsheets? Before software reads them into your system, give me three minutes. I'll show you one awkward test file, and the five traps it checks.
First, what are we talking about? A customer emails a spreadsheet with their order. Someone on your team copies each row into the system you run the business on. Automating it means software reads the rows, and creates the order for you.
Here's what I've learned. Spreadsheets look tidy, so people trust them. But one file can hide several traps. And each one can quietly become a wrong order.
Here's a made up example. Customer A sends one file with two tabs. On the first tab, one line has no quantity at all.
So here are the five traps. First, the empty quantity. Software can turn an empty cell into zero, or into one. Neither is true. I'd check this one first. So the line waits, and someone asks the customer.
Second, boxes. The customer orders three boxes of bolts, and your system already sells them by the box. If software multiplies by ten, it enters thirty boxes. The right answer is three. Otherwise that's ten times the bolts on the van, and a call to put it right.
Third, a code with two meanings. The customer's code could be one of two seals in your catalogue. Software shouldn't pick one quietly. A person chooses once, and the choice is kept for next time.
Fourth, the same washer appears on both tabs. It might be a second delivery, or a copy by mistake. Don't merge it, and don't drop it. Keep both, and ask the customer which they meant.
Fifth, send the same file twice, into a test copy of your system. If a second order appears, that's the trap. The second time, nothing new should be created. I always check it again with the file renamed, because a new name shouldn't fool it.
So that's the test. One made up file, and five traps. An empty quantity, boxes against single items, and a code with two meanings. The same item twice, and the same file sent twice. Try any software on it before it touches a real order.
The made up test file and its answers are free on shurco.ai. Rebuild it in ten minutes, and you'll see what any tool does with these traps.
1. Rebuild the awkward workbook
Rebuild the made up workbook in Excel with tabs called Order and Extras. Copy columns A to D into the stated rows. Merge A1 to D1 on both tabs. CSV files cannot retain tabs or merged cells. Keep the row map as your test record.
2. Agree the catalogue and order rules
For this made up test, Customer A uses A01 for S100, A03 for S300 and A05 for S500, all sold individually. A02 maps to S200, sold by the box of ten. A04 could mean S400 or S401. Both tabs belong to order CA101. A person must decide whether repeated item A01 is additional or copied.
3. Compare every source row with the expected reading
Read the workbook by hand or with your proposed tool. Compare all six item lines with the expected rows, excluding the headers and column headings. Keep each tab and row reference. A blank quantity stays unresolved. Missing in the answer sheet means no quantity was supplied, not text to import.
4. Resolve holds and test a repeat import
Hold the whole order while any line needs review. Ready means a line passed the reading checks, not permission to submit it alone. Record clarifications and update the expected answer. Import the resolved file twice in a test system. Check that the second attempt creates nothing and an interrupted import can be inspected before retrying.
The awkward input workbook as a row map
Made up example. Every row here is made up to show the method; recreate both tabs and merge A1 to D1 on each tab in Excel.
| Tab | Source row | A | B | C | D |
|---|---|---|---|---|---|
| Order | 1 | Customer A order CA101 | |||
| Order | 2 | Customer item | Description | Quantity | Order unit |
| Order | 3 | A01 | M8 zinc washer | 20 | each |
| Order | 4 | A02 | M6 bolts box of 10 | 3 | box |
| Order | 5 | A03 | Steel bracket | each | |
| Extras | 1 | Customer A order CA101 | |||
| Extras | 2 | Customer item | Description | Quantity | Order unit |
| Extras | 3 | A01 | M8 zinc washer | 20 | each |
| Extras | 4 | A04 | Seal standard | 2 | each |
| Extras | 5 | A05 | Tube 2 m length | 4 | each |
Download this sheet as a file for Excel
The expected rows before review
Made up example. Every row here is made up to show the method; quantities describe the expected reading and no line authorises an import.
| Source | Customer item | Catalogue item | Read quantity | System unit | System quantity | Decision | Reason |
|---|---|---|---|---|---|---|---|
| Order row 3 | A01 | S100 | 20 | each | 20 | Hold | Repeated at Extras row 3 |
| Order row 4 | A02 | S200 | 3 | box of 10 | 3 | Ready | Three boxes; do not import 30 boxes |
| Order row 5 | A03 | S300 | Missing | each | Missing | Hold | Quantity must be confirmed |
| Extras row 3 | A01 | S100 | 20 | each | 20 | Hold | Repeated at Order row 3 |
| Extras row 4 | A04 | Unresolved | 2 | each | 2 | Hold | Choose between S400 and S401 with evidence |
| Extras row 5 | A05 | S500 | 4 | each | 4 | Ready | Four lengths; 2 m is an item dimension |
Download this sheet as a file for Excel
Import checklist
Confirm the workbook setup. Order cell C5 must be empty. Check that both header rows have merged cells in the rebuilt workbook.
Confirm that both tabs were read, headings were excluded and every item line has a source reference. Check customer, order number, item, quantity and selling unit against the expected answer.
Confirm that holds reach a named reviewer with their reasons. Resolve them before submission. Test missing customer accounts, invalid item codes and rejected writes in a separate copy.
Confirm repeat detection using the customer and order reference as well as the file. Rename the file and resend it. Check the created order and all its lines after any interrupted attempt.
What can change the answer
Hidden rows, formulas, revised templates and different selling units need further examples. A formula may show an old saved value. Decide whether to calculate it or request review. Test corrected orders too. A correction needs an amendment route, not silent rejection as a repeat or creation of another order.
When this is the wrong tool
This pack checks reading and handling rules. It cannot approve prices, delivery dates or credit. Drawings and free text need separate tests. Without an isolated test system, compare proposed rows offline and keep live entry manual.
What to do with the result
Record each checklist result and keep the output. Missing lines, guessed quantities and unexplained duplicates block live use. Then test real files of the same format. Agree who handles holds, checks failures and stops imports before considering a limited live trial.