Hi. How can we help?

Search

Formatting item import files

To import inventory items via our Retail Imports team, please refer to this article.

 

To add items or item images to your Retail POS account, the column headers and inventory data in your import file need to be compatible with Lightspeed Retail POS. The same is true if you're updating items or item images. Therefore, to make sure your import file is compatible with Lightspeed Retail POS and that you enter your inventory data in the intended columns, you need to format it accordingly. Doing so involves these main steps:

 

  1. Creating your item import file
  2. Opening your item import file
  3. Formatting your item import file

 

Note: If you're importing your inventory for the first time, we recommend reading the following Help articles as a starting point instead:

 

Before you begin

Before you format your import file, please note the following:

 

  • Only 1 import can run at a time per account.
  • Once an import is done, it cannot be undone.
  • Your import file must be in XLSX or CSV format (we recommend XLSX).
  • Your import file can contain up to 10,000 items.
  • You can re-order the columns as you wish or even remove columns you won't be using. You cannot, however, create new columns with new headings.
  • Certain columns use a default value if left blank (see the Data format column).
  • You can only import the quantity on hand (QOH) of new items. To update the QOH of existing items, we recommend doing an inventory count.
  • You can't import custom fields.
  • You can import up to 10 levels of categories and sub-categories. For example, if you import all 10 levels, the column headers would be Category, Subcategory 1, Subcategory 2, Subcategory 3, Subcategory 4, Subcategory 5, Subcategory 6, Subcategory 7, Subcategory 8 and Subcategory 9.
  • To import matrix variants, you need to format them differently in your item import file than other item types. For more information, please see Formatting matrix variants in item import files.
  • To import images, your Retail POS account uses the inventory data in your import file to identify the filenames of the images you plan to import and which items they should be uploaded to. For more information, please see Formatting matrix variants in item import files.
  • If you order an item from multiple vendors, that item can have multiple vendors, vendor IDs, and vendor costs in your inventory. You can only import one vendor, vendor ID, and vendor cost per import, however. If you add the same item to a new import with a new value for these three fields, the new values will be added to the item; it will not overwrite the previous vendor, vendor ID, and vendor cost.

 

1. Creating your item import file

As a starting point, we recommend creating your item import file in one of the following ways instead of starting from scratch:

 

  • Export your inventory data from Lightspeed Retail POS
  • Download a template CSV file from Lightspeed Retail POS

 

Export your inventory data from Lightspeed Retail POS

Exporting your inventory data from Lightspeed Retail POS is an especially helpful starting point if you're updating existing items. To do so:

 

  1. From the main menu, click Inventory > Item Search. 
  2. Optionally, use the available filters to refine your list of items.
  3. At the top-right of your search results, click Export.

Screen_Shot_2019-10-22_at_3.20_1.png

A CSV file that contains the inventory data of your items should download to your computer automatically.

 

Download a template CSV file from Lightspeed Retail POS

Downloading a template CSV file from Lightspeed Retail POS is a great time-saver as it includes the column headers that are compatible with Lightspeed Retail POS. It also includes examples to help you understand how you should format your import file. To download a template CSV file:

 

  1. From the main menu, click Inventory > Import Items.
  2. Click + New Import and Download Template CSV at the bottom-left of the page.

Screen_Shot_2019-10-29_at_11.27.08_AM.png

2. Open your item import file

To open and format your file, we recommend using Google Sheets. It's free and it causes fewer data-breaking issues compared to other spreadsheet software. The only thing you need to do to avoid data-breaking issues with Google Sheets is to save your import file as an XLSX. We recommend doing so right after opening your file so you don't forget.

If you plan to open and edit your file in Excel, please see Opening and saving import files with Excel as you need to open and save your import file in a specific way to avoid breaking certain columns of your inventory data. Once you're done formatting your import file, save your import file as an XLSX.

 

3. Format your item import file

