Importing product records

Contents

• Before importing products
  Creating missing categories automatically
• Undoing an import
• Import modes
• Essential fields
  • Fields and picklists specific to your cart
• Category, manufacturer, product type and other special fields
• The import process
• Notes on the CSV file structure
• Troubleshooting import errors
  • Edit in place
• Product fields for Zen Cart
• Product fields for osCommerce

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:

• If you omit the Category field, the product records will be added to the database, but will only be visible within ShopAdmin, not in the Zen Cart admin area. But this is acceptable in some situations, e.g., when you want to update your product categories separately, via another import. And when you update your product categories, they will become visible within the Zen Cart admin area.
• You probably also want a fifth field, Enabled, with all its values set to 1 so that the newly-imported products are all enabled and available in your website. You could choose to omit the Enabled field on the initial import step, e.g., if you want to test the imported records, and you can subsequently update the Enabled field of the product records after testing.

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:

• Click the “Table info” menu under “Settings” and click the table name in the list at left. The field list appears on the right. (Click the image on the right for a screen shot.)
• Click the “CSV” button to download the list of fields.

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.

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

• The first (header) row of this file should contain the names of the product fields, in no particular order.
• The subsequent rows should contain the product data records, one per row.
• Ideally, the header row would contain the same field names that ShopAdmin recognizes. (These are the same names that ShopAdmin uses when it exports your product records.) If so, ShopAdmin will automatically detect the matching and pre-select the fields in step 2 of the import wizard. If you cannot have those names, you can still match the names in the CSV file with the ShopAdmin names in step 2.
• When importing new Product records, the Product ID field should not be included. This is because the shopping cart database automatically generates the product id value for each record. If your CSV data includes a Product ID column, you can set its field mapping (in step 2) to “Do not import”, so that the field is ignored by the import wizard.

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

• Incorrect field separator. At step 1, you can specify either the comma or the tab as the field separator that your input data uses. If you make the wrong choice here, your data will not be aligned correctly, as you can see from the screens on the right.
• Data errors.In Step 2 of the import wizard, ShopAdmin displays an error dialog (such as the one shown at right) if you have any of the following situations in your data:
  • You omit a value for required field.
  • You provide data in a format that ShopAdmin is unable to convert.
  • The data value in the cell for a picklist field (e.g., Category, Product type or Taxable Class) does not match any of the allowed values for that picklist. You can get the allowed picklist values configured within your cart database as noted above. ShopAdmin matches the picklist value you specify against these configured values, ignoring case, and issues an error if there is no match. Even though it ignores case, it includes spaces and special characters, so you will want to make sure that the values in your data conform to the values in your picklists.
  • The data value in the cell for a reference field (e.g., Manufacturer) does not match any value in the table; e.g., the manufacturer name you have specified in the cell does not occur in your cart database.
  
  The data error display

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:

• Misaligned columns. If you have omitted a column of data in your record, the entire record shifts to the left, and you will see data errors from the missing cell all the way to the right.
• Incorrect field matching. If you have provided header names that are not the ones that ShopAdmin uses, and you have matched your names with the ShopAdmin names, you must ensure that the matching “lines up” correctly. For example, if you have incorrectly matched a product name field with a date field, ShopAdmin might show data conversion errors where it cannot convert the name to a date value.

Product fields for Zen Cart

Product fields for osCommerce

Posted in General | No Comments »