Importing Products

Many Internet stores feature product catalogs that include hundreds and sometimes thousands of products. For this reason, ProductCart includes a powerful Product Import Wizard, which allows you to quickly populate your product catalog (database) by importing information from an existing product database or data file. The Product Import Wizard:

• Can import entirely new products or update existing products
• Can “undo” an import procedure to allow you to quickly “try again” when the results are not what you expected
• Can export product information in a format that makes it easy to edit the data and re-import it

Properly preparing your import file prior to importing it into ProductCart can save you a lot of time (and headaches). The vast majority of technical support requests that we receive from customers that are using the Import Wizard are due to improper formatting of the file to be imported. For example, users often forget to define the import range in their Microsoft Excel file (please see below for details on how to do so).

ProductCart supports importing from the following file types:

• Microsoft Excel (*.xls)
• Comma Separated Value (*.csv)

Importing a product database into ProductCart requires the following steps:

1. Properly formatting your file. The steps to be taken are different depending on whether you are planning to use a *.xls or *.csv file.
2. Uploading the file to your server. You can either use the built-in upload feature or FTP the file to the pc/catalog folder. We recommend that you FTP the file to the pc/catalog folder if you are importing a large file. In addition, please note that some Windows sever do not allow you to upload a file large that 200K via a script (this is a default setting on newer servers). If your file is larger than 200K and you are experiencing a problem, try transferring the file via FTP.
3. Specifying whether you are importing new products or updating existing products.
4. Mapping fields from the file to ProductCart’s database.
5. Confirming mapped fields.
6. Performing the import. Note that this task can take several minutes when you are dealing with thousands of products.
7. Reviewing which records could not be imported and why.

ProductCart’s Import Wizard will guide you through the process step by step, as explained later in this section. First, however, you will have to spend a little time formatting the file so that it can be seamlessly imported into ProductCart.

Carefully review the requirements for each field so that you will not run into conflicts when mapping the fields. In addition, we strongly recommend that you remove columns that do not contain data, so that the file that you upload to ProductCart is smaller in size and mapping is less confusing. And you may also want to rename the column headings so that they can easily be recognized when mapping the fields.

Regardless of which file type you plan to use during the import, it is very important that you make sure your file is formatted properly prior to importing into ProductCart.

If you are importing an Excel file, the following steps MUST be taken in order for the import to be successful.

By default, ProductCart limits the import to 3,000 records when using an Excel spreadsheet. This is done to avoid performance issues when importing MS Excel files. You can either setup your MS Excel file to have 3,000 rows or less (therefore splitting larger files into smaller spreadsheets), or you can remove the limitation in ProductCart.

If you decide to try to import more than 3,000 records at the same time, do the following to edit the source code:

• Download the file step4-xls.asp from your Control Panel folder
• Open it in Notepad or your favorite HTML editor
• Locate the line that reads

  <% iPageSize=3000

• Replace 3000 with a higher number
• Save the file, and upload it back to the Control Panel folder.

If you are running into performance issues (e.g. the script times out), reduce the amount of records that you are trying to import.

1. Remove all other worksheets
2. Remove all cell formatting
3. Remove all empty columns
4. Remove all columns that you do not intend to import
5. Remove all empty rows

1. Open your .XLS file in Excel.
2. Select the SKU column by left clicking on the Column header.
3. Select the menu item: Data > Text to Columns. The Convert Text to Columns Wizard will be displayed.
4. In Step 1 of the wizard, click on the Next button.
5. In Step 2 of the wizard, click on the Next button.
6. In Step 3 of the wizard, select TEXT in Column data format, then click on the Finish button.

The Excel import feature requires a named range and the range must be named “IMPORT. To do this, select (highlight) the entire data range (columns and rows) that you want to import (including the top row, which should contain the field names), and then…

1. Office 1997 to 2003:
   1. Select Insert > Name > Define
   2. Enter the name “IMPORT” without the quotes and click on the Add button.
