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:
- Properly formatting your file. The steps to be taken are different depending on whether you are planning to use a *.xls or *.csv file.
- 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.
- Specifying whether you are importing new customers or updating existing customer accounts.
- Mapping fields from the file to ProductCart’s database.
- Confirming mapped fields.
- Performing the import.
- 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)
Preparing an Excel file for importIf you are importing an Excel file, the following steps MUST be taken in order for the import to be successful.
- Clean up” the spreadsheet:
- Remove all other worksheets
- Remove all cell formatting
- Remove all empty columns
- Remove all columns that you do not intend to import
- Remove all empty rows
- 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…
- In Office 2007, select Formulas > Define Name
- Enter the name “IMPORT” without the quotes and click on the OK button.
- You can review existing “Names” used on the spreadsheet by selecting Formulas > Name Manager
- 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).
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|
|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.
Important 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 categoryAs 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.
Step 1: Upload the fileNow 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. updateOnce 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: MappingThe 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 mappingNext, 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 processProductCart 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 completedOnce 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.