To simplify your import file and make it easier to format, delete the non-essential columns within it so that only the essential columns you need remain. If you're using an export file from your previous POS or are creating your import file from scratch as a starting point, also rename the column headers to match the column headers in the tables below. This will make them compatible with Lightspeed Retail POS. 

Once you've simplified your import file (and renamed the columns headers, if required), consult the tables below to format your inventory data. How you format your import file is dependent on if you're:

 

 

Note: If you upload your import file into your Retail POS account, your Retail POS account is designed to help you map your column headers when it reviews your import file. If there are any formatting errors in your import file, it's also designed to tell you how many errors your file contains and how to fix each of them. More on that in Importing items. 

 

Format to create new items

The table below lists and defines the column headers that are compatible with Lightspeed Retail POS. It also explains how to format the inventory data you enter within each column if you want to create new items with your import file.

As for the rows in blue in the table, they only apply if you're using Retail with eCom (Omnichannel). Some cells are also in blue to provide additional Omnichannel information.

If you have a multi-store account, some rows in the table below, such as [Shop name] - Quantity on Hand and [Shop name] - Unit Cost, have additional information as well.

Note: Column headers must be in English to be compatible with Lightspeed Retail POS.

 

Column Definition Data format Imported to
Description

The name of your item. Make it unique so you can tell it apart from other item descriptions.

If you're importing a matrix item, leave the Description field blank and enter a name in the Matrix Description field instead.
  • 0 to 255 characters
  • No returns or line breaks
  • UTF-8 characters allowed
  • Cannot be blank (unless importing a matrix item)
 Inventory > [item description] > Details submenu > Description field
System ID A unique identifier generated by Lightspeed Retail. It cannot be created or updated through an item import. The system ID is also used for the barcode on labels generated by Lightspeed Retail.
  • 12 digits
  • If blank, field is skipped
  • Only used as a unique identifier when updating items to make sure the data in your import file updates the correct item in your inventory.
 Inventory > [item description] > Details submenu > System ID
UPC The "Universal Product Code" of the item. It contains the manufacturer identification number and the item number.
  • 11 to 12 digits
  • If blank, field is skipped
  • No returns, line breaks or foreign characters
 Inventory > [item description] > Details submenu > UPC field
EAN The "European Article Number" of the item. It's also known as the "International Article Number".
  • 13 digits
  • If blank, field is skipped
  • No return, line breaks or foreign characters
 Inventory > [item description] > Details submenu > EAN field
Custom SKU

The series of numbers and/or letters sometimes required for internal shop use.

For example, you can use this field for old POS barcodes or item numbers.
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed
 Inventory > [item description] > Details submenu > Custom SKU field
Manufacturer SKU The manufacturer's series of numbers and/or letters assigned to the item by the manufacturer.
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed
 Inventory > [item description] > Details submenu > Manufact. SKU field
Vendor The name of the vendor who sold you the item.
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed
 Inventory > [item description] > Details submenu > Vendor drop-down
Vendor ID The vendor's part number for the item.
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed
 Inventory > [item description] > Details submenu > ID
Vendor Cost The cost at which you purchase the item from a specific vendor.
  • Currency format (e.g. 1.99)
  • If blank, defaults to 0
  • You don't need to specify a currency symbol
  • This field requires Vendor and Vendor ID to be imported along with it
 Inventory > [item description] > Vendor ID submenu > Vendor Cost field
Brand The item's brand (e.g. Shimano).
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed
  • When adding a brand, your Retail POS accountdoes a case insensitive search of the name to avoid duplicates. If "Lightspeed" already exists, "LIGHTSPEED" or "lightspeed" can't be added.
  • Leading and trailing spaces in a brand name are removed.

Note: If you have an eCom store, please see Managing product brands in eCom to understand how brands in Retail are pushed to eCom.

 Inventory > [item description] > Details submenu > Brand drop-down
Default Cost The cost at which you purchase the item from your default vendor.
  • Currency format (e.g. 1.99)
  • If blank, defaults to 0
  • You don't need to specify a currency symbol. Prices default to your account's currency.
 Inventory > [item description] > Details submenu > Default Cost field
