CSV imports and exports

Merchants move data in bulk with CSV or Excel files. They export from the dashboard, edit and import again, or an integration writes a file in the same layout for the merchant to import. This page describes every file: its columns, the key a row is matched on, and what a blank cell means. Imports run in the merchant's dashboard. The one file the public API takes is the product file, through /v1/import (see Product import).

Rules every file follows

• Column names are matched without regard to case and in any order. A header that appears twice, in any casing, refuses the whole file. A header the importer does not know is not imported. The company, product, locations and cost centres files list it as a warning; the customer and order files drop it without one (the order file notes only its own export-only columns that carry values).
• Rows group into records. The company file has one row per company user, with the company columns repeated. The customer file has one row per address, the order file one row per order line, and the product file one row per variant or image. In the company and customer files a record's own columns may sit on any of its rows: the first value found counts, and rows that give different values fail the record. In the order and product files the first row of a group carries the record's own columns.
• A blank cell keeps the current value, so an import never clears a field by leaving it empty. Where a column can be cleared, the word none clears it; the tables say where. The order file does not follow this rule: a matching row replaces the order (see The order file). In the other files there are two exceptions:
  • a Tags cell replaces the record's tags, so a blank Tags cell removes them;
  • a blank cf:<name> cell clears that custom field. Leaving the column out of the file leaves the field alone, and when a record spans several rows, a value in any of its rows wins over another row's blank.
• Custom fields travel as one cf:<name> column per custom field definition, named after the definition. Leaving the column out of the file leaves the values alone.
• default and all. In a column that overrides a store or company setting, default removes this company's or user's own choice, so a company follows the store again and a user follows their company. In a list of allowed locations or cost centres, all goes back to every one.
• Create, update or both. At upload the merchant chooses whether rows that match nothing create records, whether rows that match update them, or both. A row the choice excludes is skipped and listed with its reason. In the company file the choice governs companies, not users: a new email on a company the file creates or matches always creates its user and customer account, even when "create" alone leaves a matched company as it is, and an existing user is updated only when updating is allowed.
• Matching keys decide whether a row updates a record or creates one, so importing the same file twice changes nothing the second time, except in the order file:

• References keep your spelling. The company file's Company External ID, and the External Ref of the locations and cost centres files, match ignoring case and surrounding spaces, and the record they match takes the file's spelling. In the locations and cost centres files, Company External ID only finds the row's company.
• Exports round-trip, except the order and product exports (see each file below). Every other export imports back unchanged, and importing the company file's export again writes nothing. An export's columns are the import's columns plus a few the platform computes, marked export only below; an import ignores those.
• Results list how many records were created, updated, skipped and failed (companies, customers, orders and products are counted per record, locations and cost centres per row), each failure with its row number and reason. In the company and cost centre files, a record that matches and that the file would not change counts as skipped, with that as its reason. One bad row never stops the others, with two exceptions: the company file and the customer file are checked a record at a time, so a row that fails the check leaves out its whole company, with every one of its users, or its whole customer, with every address. In the order and product files a row that fails is left out, and its record imports from its other rows. In the company file, a note about a company or user applied only in part, such as a catalogue it could not find or a user it could not add, is listed with the failures, though the company still counts as created or updated.

The company file

In User Locations and the two default location columns, a branch is named by its External Ref or its name. A cell that could name two locations (one location's External Ref and another's name, or the name of two locations) is noted and the field left as it is: use an External Ref only one location has, or rename one of them. A deleted location a user still holds is exported by its External Ref or name rather than as all, and an import that names it notes it and leaves the field as it is. The words all and none keep their meaning only while nothing they could name is called that. A location named All or None, or with that word as its External Ref, or a cost centre coded all, from before those words were refused, makes a cell reading the word a clash, which is noted and the field left as it is: change that External Ref or code, or rename the location. all with other values in a list is noted too, and the list left as it is. Where the store has a catalogue or a payment term actually called None, a none cell never clears one. In Pricing Catalogue and Payment Terms, a default location already holding that record is left alone without a note, and any other keeps its value with a note asking you to rename the catalogue or payment term; in User Visibility Catalogue, the user's catalogue is left as it is, with the same note. A Status Note that already reads None is not cleared by a None cell.

The customer file

The order file

A row group whose Order Reference matches an order replaces that order: its lines, amounts and statuses all come from the file. A blank amount reads as 0 (a blank Tax Amount or Discount Amount adds up the lines' own instead, and a blank Item Quantity is 1), a blank Payment Status reads as payment_due, a blank Type as order, and a blank Order Status follows from the payment status. Only Notes, PO Number, Delivery Instructions, Branch, Order Date and the addresses keep their value when blank, and a matched order keeps its currency. Each import also adds the order's shipping and billing addresses to the customer's address book, so every run adds them again. The order's customer is found by Email in lower case, compared exactly: a customer saved with capitals in their email is not found, and the import creates a new customer. A row that fails validation is left out, and its order imports from the rest of its rows, so a matched order loses that line.

The product file

The product export does not import back unchanged. Its Quantity is the quantity stored on the product, not the live stock, and an import sets the stock of every product it matches from that column, so take the column out of an export you import again unless you mean to set stock. A scheduled product's row fails, Bundle Components are not applied, and Taxonomies holds only each product's primary path. With SKU or Barcode as the match key every row is matched on its own, so a blank Tags or cf: cell on a variant's row clears that product's tags or custom field; the export fills those cells only on a product's first row.

The company locations file

Each address you send replaces the stored one whole. As in the company file, a none cell never clears a catalogue or payment term when the store has one actually called None, and Delivery Instructions that already read None are not cleared by a None cell.

The cost centres file

Every row for a company whose Cost Centres Managed Externally is on fails with "This company's cost centres are managed by its accounts system. Change them there, or through the API.", whichever columns it fills.

Next steps

• The company and user fields behind the company file's account rules: Companies, buyers and locations.
• The cost centres file's API equivalents: Cost centres.