2. Office 2007:
   1. Select Formulas > Define Name
   2. Enter the name “IMPORT” without the quotes and click on the OK button.
   3. You can review existing “Names” used on the spreadsheet by selecting Formulas > Name Manager
   4. Save the spreadsheet as an .XLS file

Here is an example of what the IMPORT range should look like (it must contain all cells that are part of the IMPORT, including the header or top row – such as the shaded area in the example below).

You can either import new product data or update/append data to an existing product database. For example, if you need to update the list price of 300 existing products in your store, you could import a file that only contains the SKU and the updated price. If you wish to update/append data, select that option during the import procedure: the Import Wizard will prompt you to make a selection after you have uploaded your data file to the system.

ProductCart will use the SKU (part number) field as the product identifier and update database records whose SKU is an exact match with the data that you are importing (note: the SKU field is not case sensitive). If the SKU is not an exact match, a new product (database record) will be added (in which case it must also contain the 3 additional required fields mentioned below). If there are multiple products in your catalog with the same SKU (which is a scenario that is not recommended), ProductCart will update the first one it finds in the products table.

Since the import file can only specify a category and its parent, you cannot create a category tree that is more than 2 levels deep through the Import Wizard. Instead, you should use the Category Import Wizard. You can then assign products to categories using the Product Import Wizard. In other words, the recommend strategy is:

1. Create the Category Tree by manually adding categories in the Control Panel or by using the Category Import Wizard
2. Use those category names in your Product Import file so that products are properly assigned to the correct categories
3. Import product information using the Product Import Wizard

Although ProductCart includes an “Undo Import” feature that allows you to go back to the way the product database was before the import, we still recommend that you BACK UP your existing store database before performing a substantial import or update procedure, which would be difficult to undo manually.

If your store uses a MS Access database, you can simply download the *.mdb data file to your computer. If your store uses a SQL database, inquire with your Web hosting company about how you can have your database backed up and restored (this is probably done automatically on a regular basis, but inquire about on-demand back-ups).

The following table contains detailed specifications for each field that can be imported. Notes:

• The “SKU”, “name”, “description”, and “online price” fields are required during a fresh data import, but are not required when appending data to the database. Only the “SKU” is required when updating existing product information, plus any field(s) that you wish to update.
• If you are importing an Excel spreadsheet, you must review the formatting requirements mentioned above before continuing
• If you are importing a CSV file, do not use double or single quotes at the beginning of any field (e.g. this is not valid: “The King of Torts”, John Grisham; the valid formatting would be: John Grisham, “The King of Torts”).

The following fields apply only if the Product Type is “DP” (Downloadable Products)

The following fields apply only if you are importing a Gift Certificate

The following fields apply only if the Product Type is “BTO” (Build To Order Products).

The Import Wizard allows you to specify which type of product you are importing: a Standard Product, a Downloadable Product, a Build To Order Product, or a Build To Order Item (for more information about the difference between a BTO Product and BTO Item, please refer to Chapter 14). To specify the type of product that you are importing, use the following values in your import file, without the quotes:

• Leave the field blank if you want the product imported as a Standard product. Therefore, if the field is blank or the field is not mapped during the import, products are assumed to be Standard products.
• Add the value “BTO” if you want the product imported as Build To Order product
• Add the value “ITEM” if you want the product imported as Build To Order Only Item
• Add the value “DP” if you want the product imported as a Downloadable Product

ProductCart includes features that allow you to import product options with the rest of the product information. For example, imagine you are importing 100 different types of shoes, each with different sizes and colors. You can now do this directly when importing new products or appending data to your existing product database.

For each product, you can specify whether there will be one or two option groups, the attributes for each option group, and whether a selection is required. If the product catalog does not already contain an option group or option attribute matching the one(s) imported, a new option group and/or attribute is created.

Product option information is imported in 3 separate fields, for up to 5 option groups (ProductCart actually supports unlimited option groups, but the Import Wizard limits this feature to 5 mainly for performance reasons). The three fields must be formatted as shown below. The actual field names can of course be different from the ones listed below: you will just need to map it correctly during the field mapping step of the import process.

