Importing products

If you're migrating from a different system to Lightspeed OnSite, or if you want to add a supplier’s price list to your database, importing is a simple way to batch import or update data to OnSite. You can import data for products, customers, suppliers, and contacts. You can import products from a text file that your supplier provides you, which can save time creating and maintaining products. Each line item in the source data represents one record in your OnSite product database. When you import new products, OnSite creates a new product card for each line item you import. You can also update existing products by matching on a field and importing new data into the fields that you specify.

Before you import products make sure you've configured your store, because certain product attributes need to be defined before you can perform an import.

NOTE: If you're an Omnichannel customer (OnSite with eCom), we strongly recommend that you turn off your eCom integration prior to importing new products into OnSite. This allows you to validate that your products were imported properly, and if necessary, restore your inventory from a backup, before OnSite syncs your new products with eCom. Once you've validated the import process, you can re-enable your eCom integration to sync your inventory.

Main steps for importing data

  1. Back up your data.
  2. Prepare your spreadsheet.
  3. Import your data.
  4. Review the import log.

1. To back up your data

2. To prepare your spreadsheet

  1. Download and unzip the product import template. Open the file in Microsoft Excel or iWork Numbers to see the data presented in a format that you can use as a guideline for arranging your data. The data you import can be one of three types:
  2. Necessary OnSite values Class, Family, Tax Status, Supplier, Currency, Web Categories, POS Categories, User, Color, Size, GL Accounts For example, if you have speakers in your source data and you want to import it into Lightspeed OnSite’s Class target field, the word speakers must already be in Lightspeed OnSite’s list of Classes.
    Optional OnSite values City, State/Province, Country For example, if you have Tucson in your original data, it will be added to your Customer/Supplier record, but will not modify your list of pre-defined values in Lightspeed OnSite.
    Pre-defined value list  Phone Types, Inventoried, Serialized, Editable Sell, among others In some cases, where the target field is a checkbox attribute within Lightspeed OnSite (such as serialized), the original data must be Yes or No, or you can click the detail arrow to select a value - yes or no - to be imported into each record. Yes values enable the attribute and No values do not. The original data can use 1 or 0 rather than Yes or No. Some fields use other pre-defined value lists that are already part of OnSite, such as Phone Type, which has values such as Work or Fax.
  3. Your data needs to match these names, so modify the data you're importing to match these names.
    * Make sure that the values you're importing match these OnSite values.
Field Description Possible values in OnSite
Product Code The Product Code as it appears in Lightspeed OnSite  
Product ID Product ID used for matching purposes only  
Description product description  
Family* product family Family
UPC product UPC Class
Tax Status product tax status Tax status
Supplier* product supplier Supplier
Cost product cost  
Sell selling price  
Sell (tax inclusive) selling price with tax included  
Minimum margin min. margin setting for modified selling price  
Cost always equals sell does cost always equal selling price? Y/N
Creation date date product was created  
Modification date date product was modified  
Color & size color and size  
Height, length, width dimensions of product box, used for web shipping  
Photo product image  
Notes special notes  
Current is product current? Y/N
Inventoried is product inventoried? Y/N
Serialized is product serialized? Y/N
Editable is description editable? Y/N
Editable sell Is the product selling price editable? Y/N
Auto add related not currently supported  
Inventory quantity Total quantity  
Web sell on Web Store? Y/N
Web price special web price  
Web keywords 1-3* Web Store keywords 1-3  
Web long description description on Web Store product page  
Web short description overview description on Web Store  
Web categories primary, secondary & tertiary Web store categories  
Reorder type reorder amount or reorder to max. Use 0 to indicate reorder amount and 1 to indicate reorder to max.  
Reorder point quantity for reorder point  
Reorder amount quantity for reorder amount or reorder to max  
Pricing level 1-10 cost costs for each pricing level  
Pricing level 1-10 pricing level prices  
GL Inventory/Asset Account * GL Inventory/Asset Account  
GL Income Account* product income GL account  
GL COGS/Expense Account * Product COGS/Expense GL Account  
Custom text fields 1-10 custom text fields  
Custom date fields 1-10 custom date fields  
Custom check fields 1-10 custom check fields  
Quantity discounts 1-5 Qty and Corresponding Sell Price of Quantity Discounts  
Customer ID (Imported) Customer ID you can import for use with Contacts (undisplayed)  
Customer ID (Matching Only) Customer ID for matching purposes only (not importable)  
First name customer's first name  
Last name customer's last name  
First Name/Last Name Customer First Name/Last Name in same field  
Company company name  
Creation date date customer was added  
Modification date date customer data was modified  
Type (C = Company/I = Individual) is customer a company or individual?  
Phone 1-4 customer phone numbers  
Phone 1-4 type Phone Number Types (Work, Home, Mobile) 1 of 7 phone types
Email customer email  
Home page customer website  
Address billing line 1 billing address line 1  
Address billing line 2 billing address line 2  
City billing billing address city  
State/province billing billing address state/province  
Country billing billing address country  
Zip/postal code billing billing address zip/postal code  
Address shipping line 1 shipping address line 1  
Address shipping line 2 shipping address line 2  
City shipping shipping address city  
State/province shipping shipping address state/province  
Country shipping shipping address country  
Zip/postal code shipping shipping address zip/postal code  
Photo customer image  
Notes special notes  
Credit hold is customer on credit hold? Y/N
Credit limit $ customer credit limit Y/N
Tax status* customer tax status tax status
Account status* account status account status
Customer category* customer category category
Currency* customer default currency currency
Tax code* customer default tax code tax code
Terms* credit terms term
User* OnSite user assigned to customer user
Language customer default language for printed documents  
Custom Text Fields 1 - 10 custom customer text fields  
Custom Date Fields 1 - 10 custom customer date fields  
Custom Check Fields 1 - 10 custom customer check fields  

