Most small parts retailers receive availability from suppliers the same way: a CSV attached to an e-mail, a portal export, or a file dropped on a share. Someone opens it, filters it, and types quantities into the shop system and the marketplace listings. It works until the file is late, the columns move, or two people update the same part on the same afternoon. This article describes a routine that removes the re-keying without pretending the files are cleaner than they are.
Start from the file you actually get
Before any tool is involved, keep three real exports from each supplier and look at them side by side. Note the column names, the part-number format, how zero stock is written (0, blank, “N/A”, “call”), the date format, and whether the file has a header line at all. Supplier files rarely follow the CSV convention exactly, but the convention is still the best reference: an optional header line, one record per line, and double quotes around any field that contains a comma, a quote or a line break. Files that break those rules are the ones that silently shift columns during an import.
Write down, per supplier, the answers to four questions: which column is the part number, which column is quantity, which column (if any) is cost, and what the file looks like when a part is discontinued. Those four answers are the whole mapping. Everything else in the file can be ignored on the first pass.
Map columns once, by supplier
The re-keying habit comes from treating every file as new. Instead, save one mapping per supplier: file column to your field, plus the small rules that make the data usable. Typical rules are trimming spaces, upper-casing part numbers, removing a supplier prefix, and translating their stock words into numbers. The mapping is data, not code; when a supplier changes a column name, you change one line in the mapping and the next import works.
Keep the mapping next to a sample file and a note of the date you last checked it. When a file arrives with a column you have not seen, the import should stop and ask rather than guess. A stopped import costs ten minutes; a guessed one can cost a weekend of oversells.
Validate every run before anything moves
Validation is cheap and catches most problems. On every import, check at least the following and reject the file if any check fails:
- Shape: the expected number of columns on every line, and a header that matches the mapping.
- Keys: every row has a part number in the agreed format; duplicates within the file are reported, not silently overwritten.
- Quantities: numeric, not negative, and within a sanity band (a supplier that normally reports 0 to 40 units should not suddenly report 40,000).
- Coverage: the file covers roughly the same number of parts as last time. A file that is half the usual size is usually a partial export.
- Freshness: the file date is newer than the last one you imported.
Rejected files go to a short list with the reason, and the previous good figures stay in place. That is the rule that makes automation safe: a bad file never replaces good data.
Reconcile, then publish to your channels
Supplier availability is an input, not your stock figure. Your own shelf count, open orders and returns still matter. The reconciliation step combines them into one number per part that you are willing to sell against, and only that number goes out to the shop and the marketplaces. If you sell on Amazon.ca or eBay.ca, the published quantity should come from this reconciled figure on a schedule, not from the raw supplier file.
Two habits keep this honest. First, record where each figure came from (supplier file name and date, or a manual count) so a surprising number can be traced in a minute. Second, review the exceptions rather than the whole list: parts that dropped to zero, parts that jumped, and parts the supplier stopped listing. Ten minutes on exceptions beats an hour scrolling through a spreadsheet.
What this looks like in ORCA
In our own operations one supplier feed is imported every day and reconciled against marketplace reports before quantities are published through the Sell module, which runs for one Amazon.ca and one eBay.ca account. The Stock module is in development: supplier imports and reconciliation work today, while the central ledger with barcode movements and low-stock alerts is still being built. The Integrations page lists exactly which connections exist and which are proposed, and the pricing page shows what a feed import setup includes. New supplier formats are mapped as part of setup, not guessed at.
What to do this week
- Collect three recent exports from each supplier and write the four-question mapping for each.
- Decide how zero stock, discontinued parts and missing values are written in each file, and add those rules to the mapping.
- Choose the five validation checks above and agree what happens when a file fails: who is told, and what stays unchanged.
- Separate supplier availability from your own stock figure, and publish only the reconciled number to your channels.
- Run the routine by hand for two weeks before automating it; the exceptions you see are the specification for the automation.
Sources
- RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files – IETF / RFC Editor (accessed 2026-10-21)