• Option 1: Name of the first Option Group (e.g. “Color”).
• Option 1 Attributes: List of attributes for the first option group. The format must be as follows: separate values for each attribute with a *, and separate different attributes with **. The attribute values that can be imported are:
  • Option Attribute: text, required (e.g. “red”)
  • Price differential: number, not required (e.g. “5.50” or “-6”)
  • Wholesale Price differential: number, not required (e.g. “3.20” or “-4.35”)
  • Display order: integer, not required
  • Option active/inactive: 1 (active) or 0 (inactive), required.

For example, assume you are importing 3 sizes: medium, large and x-large, and that you want them to appear in that order, and a price increase is associated with the x-large option (plus $5 for retail customers and plus $4.50 for wholesale customers). The field should contain the following string:

Medium*0*0*1*1**Large*0*0*2*1**X-Large*5*4.50*3*1

Once again, note how different option attributes are separated by a double asterisk (**), whereas individual settings for a certain attribute are separated by a single asterisk (*).

• Option 1 Required: Whether or not customers are required to make a selection from this menu. Use 1 for “Required” and 0 for “Not Required”.

Repeat the same for the other option groups, if any.

This empty file includes all available fields, including the 5 option group fields. If you use this file as a starting point to create your import data file, make sure to remove any columns (fields) that you do not plan to import.

The column name in your spreadsheet will define the Custom Search Field (CSF) that you are importing. The string in the cell defines the Custom Search Field value. It does not matter which CSF variable you map a field to, as ProductCart will use the value in your column header. For example, if one of your CSFs is “Year”…. then make sure that the column header in your import file is “Year” and ProductCart will properly assign the 'value', regardless of whether you map the column to CSF (1), CSF (2) or CSF (3) during the mapping process.

NOTE:

• You can only import up to 3 custom search fields per import document.
• ProductCart v3.5 and above support unlimited Custom Search Fields. If you have more than 3 Custom Search Fields for a product, you must import the remaining Fields in subsequent import files. So, if you have a product that has 6 CSFs assigned to it, you would have to assign the first 3 values in the inital import and the remaining values in the next import file.
• All products in the same import file must share the same columns (CSFs), which means that you cannot import 3 Custom Search Fields for product XYZ and 3 different Custom Search Fields for product ABC…. the imported custom search fields must be the same for all products in the import file.
• Since the columns are shared by all products listed in the spreadsheet, you will need to leave the corresponding cells empty for products that do not use those custom search fields
• The column name must match the name of the Custom Search Field that you want to use for the imported products, otherwise a new one will be created (and assigned to the products you are importing, with the values you are importing).

If you are using the Apparel Add-on for ProductCart, you can now import apparel products settings when importing products into the database. In addition, you can now import “sub-products” via a separate import wizard. Please consult the Apparel Add-on User Guide for more information.

