Importing product records


Video by kelvyn

Contents


Before importing products

Before you import new products, you might want to:

  1. Set up categories for your products. The shopping cart requires that every product be given a category. Without a category, the product will not appear in your catalog.
  2. Set up manufacturer records. When you add a product record into your cart database, you will most likely want to specify the manufacturer for that product. And the manufacturer record is maintained in a separate table. Therefore, it is a good idea to set up all your manufacturer records before you import your products. You can import manufacturer records via a process very similar to that for product records, via the Import tool in ShopAdmin’s Manufacturers area.


Creating missing categories automatically

If you wish, you can also create categories along with your product records, all in one go. To do this, you must:

  1. Check the “create missing categories” checkbox in step 1 (see screen shot at right).
  2. Include a Category column in which the category names are formatted according to the convention described below.

If you choose this option, ShopAdmin will examine all the product records in your CSV data and create category and parent category records in the correct hierarchy for all of them.

This is not recommended, though, because it tends to add unnecessarily large numbers of category records into your cart database.


Undoing an import

When the import process is completed, ShopAdmin automatically creates a filter in the list of product filters. This new filter will select and display the newly-added product records. The name of the filter is in the form Product imports yyyy-mm-dd HH:mm, containing the time at which the import was completed. See the screen shot at right.

If you wish to undo your import, you simply click the filter to select all the records you just imported, and then click Tools – Delete all.


Import modes

You can use ShopAdmin’s product import wizard for imports as well as updates. You choose the mode (import or update) in Step 1 of the wizard.

For convenience, ShopAdmin’s product import wizard recognizes the product’s Model field as a unique identifier for product records. (This is just a convenience, however – you can use ShopAdmin’s import features even if your cart does not need this feature.) ShopAdmin treats a record in your CSV data as a duplicate record if its Model field matches a product record that is already in the database. We say that a CSV record is new if it is not a duplicate.

ShopAdmin lets you import your product records in one of four modes:

  1. Import and update (also called upsert), where new records in the CSV data are added to the database, and duplicate records are used to update the corresponding database records. In this mode, ShopAdmin checks for multiple occurrences of the same value in the Model column of your CSV data, and flags such occurrences as errors. You can choose this mode via the “Update the database” option (see screen shot at right).
  2. Import without duplicates, where new records in the CSV data are added to the database, and duplicate records are marked as errors and rejected. In this mode, as with upsert mode, ShopAdmin checks for multiple occurrences of the same value in the Model column of your CSV data, and flags such occurrences as errors. You can choose this mode via the “Reject” option.
  3. Import and add, where all records are treated as new, and no duplicate checking is done. You can choose this mode via the “Add (no checking)” option.
  4. Update only, where you do not wish to add any new records, but just update a small set of fields of your product records. In this mode, your CSV data should contain a column for the lookup field, which can be either the Product ID or the Model, and any other fields you wish to update. ShopAdmin will then update only the records matching the lookup field value with the corresponding fields in your CSV data. This mode is described separately elsewhere.


Essential fields

Zen Cart: At a minimum, when importing new product records into Zen Cart, you must have at least these three fields specified: Name, Model and Product Type. In addition, you might want to include the Category and Enabled fields:

OsCommerce: With osCommerce, there is no Product Type field, so the essential fields are Name and Model. The notes above, about the Category and Enabled fields, also apply to osCommerce product imports.

Fields and picklists specific to your cart: You can download a full list of available field names, their data types, and picklist values configured specifically for your cart:

The field information for products includes the valid picklist values configured in your cart for Category, Tax class and Product type.


Category, manufacturer, product type and other special fields

Category: Categories are hierachical, meaning that you can have categories and sub-categories and sub-sub-categories. Moreover, a category name does not identify it uniquely in the hierarchy. For example, there might be a sub-category named Pants that is a sub-category of Mens apparel, and another Pants category that is a sub-category of Womens apparel. So when you add product records, you must identify uniquely the category to which it belongs. ShopAdmin requires you to provide the category chain from the top level to the category in which the product belongs, separated by “: ” (a colon and a space). For instance, with the above category hierarchy, you might use Womens apparel: Pants to refer to the Pants subcategory of the Womens apparel category.

Products in multiple categories: Shopping carts let you list the same product in multiple categories, so that shoppers can find them easily. If you want a product to appear in two or more categories, simply list all the categories in the cell, one in each line. Here is a sample of what your data might look like with products in multiple categories. When creating the CSV file using a spreadsheet program such as Microsoft Excel or OpenOffice Calc, you can use the key sequence Alt-Enter to introduce the line break between the categories within the category cell.

Category Product name Model
Bikes: Aprilia: Dorsoduro: 2001 Sample bike SAM_B1
Bikes: Aprilia: Dorsoduro: 2001
Bikes: Aprilia: Pegaso: 2002
Another sample bike in two categories ASM_B2
Bikes: BMW: F650GS: 2001
Bikes: Aprilia: RSV Mille: 2001
Bikes: BMW: R1150: 2001
A third sample bike in three categories ASM_BMW

Manufacturer: This field refers to the manufacturer of the product. There is a separate Manufacturer table in the cart database that maintains information about manufacturers, and this field points to the record in that table for the product’s manufacturer. When importing products, ShopAdmin lets you use either the manufacturer ID or the manufacturer name in this field. During the data validation (Step 2 of the import wizard), ShopAdmin checks the manufacturer id or name against those already in the table, and issues an error of it cannot find a matching record. This error check ensures that you do not accidentally import malformed data into your cart database.

