Using a spreadsheet (CSV, XLSX, or XLS) to format product data quickly, you can easily import products into your Retail POS store in bulk.
Before you start, prepare the following product information:
-
Names of your products
-
Any variations, such as sizes, colors, and flavors
-
Retail prices of your products
-
These are tax-exclusive prices if your store displays prices exclusive of tax
-
These are tax-inclusive prices if your store displays prices inclusive of tax
-
- Supply prices of your products
-
Existing barcode/SKU numbers
-
The exact names of the taxes you have set up in Retail POS (click here to learn how to set these up)
1. Download the Retail POS product template
To begin, download the Retail POS product template spreadsheet file. You can find this template as a downloadable attachment at the bottom of this article. This is a pre-formatted template that includes all the relevant product information that can be captured in Retail POS.
We strongly recommend opening the Retail POS product template spreadsheet file in Excel or using free spreadsheet software such as Open Office to retain the formatting required.
2. Understanding the product fields
Once you download the Retail POS template, you will need to add your product information.
There are three different types of products that can be added to your store and each will require slightly different information:
- Standard product:
- Single product with a single SKU and its own inventory.
- Single product with a single SKU and its own inventory.
- Product with variants
- A product that is offered in different variations, such as size or color — each variant is a unique SKU with its own inventory
- A product that is offered in different variations, such as size or color — each variant is a unique SKU with its own inventory
- Composite product
- Package different products together and create a product bundle, multi-pack, or fractional products
Below is a glossary of each product data column found within the Retail POS product template spreadsheet file and what information is required:
| Field name | Description | Requirements |
|---|---|---|
| ID |
The ID is a unique product identification number Retail POS automatically generates for each product when a product is created. The ID is used for identifying products and will mostly be used when editing existing products.
|
Leave the ID field blank when you're creating new products. If this field has any data, the products will be ignored and not created.
|
| Handle |
The handle is a unique identifier that is primarily used for the Shopify e-Commerce integration. This links products between Retail POS and Shopify to ensure product information is correctly synced. Shopify also uses the Handle to form the product URL, which forms part of your Search Engine Optimisation (SEO). For more information, read our article about linking existing Retail POS (X-Series) and Shopify products.
|
The handle is unique from the SKU. Retail POS will automatically generate Handles for products when left blank. These are generated for SEO best practice, i.e "tiger-t-shirt" If you wish to create Handles manually, each individual word should be hyphenated. When creating products with variants, the handle must be the same for each variant. When creating composite products, the handle for each product within the composite product must be the same as the composite product itself. You should import any existing products from Shopify into Retail POS in order to link the handles correctly, then treat Retail POS as the master record for creating future products (and adding them to Shopify).
|
| SKU |
The SKU (Stock keeping unit) code for your product. One of the biggest benefits to retailers is the ability to use a scannable product barcode as the SKU, which allows you to scan the barcode on the sell screen in order to add it to a sale. The SKU also adds a uniquely identifiable metric in reporting that allows you to drill down to a specific product.
|
Every product must have a unique SKU. Make sure it only has letters and numbers. You cannot have spaces or symbols in here. This can be your own existing identifier or an existing barcode number on the product. If you don’t have existing SKUs, leave this column blank and Retail POS will automatically generate unique SKUs for your products. Multiple SKUs can be captured on one product, refer to our UPC, EAN, ISBN, ITF, JAN, and other scannable barcodes in Retail POS for instructions on how to do this.
|
| Composite_Name |
If the product is a composite product, you will need to add the exact name of each existing product that will be contained within the Composite product here.
|
The Composite_Name must be exactly as it appears in Retail POS. Refer to the Adding composite products dropdown for more information.
|
| Composite_SKU |
Add the SKU of each existing product that will be contained within the Composite product here.
|
The Composite_SKU must be exactly as it appears in Retail POS. Refer to the Adding composite products dropdown for more information.
|
| Composite_Quantity |
The quantity of each existing product that will be contained within the composite product.
|
This can be a whole number or a decimal.
|
| Description |
The description of your product.
|
This field can have letters, numbers, and symbols.
|
| Name |
This is what your product is called; e.g "Tiger T-shirt". The name will form the cashier-facing product name on the sell screen. It will also be displayed on customer-facing material, such as receipts, and printed on labels. It is also used for admin-facing functions, i.e reporting, and stock orders/transfers.
|
The name can have letters, numbers, and symbols in this field but do not include #, $, or & symbols.
|
| Product_category |
The product category is the high-level category that a product belongs to. e.g 'Clothing' or 'Accessories' or 'Services'. Used for product grouping to optimize filtering and searching. This can be used to define reporting on a certain group of products, and to set promotions.
|
The product category can be made up of letters, numbers, and symbols. E.g "Clothing", "Accessories", or "Services". Leaving this column blank for existing products will overwrite any existing category. If you do not wish to replace these types, delete the column (along with the "Product_category" header). A category must have already been created in Retail POS prior to importing. Any category that has not yet been created will be ignored when imported.
|
| Variant_Option_ One_Name |
The attribute for products with variants allows you to create and sell products that fall within the same family but are offered in variations. This makes adding a variant product to the sell screen quick and accurate. It also streamlines product management and reporting.
|
The attribute needs to define the type of variation that the product is offered in. E.g. "color", "size", or "flavor". A variant product can have up to three individual attributes (using the subsequent Variant_Option_Two_Name and Variant_Option_Three_Name columns) Refer to the Adding products with variants dropdown for more information.
|
|
The value (for products with variants) allows you to create and sell products that fall within the same family but are offered in variations. This makes adding a variant product to the sell screen quick and accurate. It also streamlines product management and reporting.
|
The value needs to define the type of variation for the selected attribute. E.g. "red", "large", or "strawberry". Refer to the Adding products with variants dropdown for more information.
|
|
| Tags |
Tags are lower-level categories that the product belongs to. Much like the product category, the tags are used for product grouping to optimize filtering and searching. These can be used to define reporting on a certain group of products, and to set promotions.
|
Tags can be made up of letters, numbers, and symbols. E.g "Casual", "Cotton" "Summer Range", or "Mens". There is no limit to the number of tags that can be added to a product. Leaving this column blank for existing products will overwrite any existing types. If you do not wish to replace these types, delete the column (along with the 'Type' header)
|
|
(E-Series integration only) |
Active online shows if the product is available in your webstore.
|
Set this to 1 if you want the product to be displayed on your E-Series storefront, or 0 if you don't. If you leave this blank, no change will be made to the field.
|
| Supply_price |
The regular supply price for your product. The supply price is added when creating a product. This is the value that you purchase the item for per unit. It can be tax exclusive or inclusive, depending on your accounting needs. If your product price fluctuates, you can specify the exact supply price for each order when you complete a stock order.
|
It is strongly recommended to include a supply price when creating products. This enables accurate reporting/accounting as supply price directly affects both cost of goods sold (COGS) and average cost. If the supply price isn't added before an inventory movement/sale is made with the product, the COGS will be calculated at $0, affecting the average cost and reporting. The supply price should be entered as a numerical value. Don't include a currency sign.
|
| Retail_price |
This is the dollar value that a product will be sold for. It offers the ability to set, edit and maintain the price your customers will be charged for a product.
|
The Retail Price should be entered as a numerical value. Don't include a currency sign Tax-exclusive stores: Tax-inclusive stores:
|
| Tax_name (INCLUSIVE ONLY) |
For tax-inclusive stores, this is the name of the tax rate you want to use for this product.
|
This is automatically set to your default tax rate when left blank. Leaving the field blank will not overwrite existing data. If you want to update a rate to default tax, type "Default tax".
|
| Outlet_Tax_Your_ Outlet_Name (EXCLUSIVE ONLY) |
For tax-exclusive stores, this is the name of the tax rate you want to use for this product in this outlet.
|
You need to create a column for each outlet. If you wish to use the default tax rate, type "Default tax". Leaving the field blank will not overwrite existing data. If you want to update a rate to default tax, type "Default tax".
|
| Account_code |
Used for Xero integrations only. If you want sales of this product to post to an account other than your default sales account (set in the Xero integration settings), add the account code you'd like to post to here.
|
Leave this blank if you don't use Xero, or want to post to the default account.
|
| Account_code_purchase |
Used for Xero integration. If you want stock orders of this product to post to an account other than your default purchases account, add the account code you'd like to post to here.
|
Leave it blank to use default or if you don't use Xero.
|
| Brand_name |
The brand can be used for filtering, searching, and reporting.
|
This should be the name of the brand your product falls under. E.g "Nike" This can be made up of letters, numbers, and symbols.
|
| Supplier_name |
The supplier name is used when creating purchase orders. It can also be used to set promotions and for reporting purposes.
|
The supplier's name can be made up of letters, numbers, and symbols.
|
| Supplier_code |
The supplier code is the code used by a supplier to identify the product. It can be useful when creating a purchase order. It can also be used for product filtering and searching.
|
The supplier code name can be made up of letters, numbers, and symbols. Not all suppliers will use their own codes or these may be the same as your SKUs/product barcodes, which is why the field is not mandatory.
|
| Active |
Active is the function that determines whether a product is available to be sold. This allows retailers to keep products in their catalog but make them inactive for selling. This is particularly useful for seasonal products, such as holiday-related items, and for reporting on older products that are no longer sold.
|
Set this to 1 if you want the product to be available to be sold, or 0 if you don't. If you leave this blank, it will default to active (available to be sold).
|
| Track_inventory |
Track inventory allows retailers to enable or disable inventory management. This is useful for services that have no inventory limitations.
|
Set this to 1 if you want Retail POS to keep track of your inventory levels, or 0 if you don't. If you need to generate cost figures, you need to track inventory.
|
| Inventory_ {outlet_name} |
The inventory is the quantity of each product available at an outlet. This allows you to monitor stock levels within Retail POS for accurate sale and replenishment data.
|
Inventory MUST be added at the same time as, or after, a supply price is added. Use the Adding inventory to your products guide to learn how this should be achieved.
|
| Reorder_point_ {outlet_name} |
The reorder point is used when creating a purchase order. All products at or below the re-order point can be quickly added to the purchase order. This is NOT an automatic re-order function.
|
The reorder point should be set to the minimum inventory level you wish a product to reach before it needs to be re-ordered. E.g when a product inventory level reaches 5 (or below), it can be added to the purchase order via the re-order point function. If your store has more than one outlet, this column will appear multiple times with each outlet's name. Enter this as a numerical value.
|
| Restock_level_ {outlet_name} |
The reorder quantity is used when creating a purchase order. Determines the quantity to re-order for an outlet when a product is at or below the re-order point. This is NOT an automatic re-order function. |
The reorder quantity should be set to the quantity you wish to re-order for a product once it reaches the Re-order point. If your store has more than one outlet, this column will appear multiple times with each outlet's name. Enter this as a numerical value. |
| Loyalty_Value |
The loyalty value is assigned to a product aside from the default loyalty value from your store. |
When a value is entered in the loyalty value field, it will be assigned to the product aside from the store's default loyalty value. If the field is left blank, the product will be automatically assigned the default loyalty value. Use our article Setting up and using Loyalty in Retail POS (X-Series) for more information. |
3. Adding standard products
A standard product is a single product with a single SKU and its own inventory.
When creating a standard product, you will first need to give your product a name. Each product name must be unique from other products, and we strongly recommend not using vague names such as "t-shirt" if your store is likely to have more than one t-shirt product. Instead, you would call this "Lightspeed Tiger T-shirt".
You can choose to create your own SKU for this product or have Retail POS automatically generate a SKU by leaving the field blank.
Likewise, the handle can be created manually by entering a supported format handle, or simply leaving this field blank for Retail POS to automatically generate one.
We strongly recommend adding a supply price for the product to ensure your cost of goods sold reporting is calculated correctly moving forward.
Your product will also require a retail price. Leaving this column blank will result in the product being priced at $0.
To add additional standard products, simply move to the next line and repeat the steps above as many times as required.
4. Adding products with variants
A product with variants is a product that is offered in different variations, such as size or color, and each variant is a unique SKU with its own inventory.
When creating a product with variants, it is important that each variant product has the exact same name. This is what will group the products together. We recommend using the copy + paste function to ensure that each variant matches. Any variant that does not match will be ignored when imported.
The handle can be left blank for Retail POS to automatically generate. If you wish to create your own handle manually, the handle should be identical for each variant (see the Understanding the product fields dropdown above for further information).
Each variant must have matching variant options (Variant_Option_X_Name) such as "color", "size", or "flavor", up to three attributes, otherwise, the product will be ignored when imported.
Each variant must also have a variant value (Variant_Option_X_Value) such as "red", "large", or "strawberry" that relates to each variant option. These do not need to match across each variant, however, they cannot be left blank.
Important
Each product with variants can have up to 3 attributes (variant options), and up to 200 unique combinations in total (variant value).
If the product with variants has more than 200 variants, the product import will be ignored.
The first variant in the group must also include any information about that product group, such as type, tags, brand, supplier, and description. The subsequent variants will not require these fields to be filled.
5. Adding composite products
A composite product is made up of existing products packaged together to create a product bundle, multi-pack, or fractional product.
When creating composite products, use the first line to detail the composite product itself. This is where you need to specify the name, SKU, retail price, tax, description, and other information specific to the new composite product.
The lines below this are where you add the product information for existing products contained within the composite product. These products must already exist in Retail POS or can be created in the same spreadsheet file.
These rows must have the same SKU (and handle if manually entered) as the composite product being created.
Add the existing product's exact name as it appears in Retail POS to the Composite_Name column. Next, add the existing product's exact SKU as it appears in Retail POS to the Composite_SKU column. Then add the quantity of each existing product that will make up the composite product. For fractional composites, refer to the What are fractional composite products? guide.
You do not need to specify pricing and other details in these lines.
Composite products do not need any inventory information as their quantities are set by the component items.
6. Importing the product file
Once you have completed your Retail POS product template spreadsheet file, you can import it into your store.
Retail POS's spreadsheet checker will look for some of the most common import errors in your file before it imports into your store. It will check for any formatting issues or errors such as column headings and mandatory fields and let you know if any of these errors are present in your file.
To validate and import your product spreadsheet you must first navigate to Catalog > Products in the sidebar and then click the Import button. Once here, follow the steps below:
1. Drag and drop your file, or click Choose a file to upload to search for and add the file.
2. As soon as you place your file here, Retail POS will immediately perform a check to make sure your columns match.
- If the validation is successful move straight onto step 6.
- If Retail POS detects any errors you will be taken to the column matching screen below:
3. At this stage, you'll be able to view any unmatched columns and fix them by selecting the correct column from the drop-down menu.
You'll also have the option to change the matching of any other columns or choose not to import a specific column.
4. Once you've matched all the columns correctly, click continue. If there are no further column errors during this step move straight onto step 6.
5. If any mandatory columns are missing from your file an error screen will appear. From here, you'll be able to view the missing column(s) and download a new version of your file to make sure the missing columns are added.
Once you've made the required changes, reimport your file by either dragging and dropping it into the Retail POS window or starting again from step 1.
6. Once successful, you will be taken to a confirmation screen. From here, click Continue with import to import the products into your store.
Warning screen
You may also receive warnings before importing. If your spreadsheet contains duplicate SKUs for example, you may receive a warning message. If you receive a warning message, you will either be asked to Download CSV to fix if the error is preventing you from importing your file, or Continue anyway.
When the option to continue with the import is available it is usually because the missing information can be generated by Retail POS (i.e. SKU). If your only option is to download the file to fix it, you will need to do this and fix any identified errors before trying to upload the file again.
You can download the file to fix it either in a CSV format or in an XLSX format.
Important
To learn how to add inventory to your products, refer to the Adding inventory to your products guide
Important
For information on how to edit your product catalog in bulk using a spreadsheet (CSV, XLSX, or XLS), refer to our Editing products using a spreadsheet file article.