The Apparel Add-On gives stores using our ProductCart ecommerce software the ability to handle inventory for product variations (e.g. color/size combinations). This is something that is typical of the apparel industry (yep, that's why we called it that way), but it applies to other industries as well. What does this mean? For example, if size 32/34 of a pair of black pants is out of stock, the customer is notified. More information on ProductCart’s Apparel Add-on.

Now that you have prepared your MS Excel or CSV data file for importing it into ProductCart, select Import Products from the Products menu. You can either upload the data file through the Import Wizard or via FTP (recommended for large files).

• If you choose to upload the file through the Import Wizard, browse to the file and then click on the Upload button. The form is validated to make sure that only *.XLS and *.CSV files are uploaded to ProductCart.
• If you prefer to use FTP, you must place the file in the pc/catalog and then enter the file name (just the file name, not the path to the file) in the field provided. ProductCart will verify that the file exists on the server before proceeding to Step 2.

If you cannot proceed to step 2:

• If you are uploading the file through ProductCart, try transferring the file via FTP instead.
• If you have transferred the file via FTP, make sure that you uploaded it to the correct folder (pc/catalog) and that you entered the right file name in the corresponding field.

Once the file has been uploaded/located successfully, the Import Wizard will ask you to choose whether you intend to import new data to the database, or update existing data.

• If you are importing data into your store database, ProductCart will NOT import products whose SKU matches that of products that already exist in the database, to avoid importing duplicates. Products that could not be imported due to a SKU match are listed at the end of the import procedure.
• If you are updating data note that the SKU is used as the product identifier. A product’s data will be updated when the SKU is an exact match. If the SKU is not an exact match, a new product will be added. When you are updating the product catalog, you will need to make a selection among the following three options, with regard to the way category information is imported:
  • Add the category specified for a product to the list of categories that the product is assigned to. This option allows you to add new categories to the list of categories that the selected product is already assigned to. Since you can import up to 3 categories for each product, this option allows you to quickly add products to multiple categories.
  • Move the product from the existing category to the new category specified in the data file. When this option is selected, the product is removed from the current category and assigned to a new one.
  • Ignore any category information included in the data file. Any category information in the data file is simply ignored, i.e. a product’s category assignment is not altered.

The next window allows you to easily map fields between the two databases. Your import file’s column headings are listed on the left side and ProductCart’s on the right. Carefully map each field. Leave blank the ones for which you do not have any information to import. Note that the following fields are required if you are importing new products: SKU, name, description, online price. If any of these fields are not mapped, you will receive an error message and will be prompted to map them. The SKU field is required if you are updating product information.

Make sure that you don’t map the same field twice. If you do so, ProductCart will return an error message and will prompt you to verify that all fields are mapped uniquely.

Next, ProductCart shows you the fields as you have mapped them. Verify that the information is correct, and then proceed to the next step. Otherwise, go back to Step 2 and change the incorrect mapping settings. ProductCart will now start populating the database. Depending on the number of records that you are importing, this task may take several minutes.

Once all records have been imported, ProductCart will display a report that lists the number of records successfully imported, those that could not be imported and why. For example, you will be shown a list of products that could not be imported due to the fact that some of the required fields were not present, or because you were importing duplicate SKUs.

To visually confirm that the import was successful, navigate to the product catalog (thru the storefront) to verify that product information was imported as you expected. Also, select Manage Categories to ensure that the category tree that you imported was organized correctly.

If you are having problems importing products, check the Knowledge Base for a number of frequently asked questions that might shed some light on what could be occurring on your store. You can find a link to the knowledge base on the ProductCart Support Center.

ProductCart now allows you to easily “undo” an import/update by reverting back to the data that was stored in the product database before new data was imported or updated.

For example, let’s assume you decide to update the price of 100 products by importing a file that contains the SKU and the new price. You then realize that the price update should have been applied at a later time. How can you go back to the original price for the time being? The “undo” feature allows you to quickly restore the original prices.

When products are imported or updated, ProductCart saves a log of what information was imported in the files importLogs/prologs.txt (product information) and importLogs/catLogs.txt (category information). The system uses this information to remove the data that was imported/updated.

To undo the last import/update, select Import Products from the Products menu, and then click on the Undo Last Import or Undo Last Update button.

ProductCart v3 and above provide the ability to export product information in a format that makes it easy to edit it and re-import it. From the Products menu, select Import Products and then click on the Export for Re-Import button. You will be take to a page that allows you to select the products to export. You can:

• Export all products in your store: All products in your store will be exported.
• Locate products that you would like to export: This option allows you to locate the products that you would like to export using ProductCart’s product search feature.
• Export all products in a range: if you have over 200 products, the system will automatically create several sub-sets of 200 products each.

Once you have determined the products that you would like to export, you will be taken to a page that provides a long list of all the fields that can be exported. The select the ones that you need to update and import back into the system.

ProductCart will export the data to a CSV file. If you edit it in MS Excel, remember to properly format it before importing it back into your store. See the section above about properly formatting your file for import.