Product type: This field is required for Zen Cart installations (it is not a part of osCommerce installations). For each product record being imported, you must specify one of the product types configured in your cart database. Zen Cart uses the product type to determine the logic to display the product to your site visitor. Consequently, ShopAdmin matches the value you provide in this field against the permissible values in your cart configuration, and flags as erroneous any records that do not provide a permissible value. Note also that, broadly speaking, the product type field is not a field whose value is intended to be shown to the visitor. It is for internal use within Zen Cart, and determines the layout and associated logic for displaying the product’s information your website’s visitor. See the Zen Cart tutorial page if you want full details.

Model: This field specifies the “model number” for your product. Its value uniquely identifies the product record, so no two product records in your cart database can have the same model. Maintaining this uniqueness enables you to update your product records by model, thus simplifying the update process.

Date added: This field will be automatically set to the current date when importing new product records.


Import process

Here are the steps for importing product records.

  1. Prepare a CSV file containing the product records you wish to import. You can download a sample CSV file. See the notes below about the CSV file structure.
  2. From the Products tab, click the Tools button and select “Import/update”.
  3. The import wizard appears, displaying step 1. Choose the file from which to import the data, or paste the data into the text box. Click “next”.
  4. The import wizard shows step 2. If you need, you can match the product field names in your CSV data with those that ShopAdmin recognizes. Click “next”. ShopAdmin validates your data, making sure that the data types match up correctly, e.g., that date values such as “Date purchased” and picklist elements such as “Category” are correctly specified in each record. If there are no errors, or if you chose the setting to omit erroneous records. it shows step 3, just before the import process begins.
  5. Click the “Import” button. ShopAdmin imports your product records into your shopping cart database, and when done, shows a “Done” button.

    Click the “Done” button, and you will see the list of product records, with your new product data now in place.


Notes about the CSV file structure


Troubleshooting import errors

ShopAdmin performs several data validity checks during the import process. Most errors are detected in Step 2 of the import process. Error messages are shown in a small popup window (see screen shot at right). You can resize the popup or drag it around on the screen. If you click on any of the error messages in the error popup, the corresponding cell in the data area will be highlighted, so you an easily find the cell to which an error message refers.


Edit in place

In many cases, you can also correct errors in step 2 via the in-place edit feature: Simply click the cell
you want to edit, and an editor is shown into which you can type your new data. If the cell is in a column for a picklist or a reference to another table (such as Manufacturer), it will show a dropdown containing the values valid for that column.

Here are some errors you might encounter.


Misaligned data

Field Separator

In most of these cases, you can simply press the Back button, correct and re-paste your data. When you encounter an error, you might check some common sources of data errors:


Product fields for Zen Cart

Field name Data type Required? Comments
Always free shipping Yes/No
Category Picklist See notes above
Date added Date & time Automatically set when new products are added or imported
Date available Date
Description Text
Discount type Picklist Permissible values are configured within Zen Cart.
Discount type from Yes/No
Enabled Yes/No Set to ‘yes’ if the product should be shown in the shop
Image String URI offset for the product’s image
Is call Yes/No
Is free Yes/No
Is virtual Yes/No
Last modified Date & time Automatically set when the product record is modified in any way
Manufacturer Reference to Manufacturer Points to the record in the Manufacturer table
Master category Picklist
Meta tag description Text Description text used in the meta tag when this product is displayed to your site’s visitor
Meta tag keywords Text Keywords used in the meta tag when this product is displayed to your site’s visitor
Meta tag title String Page title text used when this product is displayed to your site’s visitor
Metatags model status Yes/No Whether the model should be shown in the meta tag
Metatags price status Yes/No Whether the price should be shown in the meta tag
Metatags products name status Yes/No Whether the name should be shown in the meta tag
Metatags title status Yes/No
Metatags title tagline status Yes/No
Mixed discount quantity Yes/No
Model String y Model number of the product. This value must be unique, i.e., no two products can have the same model number.
Name String y The name of the product
Price Currency
Price sorter Fractional number
Priced by attribute Yes/No
Product ID Whole number This field is the record id for the Product record.
Product type Picklist y Permissible values are configured within Zen Cart.
Products ordered Fractional number
Qty box status Yes/No
Quantity in stock Fractional number
Quantity mixed Yes/No
Quantity order max Fractional number
Quantity order min Fractional number
Quantity order units Fractional number
Sort order Whole number
Tax class Picklist Permissible values are configured within Zen Cart.
URL String
Viewed Whole number
Weight Fractional number


Product fields for osCommerce

Field name Data type Required? Comments
Date added Date and time Automatically set when new products are added or imported
Date available Date
Description Text
Enabled Yes/No
Image String
Last modified Date and time Automatically set when the product record is modified in any way
Manufacturer Reference to Manufacturer Points to the record in the Manufacturer table
Model String y Model number of the product. This value must be unique, i.e., no two products can have the same model number.
Name String y The name of the product
Price Currency
Primary category Picklist
Product ID Record ID for the Product table
Products ordered Number
Quantity Number
Tax class Picklist
URL String
Viewed count Number
Weight Number