Last week in Part One, we took a look at how to quickly get your products into ShopSite® using just the backoffice admin interface. If you have a large number of products, or you manage your product information with a database or spreadsheet application like Excel, using a web based interface may be too slow or cumbersome.

This week we’ll look at using a database upload in ShopSite for easier product management.

Which fields are required?

Before we discuss the technical aspects of the various databases / files you can upload into ShopSite, you should understand what is required in any type of upload.

• Uploads are modifications or additive only
  Each product uploaded can either modify an existing product (i.e. update price, image, weight, etc…) or add a new product to ShopSite. You cannot delete products via a database upload. During the upload process, you will be able to tell ShopSite to either Add or Ignore products from your upload file that do not already exist in ShopSite.

• The product name is always required
  Your upload file must contain the product name as the first column. That is the only required field. In the header row, the first column should be named “#Name” so that it matches what ShopSite expects.

• You only have to upload those fields you want to add / modify
  Your upload file does not have to contain every field available. If you are simply updating the price of some products, you only need to include two fields: Name and Price.

• The first row is always the header
  The first row is treated as the header row, meaning each column name should match a field name in ShopSite. If the header names do not match the field names exactly in ShopSite, you will be prompted to manually match these fields or tell ShopSite to ignore some or all of those un-matched columns.

List of field names and options

The ShopSite Help Docs list all the various upload fields, what their default values are, syntax, and a good description of what each field may contain. If it seems a little overwhelming, the easiest thing to do is download your products database from ShopSite, and open this file in Excel to see how existing products look when downloaded.

One tricky field that we see many clients get confused on is “Product On Pages“. This field operates differently from the field “Add To Pages“. Product On Pages is a complete list of all ShopSite pages that a product should appear on. If the page is not listed here, the product will be removed from any other pages. Add To Pages is an additive field, meaning any pages listed here will be added to whatever pages the product is already listed on.

It is important to realize that you should only use one of the above fields when dealing with assigning products to pages. Do not combine these fields in an upload file.

Uploading a tab delimited text file

Uploading a tab delimited (meaning each column is separated by a “tab” and each product is on one line) text file is the simplest upload method, and is supported in all versions of ShopSite. You can export to this format from all major software applications like Excel or MS Access.

If you are exporting from Excel, there are a few gotchas to watch out for. We have a detailed knowledgebase article that explains an easy way to get your data out of Excel into the proper format.

If you prefer to use the export feature of Excel, you have to make sure your last column/field is populated with data in every field. This ShopSite KB article explains this in further detail.

Uploading an Excel spreadsheet directly

Starting with version 10 SP1 of ShopSite, it is now possible to upload an Excel spreadsheet directly into ShopSite. No need to convert the data to a tab delimited format first. A few things to note:

• .xls (older Excel format) is the only format supported
  Only the older Excel format (not .xlsx) is supported. When saving your spreadsheet, make sure to save it in the older “Excel 97-2003″ format.

• For numeric fields, the proper number of decimal places must be set
  This would apply for fields like price and sales price. The column must be formatted as a numeric field with two decimal places. If your weight field has values with decimals, this also must be defined for the proper number of decimal places.

Setting a Unique Identifier

Upload options available in ShopSite (click image to enlarge)

By default, ShopSite will assume your product name is your “unique identifier“. A unique identifier is how ShopSite determines if the product already exists in ShopSite (i.e. you’re modifying an existing record) or if ShopSite should consider the product to be a new item. You can choose either “Product Name“, “SKU“, or “None” as the unique identifier. This field should be unique (no duplicates) for all products.

If you choose “None” then ShopSite will treat each row as a new product regardless if it exists or not.

…

Once you are comfortable with the nuances of performing a database upload, it can make product management a breeze in ShopSite. Being able to manipulate large amounts of data in Excel or MS Access and then quickly import these changes into ShopSite greatly simplifies the management aspect for your products.

Next week (part three of this series) we’ll take a look at XML file uploads and automating this process.