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 Customers with the Customer Import Wizard

Overview

ProductCart includes the ability to import customer information from an existing customer database so that you can quickly add it to your e-commerce store. This feature can be useful in a variety of circumstances, and specifically in the following two scenarios:

  • When moving to ProductCart from another e-commerce application
    Before abandoning your existing e-commerce system, export all customer records, if your old shopping cart software allows you to do so. Importing existing customer information into ProductCart will help you make the move smoother and less inconvenient to your customers, who will not have to re-register with your new store.
  • When importing customer information from your accounting or CRM software
    If you are adding an online store to your sales efforts, you can use the Customer Import Wizard to add all the customer accounts that are already in your accounting or CRM system to the online store, thus making it easy for your customers to start using the store.

Their e-mail address will be used by ProductCart as their user name, and a random password will be created for them automatically during the import procedure (if a password is not already associated with customer accounts). You can then email your customers using the Newsletter Wizard to let them know that they can log into their new account at any time by using their email address. To retrieve their temporary password, they can simply use the password reminder feature.

Properly preparing your customer 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 that section of the ProductCart documentation.

What files you can import

You can import from the following file types:

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

How the Customer Import Wizard works

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 customers or updating existing customer accounts.
  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 Import Wizard will guide you through the process step by step. First, however, you will have to spend a little time formatting the file so that it can be seamlessly imported into ProductCart.

Formatting Your File for Import

Carefully review the requirements for each field so that you will not run into conflicts when mapping them. In addition, we strongly suggest 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
  • rename the column headings so that they can easily be recognized when mapping the fields (if not self-explanatory)

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.

Updating Existing Customer Information

You can either import new customer accounts or update/append existing customer information. For example, if you need to update the newsletter preference (receive vs. not receive the newsletter) for all customers, you could import a file that only contains the e-mail address and the RecvNews field (see the next section for details about each import field). If you are using the MailUp E-mail Marketing system, you can import newsletter preferences separately for each list.

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 customer’s e-mail address as the account identifier and update database records whose e-mail addresses are an exact match with the data that you are importing. If the e-mail address is not an exact match, a new customer (database record) will be added. The field is not case sensitive.

Fields Description

You can import the following fields. Please carefully read the format requirements. Note how in some cases the Customer Import Wizard will automatically populate a field if left blank or not imported. In the case of the password field, ProductCart automatically generates a random password for the new customer. In all other cases, ProductCart will populate the field with the text string “NA during import” (Not Available during the import procedure).

Field Name Data Type Required Comments
Email Text Yes E-mail address. Text string (alphanumeric characters). Used as product identifier when updating data: only if e-mail is an exact match data is updated. Max characters: 150.
Password Text No If left blank, ProductCart will automatically populate the field with a random, encrypted password. Field is required during checkout. Max characters: 100.
Customer Type Number No Must be a number or will not be imported. Defines the type of customer. Use 1 for wholesale, 0 for retail. If left blank or not mapped, defaults to 0 (retail customer).
First Name Text Yes If left blank, ProductCart will automatically populate the field with the string “NA during import”. Field is required during checkout. Max characters: 30.
Last Name Text Yes If left blank, ProductCart will automatically populate the field with the string “NA during import”. Field is required during checkout. Max characters: 30.
Company Text No Max characters: 50.
Phone Text No Field is required during checkout. Max characters: 20
Billing Address Text Yes Field is required during import and checkout. Max characters: 255
Billing Address 2 Text No Max characters: 150
Billing City Text No Field is required during checkout. Max characters: 50
Billing State Text No This field is used to define the state or province for US and Canadian customers. Required during checkout only if the Country is US or Canada. Max characters: 4
Billing Province Tex No This field is used to define the state or province of customers outside of US and Canada. Max characters: 50.
Billing Postal Code Text No This field is used to define the postal code and is required during checkout if the Country is US or Canada. Max characters: 10
Billing Country Code Text No Field is required during checkout. No. characters: 2 - Use the ISO Country Code found under your “Settings > Manage Countries” page.
Ship To Company Text No Max characters: 150.
Ship To Address Text No Max characters: 255.
Ship To Address 2 Text No Max characters: 150.
Ship To City Text No Max characters: 50.
Ship To State Text No Max characters: 4.
Ship To Province Text No Max characters: 50.
Ship To Postal Code Text No Max characters: 10.
Ship To Country Code Text No No. characters: 2 - Use the ISO Country Code found under your “Settings > Manage Countries” page.
Ship To Phone Text No Max characters: 20.
Ship To Email Text No Max characters: 100.
Custom Fields Text No You will be able to import any of the custom fields that have been created in the Control Panel. For example, if you have 4 custom fields, you can import 4 additional fields in your import file. Each column in the import file must represent a specific custom field (colum heading = custom field name)
Reward Points Balance Number No Must be a number or will not be imported.
Newsletter Subscription Number No Must be a number or will not be imported. Defines whether the user opted to receive the store newsletter. Use 1 for yes, 0 for no.
Pricing Category ID Number No Must be an integer. Corresponds to the ID of the pricing category to which the customer belongs.

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, enquire with your Web hosting company about how you can have your database backed up and restored.