Default - Price The price associated with the item's Default pricing level and charged to the customer in your Retail shop.
  • Currency format (e.g. 1.99)
  • If blank, defaults to 0
  • You don't need to specify a currency symbol. Prices default to your account's currency.
  • If the Online pricing level is 0, the Default pricing level is used for the item in your eCom store.
 Inventory > [item description] > Details submenu > Default price field
MSRP - Price The price associated with the item's MSRP (manufacturer suggested retail price) pricing level. This price is not charged to the customer unless you set it to. You can also specify whether or not to display the MSRP price on labels.
  • Currency format (e.g. 1.99)
  • If blank, defaults to 0
  • You don't need to specify a currency symbol. Prices default to your account's currency.
 Inventory > [item description] > Details submenu > MSRP price field
Online - Price

The price associated with the item's Online pricing level and charged to the customer in your eCom store (if you don't use the Default pricing level).
  • Currency format (e.g. 1.99)
  • If blank, defaults to 0 and Default pricing level is used for the item in your eCom store.
  • You don't need to specify a currency symbol. Prices default to your account's currency.
 Inventory > [item description] > Details submenu > Online price field
[Name of custom pricing level] - Price

The price associated with the item's custom pricing level. This price is not charged to the customer in your Retail shop unless you set it to.
  • Currency format (e.g. 1.99)
  • If blank, defaults to 0
  • You don't need to specify a currency symbol. Prices default to your account's currency.
 Inventory > [item description] > Details submenu > [Name of custom pricing level] price field
Matrix Description

The name of the matrix that will be included in the name of all the matrix variants it contains.

To elaborate, the Item Import tool creates matrix variant names based on the Matrix Description + Attribute 1, Attribute 2, and Attribute 3 fields.

E.g. T-Shirt (matrix description) + Blue (attribute 1) + Medium  (attribute 2) +  Cotton (attribute 3)
  • 0 to 255 characters
  • No returns or line breaks
  • UTF-8 characters allowed

Note: If you're using Retail with eCom, you can only publish 100 variants per matrix to eCom. For more information, please see the article Formatting matrix variants in item import files.

 Inventory > Matrix > [matrix description] > Setup submenu > Description field
Matrix Attribute Set

The group of attributes the variants in the matrix share. Retail POS has four built-in matrix attribute sets:

  • Color
  • Size
  • Color/Size
  • 3 Attributes

E.g. If the items in your matrix (matrix description) are separated only by size, enter size in this column.

You can also create custom matrix attribute sets under Inventory > Matrix Attribute Sets.
  • Color
  • Size
  • Color/Size
  • 3 Attributes
  • Name of custom attribute set
  • If blank, field is skipped

For more information, please see the article Formatting matrix variants in item import files.

 Inventory > Matrix > [matrix description] > Setup submenu > Attributes drop-down
Attribute 1 The name of the attribute (e.g. Blue).
  • 0 to 255 characters
  • No returns or line breaks
  • UTF-8 characters allowed
  • If blank, field is skipped

For more information, please see Formatting matrix variants in item import files.

 Inventory > Matrix > [matrix name] > Setup > veld Attribute 1 field, Color field or Size field
Attribute 2 The name of the attribute (e.g. Medium).
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed

For more information, please see Formatting matrix variants in item import files.

 Inventory > Matrix > [matrix name] > Setup > Attribute 2 field or Size field
Attribute 3 The name of the attribute (e.g. Cotton).
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed

Please see the complimentary article Formatting matrix variants in item import files for more information.

 Inventory > Matrix > [matrix name] > Setup > Attribute 3 field
Discountable Specifies whether or not discounts can be applied to the item.
  • Yes
  • No
  • If blank, defaults to yes
 Inventory > [item description] > Details submenu > Discounts Allowed checkbox
