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
- Supply prices of your products
- Existing barcode/SKU numbers
- Exact names of the taxes you set up in Retail POS
Using the Retail POS product template
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.
Open the Retail POS product template spreadsheet file in Excel or use free spreadsheet software like Open Office to retain the formatting required.
Understanding the product fields
Once you download the Retail POS template, you will need to add your product information. 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. When integrating with an e-Commerce platform, the description will form the customer-facing 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. Each product name must be unique from other products. If your product is a variant, the name will be used to group products with variants in CSV imports. All variant products within the same family MUST have the same name. Refer to the Adding products with variants dropdown for more information. |
| 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. |
| Variant_Option_One_Value |
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". Each variant product can have up to 200 values total. 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) |
|
Active_online (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. To change the tax rate, an alternative tax rate will need to be created before applying it to a product. These can be added/managed by navigating to Setup > Sales taxes. Enter the tax rate name exactly as it appears in Retail POS. 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". To change the tax rate, an alternative tax rate will need to be created before applying it to a product. These can be added/managed by navigating to Setup > Sales taxes. Enter the tax rate name exactly as it appears in Retail POS. 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. It is also one of the metrics that can be used to set promotions. |
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. If you have more than one supplier for the product, leave this blank. |
| 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 About Loyalty for Lightspeed Retail (X-Series) for more 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.
- 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
- Composite product: Package different products together and create a product bundle, multi-pack, or fractional products
Adding standard products
A standard product is a single product with a single SKU and its own inventory.
- Product name (name): 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".
- SKU (sku): You can choose to create your own SKU for this product or have Retail POS automatically generate a SKU by leaving the field blank.
- Handle (handle): 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
- Supply price (supply_price): Adding a supply price for the product to ensure your cost of goods sold reporting is calculated correctly moving forward.
- Retail price (retail_price): 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.
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.
- Product name (name): 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.
- Handle (handle): 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.
- Variant options (variant_option_#_name): 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.
- Variant values (variant_option_#_value): 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.
- Other fields: 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.
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.
Adding composite products
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.
- SKU (sku): Add the existing product's exact SKU as it appears in Retail POS to the composite_sku column. These rows must have the same SKU (and handle if manually entered) as the composite product being created.
- Composite product name (composite_name): Add the existing product's exact name as it appears in Retail POS to the composite_name column.
- Composite quantity (composite_quantity): Add the quantity of each existing product that will make up the composite product.
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.
Importing the product template spreadsheet file
Once you have completed your Retail POS product template spreadsheet file, you can import it into your store.
- To validate and import your product spreadsheet you must first navigate to Catalog Products in the sidebar and then click the Import button.
- Drag and drop your file, or click Choose a file to upload to search for and add the file.
- As soon as you place your file here, 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.
- If the validation is successful, go to step 4.
- If Retail POS detects any errors you will be taken to the column matching screen below:
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.
Once you've matched all the columns correctly, click continue. If there are no further column errors during this step move straight onto step 4.
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.
- Once successful, you will be taken to a confirmation screen. From here, click Continue with import to import the products into your store.
Fixing common importing errors
- Warnings before importing: If you receive a warning message, you will either be asked to Download spreadsheet 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.
- Please upload a valid spreadsheet file: If you see the message, “Sorry, we couldn't read that file. Please upload a valid .CSV, .XLS or .XLSX file”, the file type you have tried to import may either be incorrect or corrupt. Double-check that this file is a .csv, .xlsx or .xls file and reimport it.
- Products ignored on import: Make sure the products have matching tax_names as those set up in your store. You can find your tax names in Setup > Sales taxes. If your Store sales tax is "GST", but "Tax" has been entered in the column in the spreadsheet, this product will be ignored.
- Duplicate SKUs: Except for composite products, each SKU must be different. Before importing into Retail POS, open the file in Notepad (Windows) or Text Edit (Mac) and check that the SKU column has not truncated the number. If Excel is truncating long SKU numbers, export product lists as XLXS files or [format CSV files in Open Office].
- Errant commas and unusual characters: Double commas and commas in unusual places (e.g. before an entry) may cause the product file to be rejected. Other non-alphanumeric characters like apostrophes, parentheses or square brackets, quotation marks, and/or ampersands in the SKU or the handle field may cause the file to be rejected.
- Missing mandatory name field: The name column is a mandatory field. If you do not fill out this field, the file will not import.
- Handle is not in the correct format: The handle field cannot have spaces or any special characters, aside from a hyphen, which is autogenerated at the time of creation. Unless it is a variant product, your product handles must also be unique.
- Outlet names not matching those set up in Retail POS: The product file import will create fields in Retail POS for example brands, suppliers, tags, tax rates, etc.; however, it will not create outlets. Therefore, check that the outlet spelling in the product import matches the outlet spelling in Retail POS.
What's next?
Adding products in Retail POS (X-Series)
Learn moreEditing products in Retail POS (X-Series)
Learn moreAttachments
- Vend product import template.csv813 Bytes