Importing data

Report · Mar 14, 2012

Importing product data into an online store
Using the basic import process to import contacts
Contact Import - Advanced
Importing Web App items
Importing web pages and 301 redirects
Importing blog posts from an external blog
Importing CRM Forms
Importing non-English character sets
Troubleshooting import issues

In this article, you'll learn how to import all types of data into the database for your site. You'll see how to update inventory in an online store, update the customer database with new contacts, Web App items, 301 redirects for pages, blog posts, CRM forms and non-English (unicode text) characters. Use the links at the top to jump to the relevant section.

Importing product data into an online store

You have two methods you can use to update inventory in an online store. If you only have a few items to edit or update, you can make the changes manually using the interface in the Admin Console. The first option involves following these steps:

Choose eCommerce > Products.
Select the name of an existing product in the list. Or click the option in the sidebar to Create a new product.
After clicking an individual product, the Product Details page for that product appears. Use the provided fields to make changes to the item's description, URL, image, or other information.
Scroll to the bottom and click Save to update the individual product.

This approach gives you access to the full range of features available in the interface when adding products. However, as the number of products in an online store increases, the manual process can become tedious and labor-intensive.

The second method is useful when you have a significant number of products to add or update in an online store. In this case, you can import a spreadsheet that contains all of the data for all of the products at once. This process is outlined below.

Creating the import file

You can use a Microsoft Excel spreadsheet file to manage an entire product catalog. Using this strategy, you can add thousands of products to a single file and quickly add, update or delete them within the system by importing the edited spreadsheet file. You can import products as often as desired.

Note: The import file allows you to import all of the key information associated with products. However, some information cannot be imported using this method and needs to be entered manually. Please refer to the Product

Import file for full details and instructions. Remember, product names can only contain alphanumeric characters; do not use special characters, such as quotes or & when naming products.

If your store is brand new, the first part involves obtaining the Product Import file that you'll use as the template to add or edit product information. After downloading it, you'll use the Product Import file to enter new products and fill out the store's inventory. Follow these steps:

Choose Admin > Import Data.
Select the Products icon.
Select the option in the sidebar to Download Import Template File.

If the store already contains existing products, follow the steps below to export the list of product data that you can use to update and re-import to the system.

Exporting a file containing the existing products in an online store

Follow these steps to export a store's entire product catalog as an Excel spreadsheet:

Choose Admin > Import Data.
Select the Products icon (as described in the section above).
Click the option in the sidebar to Download entire Product list.
Save the file as an Excel spreadsheet to your local machine.

Note: At this point, Mac users must manually rename the resulting .xls file with the .csv file extension. This ensures that the file can be correctly interpreted by Microsoft Excel when you open it, without specifying the delimiter. (By default, Microsoft Excel looks for TAB delimiters, while the exported file uses comma delimiters). Windows users do not need to perform this step.

Also, when opening the exported file (renamed to .csv) on MAC, some special characters may appear at the beginning of the file next to the Product Code column’s name. This occurs due to a known issue with the Mac version of Microsoft Excel; the program does not correctly read the characters which usually define the UTF-8 encoding signature for a file. Until Microsoft resolves this issue, you can ignore or delete those special characters. Or, if the product data you are importing includes non-English characters, save the Excel file as the Unicode file type to preserve them.

Editing the exported spreadsheet file

Use Microsoft Excel to edit the file to add, edit or remove products. After you are finished, follow these steps:

Save the file as either CSV (comma delimited) file type, TXT (tab delimited) file type or the Unicode file type (if you need to fully support UTF8 character set). For more details about importing non-English characters into the database, see the section in this article below titled Importing non-English character sets.
Return to the Admin Console. Choose Admin > Import Data. Select the Products icon.
The Products Import page appears. Click the browse button and navigate to the edited CSV or TAB delimited file you saved in Step #1.
Select the corresponding File Type (Comma Delimited CSV or Tab Delimited) by checking the radio button. If you saved the file as Unicode format type, choose the Tab Delimited option.

Note: Sometimes during a normal save operation, Microsoft Excel automatically converts the file’s format from CSV to Tab Delimited. If the File Type option you selected is not working, try the other one, especially if you are not sure which file type your import document is using, or if you encounter errors like the Number of columns in the imported file is incorrect or No name found.