Taxable Specifies whether or not the item is taxable.
  • Yes
  • No
  • If blank, defaults to yes
 Inventory > [item description] > Details submenu > Taxable checkbox
Tax Class

Specifies the tax class to apply to the item. 

Retail POS has three built-in tax classes:

  • Item
  • Labor (if optional module Service is enabled)
  • Gift Card (if optional module Gift Cards is enabled)

You can also create custom tax classes (e.g. ) under Settings > Tax Classes.
  • Item
  • Labor
  • Name of custom tax class
  • If blank, defaults to the Item tax class.
 Inventory > [item description] > Details submenu > Tax Class drop-down
Item Type

The type of item you're importing:

  • Single
  • Non-inventory
  • Assembly
  • Box
  • Single
  • Non-inventory
  • Assembly
  • Box
  • If blank, defaults to single.
 Inventory > [item description] > Details submenu > Type drop-down
Publish to eCom Specifies whether or not the item should be published to your eCom store (with online visibility hidden).
  • Yes
  • No
  • If blank and creating an item, defaults to the Automatically publish to eCom setting under Settings > General Options > General.
  • If blank and updating an item, field is skipped.
  • Publish to eCom item limit of 15,000 and variant limit of 100 per matrix also apply.
 Inventory > [item description] > Details submenu > Publish to eCom field
Serialized Specifies whether or not to track serial number information for the item.
  • Yes
  • No
  • If blank, defaults to no
 Inventory > [item description] > Details submenu > Serialized checkbox
Category The category you want the item to be organized into (e.g. Apparel). It's helpful to organize your items into categories as you can use them as filters in many areas of your account (e.g. inventory searches and reports).
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed
 Inventory > [item description] > Details submenu > Category field
Subcategory 1 to 9 The subcategories you want the item to be organized into (e.g. Apparel > Women > Tops). It's helpful to organize your items into subcategories as you can use them as filters in many areas of your account (e.g. inventory searches and reports).
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed
 Inventory > [item description] > Details submenu > Category field

Clear Existing Tags

 Specifies whether or not to delete the item's existing tags.
  • Yes
  • No
  • If blank, defaults to no
 Inventory > [item description] > Details submenu > Tags field

Add Tags

 Specifies the new tags to add to the item's existing tags.
  • 0 to 255 characters
  • If blank, field is skipped
  • No returns or line breaks
  • UTF-8 characters allowed
  • If adding multiple tags, separate them with a comma
 Inventory > [item description] > Details submenu > Tags field

Note

 The note that will appear on sales and receipts for the item. For the note to display, the item's Display Note column needs to be set to yes.
  • 0 to 255 characters
  • Cannot be blank if the item's Display Note column is set to yes.
 Inventory > [item description] > Details submenu > Notes field

Display Note

 Determines whether or not to display the note specified under the Note column on sales and receipts for the item.
  • Yes
  • No
  • If blank, defaults to no.
 Inventory > [item description] > Details submenu > Display Note On Sales and Receipts checkbox

Archive

 Specifies whether or not the item is archived.
  • Yes
  • No
  • If blank, defaults to no.
 Inventory > [item description] > Details submenu > Archive or Un-Archive button

Featured Image

 Specifies the filename of the item's featured image.
  • 0 to 255 characters
  • If blank, field is skipped.

Note: Entering a new filename will push the item's current featured image further down the list of images. For best practices regarding filenames of item images, please see Formatting images in item import files.

 Inventory > [item description] > Details submenu > Image section

Image

 The filenames of the item's images. You can enter up to 11 filenames, one in each of the 11 Image columns.
  • 0 to 250 characters
  • If blank, field is skipped

Note: For best practices regarding filenames of item images, please see Formatting images in item import files.

 Inventory > [item description] > Details submenu > Image section

Shop Quantity on Hand

 Specifies the shop's current stock level of the item. You can only enter a shop's QOH when creating an item, not when updating an existing item.
  • Positive whole number (e.g. 10)
  • If blank, defaults to 0
  • If multi-store account, template file will have a column for each shop (e.g. [Shop name] - Quantity on Hand).
 Inventory > [item description] > Inventory submenu > Inventory Details section > Remaining field

