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 have configured your store, because certain product attributes need to be defined before you can perform an import.
Main steps for importing data
- Back up your data.
- Prepare your spreadsheet.
- Import your data.
- Review the import log.
1. To back up your data
- In OnSite StoreMaster, manually back up your data.
2. To prepare your spreadsheet
- 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:
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.
- 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|
|Tax Status||product tax status||Tax status|
|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|
|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|
|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|
|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|
|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|
|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
- On the Tools menu click Import Tools > Import Products.
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.
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.
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.
- 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.
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.
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.
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 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
- Put all of your product photo files into one folder.
- In your import spreadsheet, create a column for the photo files you want to import. You can use up to five columns.
- For each product, list the photo file name, such as photo1.jpg.
- Do a manual backup of your database at Tools> Utilities> Backup Database.
- In OnSite StoreMaster on the Tools menu click Import Tools> Import Products.
- Under Options change Import Products to Update Products.
- Click Open File and select your spreadsheet.
- Click the Action gear and choose Deselect All.
- In the Field column, drag the titles so that they are next to the corresponding fields in the Preview column.
- In the Import column, check the fields you want to update.
- Review the parameters you set and click Import.
- When you are prompted to select the folder your photos are located in, browse to the folder.
- 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
master: main code
child: master product code-color-size
master: main description
child: master description, color,size
1 or Y = yes
0 or N = no
master: leave blank
child: master product code
master: leave blank
master: leave blank
Example of data for matrix import