Announcement:Materials and articles for ProductCart 5 can be found at our brand new support center.

Create an account to edit articles | See Formatting Syntax for Wiki syntax | We look forward to your contribution!

Importing Product Categories

Overview

The Category Import Wizard allows you to import category information into your ProductCart-powered store, allowing you to potentially add hundreds of categories to your store database in a few, easy steps. You will be able to quickly import categories, sub-categories, and all of the many category-specific settings supported by ProductCart.

The feature was included starting with ProductCart v3.11 and it can be useful in a variety of circumstances. For example, if you are moving from another e-commerce application to ProductCart, this tool – along with the Product Import Wizard - can assist you in rapidly populating your product database.

Preparing for the import

Properly preparing your category database file prior to importing it into ProductCart can save you a lot of time (and headaches). Because many of the instructions are identical when importing a product database into ProductCart, in the following paragraphs we will sometimes refer to the Product Import Wizard documentation.

You can import from the following file types:

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

Importing a customer 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 catalog subfolder within the pc folder.
  3. Specifying whether you are importing new categories or updating existing categories.
  4. Mapping fields from the file to ProductCart's database.
  5. Confirming mapped fields.
  6. Performing the import.
  7. Reviewing which records could not be imported and why.

ProductCart's Category 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.

Formatting your import file

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.

Preparing an Excel file for import

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

  1. “Clean up” the spreadsheet:
    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
  2. Create a name for the data range:
    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
  3. Save the .XLS file.

The vast majority of technical support requests that we receive from users of ProductCart that are not able to successfully import an XLS file are due to improperly setting the IMPORT range. If you are experiencing problems, please review the steps outlined above to make sure that the IMPORT range has been properly defined.

Categories and sub-categories

Order categories in your spreadsheet by category level (parent categories at the top, then sub-categories) because ProductCart cannot assign sub-categories to a parent category if the parent category was not imported before the sub-categories that refer to it.

In other words, you need to tell ProductCart to create a subcategory, before you can use it as a parent. When you create the subcategory in your import document is completely irrelevant, as long as you do so before you use it as a parent category. For example, you could:

- create all level 1 categories - then create all level 2 categories - then create all level 3 categories

… or you can do it for each category tree “as you go” (parent 1, sub-categories of parent 1, parent 2, sub-categories of parent 2, etc.).

Other file preparation notes

  • If you are importing a CSV file, do not use double or single quotes at the beginning of any field.
  • When importing new categories, you cannot map the following fields:
    • Category ID, as it does not yet exist in the database
    • Featured Sub-Category Name
    • Featured Sub-Category ID
  • Whenever you want to update category data, or import sub-categories, and your store has categories that share the same name (e.g. Men > Sweaters; Women > Sweaters), always use the “Category ID” field instead of “Category Name” to match the existing categories (or assign the sub-categories to the correct parent category). The same is true for using “Parent Category ID” instead of “Parent Category Name”, and “Featured Sub-Category ID” field instead of “Featured Sub-Category Name”. Otherwise ProductCart will use the first match it finds in the database.
  • Whenever you want to update category names, always use the “Category ID” field to match the existing categories.

Verify field requirements

Carefully review the requirements for each field - specified in the next section - so that you will not run into conflicts when mapping the fields.

Rename the column headings so that they can easily be recognized when mapping the fields.

Field requirements

Field Name Data Type Required Comments
Category Name Text Yes Category Name. Text string (alphanumeric characters). Used as category identifier when updating data (if data file does not have Category ID field): only if Category Name is an exact match data is updated. Max characters: 50.
Category ID Number Yes Only required when updating data. Used as category identifier when updating data (higher priority than Category Name field). Must be a number or will not be imported.
Small Image Text No If left blank, ProductCart will automatically populate the field with the string “no_image.gif”. Max characters: 150.
Large Image Text No If left blank, ProductCart will automatically populate the field with the string “no_image.gif”. Max characters: 150.
Parent Category Name Text No Used as parent category identifier if data file does not have Parent Category ID field. Max characters: 50
Parent Category ID Number No Used as parent category identifier (higher priority than Parent Category Name field). Must be a number or will not be imported.
Category Short Description Text No Max characters: ~ 65,000
Category Long Description Text No Max characters: ~ 65,000
Hide Category Description Number No Must be a number or will not be imported. Use 1 for yes, 0 for no.
Display Sub-Categories Number No Display style for sub-categories. Must be a number or will not be imported. Use 0 for default. 0 = horizontally, with images; 1 = in a list (no images); 2 = in a drop-down menu; 3 = default (inheriting parent’s settings)
Sub-Categories per Row Number No Number of category columns. Must be a number or will not be imported. Use 0 for default.
Sub-Category Rows per Page Number No Number of category rows. Must be a number or will not be imported. Use 0 for default.
Display Products Text No Display products style. Leave empty for default style (inheriting parent’s settings): h = Horizontally; p = Vertically; l = In a list; m = In a list (multiple Add to Cart)
Products per Row Number No Number of product columns. Must be a number or will not be imported. Use 0 for default.
Product Rows per Page Number No Number of product rows. Must be a number or will not be imported. Use 0 for default.
Hide category Number No Hide this category in the storefront. Must be a number or will not be imported. Use 1 for yes, 0 for no.
Hide category from retail customers Number No Hide this category in the storefront from retail customers (wholesale customers can see it). Must be a number or will not be imported. Use 1 for yes, 0 for no.
Product Details Page Display Option Text No Display layout for the product details page. Max characters: 250. Leave empty for default style (inheriting from parent): C = Two Columns - Image on Right (“classic”); L = Two Columns - Image on Left; O = One Column - Image Centered
Category Meta Tags - Title Text No Max characters: 250.
Category Meta Tags - Description Text No Max characters: ~ 65,000
Category Meta Tags - Keywords Text No Max characters: ~ 65,000
Featured Sub-Category Name Text No Only used when updating data. Used as sub-category identifier if data file does not have Featured Sub-Category ID field. Max characters: 50
Featured Sub-Category ID Number No Only used when updating data. Used as sub-category identifier (higher priority than Sub-Category Name field). Must be a number or will not be imported.
Use Featured Sub-Category Image Number No Must be a number or will not be imported. Use 0 for small image, use 1 for large image.
Category Order Number No Must be a number or will not be imported.