Shop Unit Cost

 Specifies the unit cost at which your shop purchased the item's quantity on hand.
  • Currency format (e.g. 1.99)
  • You don't need to specify a currency symbol. Prices default to your account's currency.
  • If blank, the item's Default Cost is used instead.
  • If enter 0, the item's Unit Cost will be 0.
  • Can only enter a unit cost if your shop is importing a QOH for a new item, not an existing item.
  • If multi-store account, template file will have a column for each shop (e.g. [Shop name] - Unit Cost).
 Inventory > [item description] > Inventory submenu > Inventory Details section > Unit Cost field

Shop Reorder Point

When you view your item or the Reorder List report, the item will show as needing to be reordered if your stock is at or below the quantity specified as the reorder point.
  • Positive whole number (e.g. 10)
  • If blank, defaults to 0.
  • If multi-store account, template file will have a column for each shop (e.g. [Shop name] - Reorder Point).
 Inventory > [item description] > Details submenu > Automatic Re-Ordering section > Point field

Shop Reorder Level

Specifies the stock level your store wants to replenish up to when you reorder stock.

E.g. If you have 3 items in stock, the reorder point is 5, and the reorder level is 10, the Reorder List report will show that you need to order 7 to get your stock level back to 10.
  • Positive whole number (e.g. 10)
  • If blank, defaults to 0.
  • If multi-store account, template file will have a column for each shop (e.g. [Shope name] - Reorder Level).
 Inventory > [item description] > Details submenu > Automatic Re-Ordering section > Level field

 

Format to update existing items

If you're updating existing items, you can either replace or delete the details of items in your inventory.

To replace the details of your existing items, enter the new details in the columns of your import file. To delete the details of your existing items, enter "NULL" in the columns of your import file. If you leave any columns empty, your Retail POS account will simply ignore and skip it.

Whether you're replacing or deleting the details of your existing items, you need to include one of the below columns as a unique item identifier. This unique item identifier will make sure that the details in your import file will be imported to the correct item in your inventory.

 

  • System ID (recommended)
  • UPC
  • EAN
  • Custom SKU
  • Manufacturer SKU

 

Note: You can only enter "NULL" in the columns below. The rows in blue only apply to merchants who use Retail with eCom (Omnichannel).

 

Column "NULL" value handling
UPC Entering "NULL" will delete details in this field.
EAN Entering "NULL" will delete details in this field.
Custom SKU Entering "NULL" will delete details in this field.
Manufacturer SKU Entering "NULL" will delete details in this field.
Vendor Entering "NULL" will clear the relation between the item and the vendor.
Vendor ID Entering "NULL" will delete details in this field.
Vendor Cost Entering "NULL" will set the field to 0.00.
Brand Entering "NULL" will clear the relation between the item and the brand.
Category Entering "NULL" will clear the relation between the item and the category.
Shop Reorder Point  Entering "NULL" will set the field to 0.
Shop Reorder Level Entering "NULL" will set the field to 0.
Price Entering "NULL" will set the field to 0.00.
MSRP Entering "NULL" will set the field to 0.00.
Default Cost Entering "NULL" will set the field to 0.00.
Online Price Entering "NULL" will set the field to 0.00.
Discountable Entering "NULL" will disable this setting.
Taxable Entering "NULL" will disable this setting.
Publish to eCom

Entering "NULL" defaults to the Automatically publish to eCom setting under Settings > General Options > General.
Item Type Entering "NULL" will update this setting to Single.
Serialized Entering "NULL" will disable this setting.
Note  Entering "NULL" will delete details in this field.
Display Note  Entering "NULL" will disable this setting.
Archive Entering "NULL" will un-archive the item.

 

What's next?

If you've formatted your item import file (matrix variants, images and all), you're ready to import your items.

Was this article helpful?

2 out of 2 found this helpful