Import Field Notes

  • The “E-mail Address” field is required for both a new import and an update procedure. The field is not case sensitive. In ProductCart, the e-mail address is also the customer login name.
  • If you are importing a CSV file, do not use double or single quotes at the beginning of any field.

Customer Type and Pricing Category

As mentioned above, the Customer Import Wizard allows you to specify the type of customer you are importing. You can import a customer account as either a retail or wholesale customer, or belonging to a certain pricing category:

  • If the file that you are importing does not contain a customer type column, all customers will be imported as retail customers. The same is true if you are mapping the field, but the field is left empty or has the value “0” in it.
  • Map the customer type field and enter the value “1” to import the customer as a wholesale customer.
  • When you are importing the Pricing Category field, the customer type will be overwritten if the pricing category that you are importing the customers into has wholesale vs. retail privileges. In that scenario the customer type will be changed to wholesale regardless of the value in the “customer type” field.

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 Import Customers from the Customers menu to start the Customer Product Wizard.

We recommend that you back up the store database before proceeding. This is especially important if you are importing data into already active store. You will be able to quickly restore the database in case for any reason the import procedure did not complete successfully.

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.

Step 2 - Import vs. update

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

  • If you are importing data into your store database, ProductCart will NOT import customers whose e-mail addresses match those of customers that already exist in the database. Customers that could not be imported due to an email address match are listed at the end of the import procedure.
  • If you are updating data note that the e-mail address is used as the customer identifier. A customer account will be updated when the e-mail address is an exact match. If the e-mail address is not an exact match, a new customer account will be added.

Step 3 – Fields mapping

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. ProductCart will automatically try to find a match. Carefully map each field that has not been mapped, and review the mappings set by ProductCart. Leave blank the ones for which you do not have any information to import. Note that the e-mail address field is required.

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 customer 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 4 – Confirm fields mapping

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 and change the incorrect mappings.

Step 5 – Import process

ProductCart will now start populating the database. Depending on the number of records that you are importing, this task may take several minutes.

Step 6 – 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 customers that could not be imported due to the fact that you were importing duplicate e-mail addresses.

To visually confirm that the import was successful, select Customers > Locate a Customer and search for a customer record that you have just imported.

"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 customer table before new data was imported or updated.

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

To undo the last import/update, select Import Customers from the Customers 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 products. The “undo” can only be applied to the last import or update. It cannot be applied to previous imports or updates.

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.

DO NOT use this feature if you have made changes to customer records since the last import/update or you risk losing that information.


QR Code
QR Code Importing Customers with the Customer Import Wizard (generated for current page)