A sample category import file

You can download a template spreadsheet that can help you visualize how category information should be organized.

The spreadsheet contains 3 worksheets. This is just to show you different scenarios in which the data can be imported. You should never have more than 1 worksheet in your import file. Remove the other two before executing the import, if you end up using this file as a starting point for your own category import data file.

New Categories

You will notice that there is no “Category ID” column as the ID is not known before the record is created into the database. The same is true for the “Parent Category ID”. When the “Parent Category ID” is not there and the “Parent Category Name” is empty, the new category is created in the root. The columns “Featured Sub-Category ID” and “Featured Category Name” are also not present as we assume that you are importing new categories.

Update Existing with Names

Here we assume that you are updating existing category information, matching the existing categories based on their name. If you have categories that share the same name, you should not use this method and instead use the category IDs.

Update Existing with IDs

Here we assume that you are updating existing category information, matching the existing categories based on the category ID. So the most important column in this spreadsheet is the first one, “Category ID”. Other information within a certain category can also be updated using the category ID. For example, to indicate which sub-category would become “Featured”, you can use the “Featured Sub-Category ID” column.

Use (3) if you wish to update existing category names. That is: ProductCart will match the existing categories using the category ID, then will use the “Category Name” field to update the category names.

When you are updating information, you might only be importing one or two columns in addition to the column used to match the existing categories (Category Name or Category ID). For example, you might import a spreadsheet with “Category Name” and “Display Products” to change the product display settings for the imported categories. When you do so, remember to remove the columns you don’t need.

Category import steps

Step 1 – Upload the file

Now that you have prepared your MS Excel or CSV data file for importing it into ProductCart, select Products > Import Categories to start the Category Import Wizard.

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 following folder: “pc/catalog” and then enter ONLY THE FILENAME in the field provided. ProductCart will verify that the file exists on the server before proceeding to Step 2.

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

Step 2 – Map the fields

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.

ProductCart saves the current mapping preferences so that you don't have to remap all the fields in case to need to import data from the same file (e.g. you may use the same Excel spreadsheet multiple times to regularly import new category data from another application).

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.

Step 3 – Confirm mapped fields

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.

Step 4 – Import completed

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 categories that could not be imported due to the fact that a match to the parent category could not be found.

To visually confirm that the import was successful, select Manage Categories and look for categories that you have just imported (you can use the search feature to easily locate a specific category).

Undoing the last import/update

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

When category accounts are imported or updated, ProductCart saves a log of what information was imported in the file importLogs/catlogs.txt. The system uses this information to remove the data that was imported/updated.

To “undo” the last import/update, select Import Categories from the Manage Products menu, and then click on the Undo Last Import or Undo Last Update button. Note that the button is only visible once you have imported or updated categories. The “undo” can only be applied to the last import or update. It cannot be applied to previous imports or updates.

Note that this feature is not a substitute for backing up your store database before importing or updating data. Backing up your database remains the safest way to ensure a quick recovery in case the database is compromised during an import or update procedure. That's because the “undo” feature is script-based, and therefore any glitch in the way the script runs (e.g. a server problem at the time the script is run) could prevent you from returning exactly to the status in which the database was in before the import was executed.

Updating data within an existing store database

You can either import new categories or update/append existing categories. Adding sub-categories to existing categories falls under importing new categories. If you wanted to change the display settings, instead, you would be appending data (i.e. changing existing records in the categories table).

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 can use either the category ID or the category name to match the data that you are importing to an existing category.

Back-up recommendations

To ensure that you can quickly revert back to the way your store database was structured before performing an import or update, we strongly recommend that you ALWAYS BACK UP your existing store database before performing an import or update procedure.

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 especially important when you are importing or updating a large number of categories. The “Undo” feature discussed above is not a substitute for having a good back-up policy.


QR Code
QR Code Importing Product Categories (generated for current page)