5. Scroll to the bottom and click Next.

6. A preview of the import is displayed. Check the data in the preview and then scroll to the bottom and click Import. \

Note: Previously, an error would occur during the import process if the spreadsheet was missing any of the product code information. The system has recently been updated to automatically generate product codes if none exist during an import operation. Each product should contain a unique product code. This is not currently enforced at import time, but you should assign unique codes to products in order to avoid the possibility of data being overwritten. You can use the eCommerce > Products section of the Admin Console interface to locate the existing duplicate product codes. Once you've identified the duplicates, you can either manually edit them or choose to have the system automatically assign unique product codes.

To learn more about working with products, see Adding products to catalogs in an online store.

Using the basic import process to import contacts

An online business relies on the database to store and track all customer information. In this section, you'll learn how to import your existing customer database into the system. There are two options: you can perform a basic or advanced import. Before importing, be sure to familiarize yourself with the differences between the basic and advanced import to choose the import process best for your situation.

Using the basic import option, you can import data for the following fields:

Email Address (the system's unique identifier)
Title (such as Mr, Mrs, or Ms)
Firstname
Surname
Company Name

A single field for Full Name does not work within this system. The Full Name must be modified.

Using the advanced option, you can import and populate more than 30 fields in the site's customer database. To review the complete list, choose Admin > Import Data > Contacts (Advanced).

To import your existing customer database into this system, follow these steps:

Choose Admin > Import Data.
Select the Basic or Advanced (Detailed) import option.
Download the Import Template File. This file contains the columns of data in the order in which they must be arranged for import.
Export your existing customer database into Excel, CSV or TAB delimited format. If you use an application such as ACT, Goldmine or other popular CRM packages, choose the option to export your existing customer database into one of these file formats.
Ensure your columns match those found in the Import Template file. Please keep in mind, even if you don't want to populate a column, it must still exist in your import file in order to successfully import the file. However, you can leave the column empty.
Save the file as either CSV or Tab Delimited (preferred) format.
Import the CSV or TXT file by browsing to select it from your local machine.

Import rules you should be aware of when importing contacts

An email address may only ever belong to one contact. That is no two people can share the same email address regardless of import option you choose. Please note that company records however can share email addresses with contacts.
In the basic import contact records are identified and matched using the email address. If no email address is provided then the contact is imported but not matched to any existing contacts. Subsequent imports of the same file will create new records for any contact without an email address.
If you use the detailed import and chose to use the Your ID field, then contacts are matched using this field. When this column is utilized then any contact without an email address can still be matched. Please note that even if you use this column, no two contacts can share the same email address under any circumstances.

Contact Import - Advanced

Advanced contact import is done in the same fashion as the basic import except the import template it is using contains more columns.

To download the import template go to Admin ->Import Data -> Contacts (Detailed) and click Download Import template file from the sidebar.

When using the Advanced Contact Import method, the following rules apply:

Mandatory Fields that must be imported to avoid wiping out an existing value:

Title
First name
Last name
DOB
Username
Email
Customer Type
Lead Source Type
Industry Type
Rating Type

Fields that will not get overwritten if the values are empty in your import file:

Company name
Password
Addresses
Phone Numbers or Web URL

Alternatively you can use the Basic Contact Import which allows you to update:

Title
First name
Last name
Email
Company (can be left empty without affecting an existing record)

Importing Web App items

Using this import option you can manage your entire web app catalog using a single Microsoft Excel document. The import feature can be used to add new items or update existing ones.

You can download the entire list of items for any of your web apps. Select Modules >Web Apps and choose Download entire Web App item list from the sidebar.

To import items, choose Admin > Import Data. Select the Web Apps option. Then, select the Web App from the list that you'll be importing items. Download the import template file (using the option in the sidebar). The import template file includes the instructions on how to setup and edit the import file. The first column in the import file is titled "External ID". The value you enter in this column will uniquely identify each Web App item; this means that during subsequent imports, existing Web App items can be updated.

Linking Web App items to customers during Import

We App items can be linked to customers in your database, which is useful when you are porting a site with a lot of existing items submitted by existing clients to the system.

Follow these steps:

Import all contacts first, with their allocated Username and Password, as well as their secure zone subscriptions. This is done using the Contacts Import (Advanced) option, as discussed above.
Once all contacts are in the CRM Database, perform a Report > Customer Report > Contacts only, which includes the CRM ID field. This will give you the database Entity ID for each Contact.
Locate the Entity ID and enter it in the Web App import template... this links the Web App to the CRM Record.

Troubleshooting Web App imports

If you import is failing here are some tips you need to know when troubleshooting the file:

Look at the custom field types and compare them with your data. If you have used Number as a type for one of the fields, you can not place a string into it. Even if the field contains a phone number, you should use string. It is quite rare that you'll ever need to use the Number type field. The same is true when importing the date. The format has to match the system format or you will not be able to import data into it.
If you do use the Number type, the field must have to have at least one numeric digit in it. You can enter 0 as a placeholder if desired.
You must enter a date in the mandatory fields. These fields include the name, description and any field you have selected as mandatory by selecting the Mandatory option when you created it.

Importing web pages and 301 redirects

Web page import feature was created for SEO purposes and it allows you to create 301 redirects from old URLs to your new URLs and that way keep the existing search engine rating. So, if you have moved a site from another host to us this is the feature for you. For example if your old site was in ASP, and a particular web page url was index.asp, you can now redirect any user accessing that page to index.htm.

It allows you to import a URL with any extension except .aspx, which is reserved for the system.

This is what the file should look like:

Important considerations:

Old URL extensions can uses any file extension, with the exception of .aspx. Extensions such as .php, .asp, .cfm, .pl, .cgi, .htm, .html and others are supported.
If the destination URL is a relative path, it can have an extension of .htm, .html or no extension, such as: home.htm, home.html or home. No other extension is allowed.
If the destination URL is on an external server (if it starts with http:// or https://) any extension is allowed.

Note: To update or modify your current redirect set-up, you'll need to re-import the data to provide the old and new URLs.

Importing blog posts from an external blog

Follow these steps to import the blog posts from an existing TypePad and Word Press into the blog on your site:

Export all of the existing posts from TypePad or WordPress.
Log into the Admin Console and create a blog. Choose Website > Blog > Create a New Blog. Remember to enable the trackback option, if you have a trackback feature enabled in your existing blog software.
Choose Admin > Import Data. Select the Blog Import option. From the menu that appears, select the blog you created in step #2.
After selecting the blog, another menu appears. It contains the list of external blog engines that are supported by the blog import feature. Choose the Typepad / WordPress option.
Next, you'll upload the data. Select the exported TypePad or WordPress file and click Next. Remember, the preview feature is not available when importing blog posts.

Warning: Double-check that you are importing the correct file before clicking Import. You must only import a blog once. If you import it again, it will result in a duplicate set of the posts, comments and trackbacks being added to the system.
After you import the blog, visit the live site in a new browser window. Navigate to the blog and check that the blog posts have imported correctly. If not, delete the blog and repeat the entire process again, begining with step #2.

Importing CRM Forms

You can import Customer Relationship Management data into the system. Follow these steps:

You can import a CRM Forms attached to a contact; this is accomplished by choosing the advanced contact import option.
If you need the template, select the CRM form from the menu that appears at the bottom of the advance contact import field list. Click the option to download the template file.
Only include the contacts that the CRM form needs to be attached. If the contact doesn’t have the CRM form attached, then it will attach the form and import the values.
You can only select one CRM form at a time.

Importing non-English character sets

If your site uses a non-English character set such as Chinese, Japanese, Arabic, and others, be sure to save your Excel import file as a Unicode Text file, as shown below:

Next, after choosing Admin > Data Import, select the Tab Delimited file type. After the file imports, the non-English text is not affected or changed as it is imported.

Undoing an import

If you make a mistake during your import you can use the Undo Previous Import option to undo the previous operation. When you undo the import any contact created or updated during the import will be deleted permanently. This option should only be used when you are first populating your customer database as you would only be deleting new contacts.

To use this feature, choose Admin > Import Data > Contacts > and click the option in the sidebar to Undo Previous Import

Troubleshooting import issues

There are two main reasons why imports fail.

1. Your choice of file type can affect your import. For example when in Microsoft Excel you choose to save your file in CSV format the output file will have all your columns separated by a comma (,).

Original File

CSV File Output

john@mail.com,Mr,John,Citizen

sally@yahoo.com,Ms,Sally,McDermott

When you use a CSV file you can no longer use commas (,) inside any of your columns. Otherwise the extra commas will appear as extra columns and will affect your import. If wish to use your own commas in your columns then you should save your file as a TAB delimited file. In fact TAB delimited files are the preferred format for the import feature.

2. Your columns must not include any line breaks otherwise your import will fail. When you create your CSV or TAB delimited file each customer record will appear on one line (as shown above). If for example you were to use the Detailed import and the address column included a line break the output would appear as:

CSV File Output

john@mail.com,Mr,John,Citizen,233

Pacific Highway,North Sydney,

sally@yahoo.com,Ms,Sally,McDermott

The Line Break directly after 233 has pushed the remainder of the columns to a new line. The import feature will treat the new line as a new customer which is incorrect. Its likely that your import will not fail but the data imported will be incorrect.

After you create your CSV or TAB delimited file its important to reload it into any text editor such as Notepad and quickly view its content to ensure nothing out of the ordinary stands out. Doing this ensures your imports are error-free and painless.

Report · Jun 07, 2012

Is there a way to createst a CSV file for the blog import? I need to do a find/replace across all my old blog posts, but that's impossible once they're in the system.

Report · Aug 06, 2012

When I need to import blog posts from a CSV file, since BC has no way, I have created a temporary WordPress site, used one of the serveral WordPress plug-ins like CSVImport that can create WordPress posts from a CSV file, then use the WordPress export to create an export file. BC can import the export file that WordPress creates. BC cannot however import categories or tags from a WordPress export file, only the post title and post body.

Jonathan @ AtlantaWebDesignGA.com

Report · Oct 15, 2012

Good one. Well said. It ensures easy import and export of data from various source.

Report · Oct 19, 2012

Hi, I'm working on an product import document and I'm having a hard time figuring out how to get the imported products to retain the tags entered in column "S". For some reason they show up in the keywords area of the product, but not in the tags from the Actions menu > Tag this Item area. I'm using these tags to display products is different places on the site and would love to not have to apply those all manually. Is there a way to use the product import file to populate the tags area under the Actions Menu? Also, if so, what characters are used to separate multiple tags?

Many thanks!

- Eric

Report · Dec 03, 2012

I'm sharing this for those that are struggling with the date format in Excel ...where the date format changes such that BC cannot recognise start and release dates. This is because Excel uses the default date format that is set by the Operating System. So Windows OS by default will show the date as dd/mm/yyyy. The problem is with the / slashes. BC will only recognise date formats as: dd-mm-yyyy i.e. with hyphens.

I spent hours searching until I found this article which solved the problem for me once and for all. This is the solution I found online, corrects the default date format affecting Excel:

Solution: http://office.microsoft.com/en-gb/excel-help/change-the-default-date-format-HP005285937.aspx

I hope that helps!

Report · Jan 30, 2013

Why can we not have a import of CRM option to delete the records as we do with products as this feature is very sadly missing and certainly a feature well needed by many. A more irgorous and practical approach to cleaning up CRM records is very much needed as if yuo have a client hit by spammers it can take forever to clean up what should be a relaively straight forward exercise

Report · Apr 15, 2013

Hello. I've imported a contacts list using the method instructed by BC, have a Tab Delimited file with 5 columns :email, title, first name, last name, and company name, and it has uploaded successfully and the contacts all appear in the Customers list. My question is, where do I find the Company Name listed? It doesn't appear in the Customer details anywhere.

Can anybody help with this please.

Grant

Report · Apr 15, 2013

Ok, I've just realised that the contact name and the company name have been listed in the Customers listing. Is there a reason for this?

Report · Jun 26, 2013

">">

">"> URL: %22%3E%3Cscript%3Ealert(1)%3C%2Fscript%3E %22%2F%3E%3Cscript%3Ealert%281%29%3B%3C%2Fscript%3E URL: %22%3E%3C%69%6D%67%20%73%72%63%3D%78%20%6F%6E%65%72%72%6F%72%3D%61%6C%65%72%74%28%64%6F%63%75%6D%65%6E%74%2E%64%6F%6D%61%69%6E%29%3B%3E HTML:"><img src=x onerror=alert(document.domain);> #whatever=javascript:alert('xss')

Report · Dec 06, 2013