Vendor Price Sheet → Jobber Import ETL
Crestwood Home & Hearth sold and installed products from many manufacturers, and Jobber, its field-service software, needed every one of them in its product list. Each manufacturer publishes its price sheet in its own layout, so each Jobber import file was built one vendor at a time. This pipeline replaces that with one repeatable run.
The problem
- No two price sheets match. One manufacturer's sheet has part number, list, net, UPC, weight, and box dimensions; another has a model number and a single price.
- Jobber accepts exactly five columns: name, description, price, taxable, and category.
- The sheets carry small data problems that break an import or show up on a customer's quote: broken characters, inch marks, prices stored as text, placeholder rows, and duplicates.
The approach
- Extract: reads Excel or CSV, finds the header row under any title banner, detects the text encoding, and picks the right sheet.
- Transform: maps each vendor's columns to Jobber's five, adds the vendor prefix to the SKU, parses prices, and cleans the text.
- Validate: rows with no SKU, no price, or a $0 price, and duplicate SKUs, go to a rejects file with the reason.
- Load: writes the Jobber CSV, the rejects file, and a run report.
What the cleanup actually fixes
Every example below is a real row from a manufacturer's catalog.
| Problem | In the price sheet | In the Jobber import |
|---|---|---|
| Prices stored as text | List: "6,022.00" | Cost: 6022 |
| SKU needs a vendor prefix | 017-01-H-SM-RBC | AFD 017-01-H-SM-RBC |
| Two apostrophes used as an inch mark | 9.69’’ (246MM) CEILING MOUNT POLE | 9.69" (246MM) CEILING MOUNT POLE |
| Hidden control character from a Windows export | 8"␝ x 5.5"␝ | 8" x 5.5" |
| Escaped symbols | COVER,AFD \#435 BRDX LARGE | COVER,AFD #435 BRDX LARGE |
| $0 placeholder row | STAND - TUNGSTEN PORTABLE - BOX 2 OF 2, 0 | Rejected: zero price |
Design choices
- Configuration, not code. Each vendor is a short entry in a JSON file: which file names it matches, its SKU prefix, and which columns hold the SKU, description, and price. Adding a manufacturer takes a minute.
- Nothing disappears silently. Every dropped row lands in a rejects file with the reason, so a person can fix the source instead of guessing.
- Safe to re-run. Finished Jobber files are skipped, and SKUs that already carry a prefix don't get a second one.
- Pricing is a setting. Use the list price as is, or start from dealer cost with a markup.
Results so far
- Tested on real rows from three manufacturers, chosen because they contain the problems above.
- 15 rows imported cleanly; 3 rejected, each with the correct reason: a missing price, a $0 item, and a duplicate part number.
Try it, and what's next
- Try the live demo: run the sample price sheets or your own, right in the browser. It runs the same Python code as the repo, and files never leave your computer.
- Run it on every full price sheet in the catalog.
- Watch a shared Drive folder and convert new price sheets automatically when they arrive.