3. To import your data

  1. On the Tools menu click Import Tools > Import Products.

  2. Click Open File and find the source file that contains your original data. This file must be a tab-delimited text file or a comma-separated text file, and each line should represent a different record. It is recommended that you include the column headings in your file, as they can be omitted when you do the import but are useful for matching fields. To omit a record, use the Browse slider to find the record and click Omit This Record.

  3. Using the window’s two-column layout, match the target fields in the second column with your source data in the first column by dragging and dropping them into the proper order. If you do not want to import data into a field, clear its checkbox. You can use the Action gear at the bottom to select or clear all.

  4. If there is an arrow in the first column it means that this field has a pre-defined set of values in OnSite. If you click the arrow, you can choose a pre-defined value to add it to the records you import. If you do not click the arrow, the values from your source data are imported to the target field of each imported record. For example, if you are importing billing address cities, you can import a number of cities in one import, or you can click the arrow, and choose one city to be imported into all of the records you’re importing.

  5. In the Options area, you can skip records that match on some fields. For example, choosing Except Matches on Product Code skips records that have a Product Code identical to one already in the system. This means that if your original data matches a product code with the existing products in OnSite, the record will be skipped. It also means that if the product codes in your original data are not unique, only the first line item creates a new product.
  6. Using the Browse slider, slide it from left to right to review your original data to ensure it will be imported the way you expect. If there are any records you want to omit from the import, select the Omit This Record checkbox for the record you want to omit.

  7. You can optionally click Presets to save this configuration for a future import so that you do not have to manually re-match the same field order. You can export and import preset files to have your fields automatically arranged according to an imported preset file. For example, if you have an associate who has already matched fields for the source data that you are also importing, you can import their preset files to save time.

  8. Click Import to import your data. OnSite displays how many records were imported, and any that were not.

4. To review the import log

Every time you import or update data a log file is saved to your desktop that notifies you if any data failed to import, and why. The Import Log includes any lines that were not imported, the Product Code, and the reason. The name of the file saved to your desktop reflects the type of import or update you are doing, such as product_import_log.txt.

Import type Description Reason
Import product Warning: Unable to import quantity for serialized product, skipping inventory quantity field. Serialized products cannot have their quantities or serial numbers imported. They must be counted using Count Inventory.
  Error: Product already exists in database, skipping duplicate product. Error: Product already exists in database, skipping duplicate product.
Update Product Warning: Unable to update quantity for serialized product, skipping inventory quantity field. Serialized products cannot have their quantities or serial numbers imported. They must be counted using Count Inventory.
  Error: Product does not exist, unable to update product. Importing was not able to match an existing product based on the field you’ve chosen to match on.

Importing product photos

Although you can add one product photo to each product that you import, in OnSite you can have multiple product photos. Use this procedure if you have several photos for a product. You can update up to five photos for each product.

To update multiple product photos

  1. Put all of your product photo files into one folder.
  2. In your import spreadsheet, create a column for the photo files you want to import. You can use up to five columns.
  3. For each product, list the photo file name, such as photo1.jpg.
  4. Do a manual backup of your database at Tools> Utilities> Backup Database.
  5. In OnSite StoreMaster on the Tools menu click Import Tools> Import Products.
  6. Under Options change Import Products to Update Products.
  7. Click Open File and select your spreadsheet.
  8. Click the Action gear and choose Deselect All.
  9. In the Field column, drag the titles so that they are next to the corresponding fields in the Preview column.
  10. In the Import column, check the fields you want to update.
  11. Review the parameters you set and click Import.
  12. When you are prompted to select the folder your photos are located in, browse to the folder.
  13. When the update is completed, review your products to make sure the data is updated properly.

Importing size-color matrix products

OnSite can import size-color combination products that use a matrix master-child relationship. This relationship of a master product, representing a style common to several combination products and its child products helps simplify and speed up your purchasing and inventory management.

Important: You must have sizes and colors set up in OnSite before you import the data.

To import master and child products, structure your data in this format

Product code

master: main code

child: master product code-color-size

Description

master: main description

child: master description, color,size

Master?

1 or Y = yes

0 or N = no

Master code

master: leave blank

child: master product code

Color

master: leave blank

child: color

Size

master: leave blank

child: size

Example of data for matrix import

 

 

Have more questions? Submit a request || || Request a callback