Prepare data from spreadsheets or another system
Plan a move from spreadsheets or another purchasing tool, and fill Data Exchange workbooks so they import cleanly the first time.
You can import suppliers, materials, units, barcodes, supplier catalogs, product matches, bins, bin allocations and open purchase orders. Closed purchase orders, past receipts and counts from another system are not imported; keep them in your old system or as files.
Plan the move
Import related data in this order, and check each batch before starting the next:
- Suppliers.
- Materials. Reuse materials Zapstra already created from Shopify products rather than adding a second material for the same item.
- Material Units and Barcodes for each material.
- Supplier Catalog: what each supplier sells, with their SKU and unit.
- Product Matches: which supplier item is which material.
- Bins, if you use them.
- Opening stock, through opening inventory. Importing a material does not give it a stock balance.
- Open purchase orders, through Import open purchase orders from another tool.
Keep an untouched copy of your old system's export, and work in a copy. Get each blank workbook from Data Exchange; see Import and export with Data Exchange.
Supplier vendor codes, supplier SKUs, your material SKUs and barcodes identify different things. Decide which ones you will use before you start. Check base units and supplier units carefully; see Configure units, packs and barcodes.
Identifiers by data type
| Data type | Identify records with | Import first |
|---|---|---|
| Suppliers | Vendor Code, Supplier Name; Match Vendor Code or Match Supplier Name to update one | — |
| Materials | SKU, Name, Type, Base UoM for new ones; Match SKU or Match Barcode, plus Match Store / Channel if needed, to update | Suppliers |
| Material Units | Material, unit code and conversion fields | Materials |
| Barcodes | Material, Barcode, Symbology, UoM Code and scope | Materials and units |
| Supplier Catalog | Vendor Code and Supplier SKU | Suppliers |
| Product Matches | Vendor Code, Supplier SKU, material and Operation | Supplier catalog and materials |
| Bins | Warehouse / Location and Bin Code | Locations and stock pools |
| Unassigned Bin Allocations | The rows in the downloaded workbook | Activated stock and bins |
| Purchase orders | PO Number, Supplier, and SKU, Barcode or Supplier SKU | Suppliers and materials |
The workbook for each data type is the full field reference. Keep its sheet names and headings.
Suppliers
A minimal new supplier. Contact Email is where purchase orders are sent:
Vendor Code,Supplier Name,Status,Currency,Contact Email,Lead Time Days
NORTHWIND,Northwind Packaging,active,USD,orders@northwind.example,14
Download this sample Suppliers CSV and replace the row with your own suppliers.
The template also has Legal Name, Contact Name, Contact Phone, address fields, Tax ID, Payment Terms and Incoterms.
To rename a supplier, identify it with Match Vendor Code or Match Supplier Name and put the new name in Supplier Name.
Materials
A minimal new material:
SKU,Name,Type,Base UoM
MAILER-KR6,Kraft mailer,VERP,ea
Download this sample Materials CSV and replace the row with your own materials.
- Type is one of ROH, HALB, HIBE or VERP. See Create and manage materials.
- Type and Base UoM are set when the material is created. Leave them out of update rows.
- On an update, the match fields must point at exactly one material.
- Match Store / Channel is the store name exactly as shown in the app.
- SKU can add a SKU to a material that has none. It can't rename or clear an existing SKU.
- Product and Variant are for information. Change Name to rename a material.
- Change Status in a separate upload from other changes.
- A new weight needs a value and unit. New dimensions need length, width, height and unit.
Units, barcodes, catalog and matches
- Create the material first, then its units and barcodes.
- Format barcode cells as text so leading zeros are kept. A barcode on a case must point at the case unit, not the single item.
- A Supplier Catalog row can exist before it is matched to a material. Its fields include Supplier UoM, Minimum Order Quantity, Order Multiple, Lead Time Days, Preferred, Country of Origin and Status. Set prices through supplier prices, not this file.
- In Product Matches, Operation can propose, approve, reject or deactivate a match. Choose it row by row.
Blanks and clearing values
When you update records, a blank cell keeps the existing value. Where a field can be cleared, the workbook instructions tell you to use __CLEAR__. Clearing one weight or dimension field clears the whole group, so check the preview.
Avoid formulas. Use a decimal point in quantities, and dates in the format the workbook asks for.
File limits
| Limit | Maximum |
|---|---|
| Uploaded file | 8 MiB |
| Uncompressed Excel contents | 32 MiB |
| Sheets | 16 |
| Data rows per sheet | 10,000 |
| Total data rows | 25,000 |
| Columns per sheet | 128 |
| Characters in one cell | 32,767 |
Smaller batches are easier to review. When you split a file, keep each record's rows together.