Product import/export using excel spreadsheet

Introduction

The Zeald Website management system allows you to export and import your products to and from various spreadsheet formats. This facility is most commonly used for two purposes:

To import an initial product batch into the website, prior to the website launch
To bulk update product data. Exporting products to Excel, making changes then re-importing is a faster and more efficient way of performing bulk product updates by individually editing each item in the website manager

Please Note. This is an advanced method and requires some knowledge of Excel and database structure. We only recommend the use of this method for the loading of catalogues containing more than 100 products. For smaller catalogues, we recommend that the standard Zeald website administration is used.

import export excel-538

The first screen of the Product Importer

Preparing for a bulk product import

Careful preparation for a bulk product import is the key to successfully listing your product items on your website - bulk item importing is a time-consuming and methodical process. Remember that presenting your product inventory in a clear, attractive and well-structured way is key to increasing your website results, so it is worth taking the time to get your initial website inventory load right. An initial inventory upload consists of three main steps:

Decide what categories you will be using, and how these will be structured
Prepare your images and then bulk upload them using the Zeald bulk image importer
Fill the bulk import spreadsheet with data and import it using the Zeald bulk 'Product Importer'

To take each of the three steps in order:

1. Deciding what categories to use.

You may already have your items categorised, e.g. if you have an existing website or you have items stored in inventory management software. If you do not have your items categorised, or you feel your items could be categorised in a better way then take the time to think of what categories you would use to categorise your items. As an example imagine you have a product 'Chocolate Gift Box' - and you are running a special promotion for Mother's Day. There would be a number of ways you could categorise this item - you could create a top-level 'Gift Box' category and put the product in there, or you could create a sub-category under the 'Gift Box' category called 'Chocolate Gift Boxes'. You could even create a special 'Mother's Day' category temporarily just for Mother's Day.

As a general rule, categories should clearly divide your inventory in the most logical way possible - but don't have too many categories within categories. No one wants to have to click through 5 or 6 categories just to find a product. For a small product range, you may only need top-level categories, for a very large range you may need to go three categories deep. Have a look at other e-commerce websites, particularly ones in your industry and get an idea of how others have categorised their inventory.

2. Preparing your images

A well-presented product image will greatly assist in the online selling of that item - ideally, all products you sell will have a high-quality image associated with them. If you do not have photographs of your stock then you may want to consider professional product photography. Extra images are also helpful in showing different views of the product. Collate all your product images, give them logical names (we recommend the SKU number of the product then the description - eg. '1234_chocolate_truffle.jpg') and place them in a single folder. The next section 'Zeald automated batch image import' describes how to upload your images to your website and gives more information about this step.

3. Fill the bulk import spreadsheet with data.

Finally, you need to fill the bulk import spreadsheet with data - one row for each product. On this spreadsheet, you will supply the data that your Zeald website needs to display the product. The first column is the SKU (Stock Keeping Unit number, sometimes also called Product Code or Product ID) this is the reference the Zeald system will use to keep track of your products. If you do not have SKU numbers for your products, then simply give each product a number on the spreadsheet e.g. 1,2,3,4 etc. On this spreadsheet you will also supply the image name of the image that is to be associated with the product - this must be the exact file name of the image being used (eg. '1234_chocolate_truffle.jpg').

Once you have completed the spreadsheet then import it into the Zeald administration area (under 'Items' > 'Product Importer'). More information on the columns to use for this import and how to do the import can be found on this page in the 'Product database Excel spreadsheet template' section.

Zeald automated batch image import

Your product images are not automatically imported with the Excel bulk image importer. These images need to be uploaded separately, using the bulk image import facility. You can bulk upload images by using the "drag and drop" feature or by selecting multiple images in the Image importer.

Image Format

Ensure all images you are attempting to import are in jpeg/jpg, gif, or png format. Other image formats such as tiff, psd etc are currently unsupported and must be converted to one of the above file types before the import.

Image Size

The bulk image importer will automatically re-size images. Thus if you have very large images these images will be re-sized prior to displaying them on your product pages. An image width of between 550 pixels (px) and 1000 px is recommended. If your image width is smaller than 550 px you will not be able to use it to display an 'enlarged view' of the image. Images that are wider than around 1000 px will have very large file sizes and may take a long time to upload.

Image name

Images must match the exact name of the item in the product spreadsheet (case sensitive). It is also a good idea if the image name contains important keywords that describe the product. As a recommendation, use the SKU code of the item, then the item name - for example, "12345_chocolate_truffle.jpeg". Using the SKU code will make it easy for you to tell which image is associated with which product; using the item name will increase the keyword saturation of the pages that this image is used and may help to achieve higher search engine ranking.

Image file names should have names that are alphanumeric only with no special characters such as / $ or # etc
It is not recommended to have spaces in the image name, underscores are recommended instead. This is because you will need to match the actual image file name, with the file name listed in the import spreadsheet, and there is the possibility that an image name could contain an extra space - making troubleshooting difficult - for example, if the image file name is 'my image.jpg' (note two spaces) and on the spreadsheet, you list 'my image.jpg' (note single space)

To batch upload your images

Once your images are ready to upload to the website, go to:

Website Manager > Items > Image importer
Drag and drop the files OR use the "Select files" button (hold down the CTRL or SHIFT key to select multiple files)
Click Next, and then Process Selected Images (you can also choose to upload more images before processing the images).

Please note:

the drag and drop feature doesn't work on Internet Explorer,
Apple Mac users please use the "Apple key" and click the file, or you can also select multiple files by holding SHIFT)
To import a very large number of images, we recommend you break them down into smaller batches to reduce the chance of interrupted transfers.

An example screen from the Bulk Image Importer

Product database Excel spreadsheet template

You may download a copy of the Excel spreadsheet here

For an example of your own product database Excel spreadsheet template (if you have a Zeald website already constructed), please download the spreadsheet for your website, which you can find under the Items tab > Product Exporter

Please note: You may hide any of the columns that you do not require to make the administration of the spreadsheet more usable by selecting the unused columns from the top and right-clicking and selecting Hide from the drop menu. Alternatively, you may delete the unused columns

You may upload information as an Excel Spreadsheet, Comma separated values (CSV) or tab-separated values. Please note that multi-line CSV files (aka Microsoft CSV is not supported). Whichever format you choose, only rows intended for upload to the website should be included in the spreadsheet, with an optional header line in row 1.

Product Import Fields

The Product database Excel spreadsheet includes each of the following fields. The following list includes an overview of each column in the spreadsheet and describes the type of data required and the type of data expected by the website manager system.

* Denotes a required field

SKU #*

Stock Keeping Unit Number

Sometimes referred to as the Product Code or the Product ID, the SKU # is a unique code used by the system to identify each unique product of the database. If a change is made to the SKU # in the product spreadsheet and the spreadsheet is re-uploaded to the website, a new product is created on the website when the spreadsheet is uploaded.

You may use the product code that you use internally to identify your products if you have one.

An SKU Number may contain any numbers and or letters.
The SKU Number is case sensitive
Do not use 'spaces' or unusual characters such as '%$#@!'
Underscores '_' and hyphens '-' are permitted.
It's crucial to include leading zeros in your SKU to prevent data errors. For example, '000112345'.

Product Title*

The name of the product, this information is used as the product title for the product page and displayed as a link to the product page on the category page. The product title should 'Stand alone' free of the category labels and the website theme - if a user finds the product page via a search through a search engine, the product title should explain the product sufficiently for users to instantly recognise the product that he/she is looking for.

For example, the product title '6 piece chocolate gift box' should not be reduced to '6 piece' even though the product may be adequately explained through the category structure (e.g. if the product was categorised under 'Gift boxes > Mother's day >Chocolates')

Specify the full path for every product

It may seem repetitive process to specify every category and sub category for each product, but the automated scripts need to know the exact category path for every product. A tip - when you have created a category title you can easily duplicate further titles for products below in the spreadsheet by dragging the bottom right hand corner of the cell.

Accuracy

Make sure that the product category structure is accurate and free of spelling errors before uploading the product spreadsheet.

It is a common mistake for the spreadsheet to contain misspelled categories. If a category title is misspelled a duplicate category is automatically created. For example if the top level category 'Chocolates' is specified for a number of products and the category label is misspelled 'Chocolate' for just one product a new category will be created and two categories ('Chocolates' and 'Chocolate') will exist.

If you do make a mistake in this way, it will need to be corrected in the website administration area by editing each product and placing it in the correct category. The incorrect category can then be deleted.

Importing Multiple Categories

Click here to for a guide to import a product into multiple categories

Description

Sometimes also known as 'short description'. Text entered within this field is used to displayed on the category page alongside the product title (but only if the category page has been set to 'list' mode - if the category page shows products in a grid view then this text is not shown). This text should consist of a basic overview of the product in a sentence or two. This information is also used for search engines (as the 'Meta Description').

Detailed Description

This field represents the full description of the product and should be made up of more than 2 paragraphs of keyword rich content to describe the product. It should describe the product in terms of benefits as well as listing the features.

Note. If rich text formatting is required for the display of product description, this entry must be entered into the spreadsheet in HTML format. If you are not proficient in HTML code, we recommend you format the text using the website content management software, after the product has been loaded initially.

Any formatting in your Excel spreadsheet (such as bold or Italic fonts) is ignored. You should use the HTML code instead. The following are examples of some common HTML tags for basic text format functions.

html-tags2

Note: In some, more rare cases you may with to enter special characters such as the copyright - © symbol or the trademark - ™ symbol. In these cases you need to use a special HTML code version of these symbols. For example use © for copyright and ™ for trademark. You can find more information on this here

Product Image

The Product Image field is used to match the product to the appropriate product image (e.g. to an image you uploaded using the bulk image importer as described above). It is important that this name is accurate in order for the image to be connected with the product.

The name entered in this column for the image must be identical to the image name itself including the filename extension

The product image name may contain any numbers and or letters.
The name is case sensitive
Do not use 'space' or unusual characters such as /%$#@!
Underscores '_' and hyphens '-' are permitted.

Thumbnail Image

The thumbnail image is displayed when the product is viewed in 'category view'. In order to have your product display a thumbnail image you need to specify the image name in this column. Use the exact image name, exactly as per the 'Product Image' field.

Large Image

The large image is the image displayed when users click on your product image to display a full size version of your image. Displaying a large image is optional, if no large image is specified, then users will simply not be able to enlarge the product image. If you wish to have a large image associated with your product then you need to specify the image name in this column.

Use the exact image name, exactly as per the 'Product Image' field. Do not use the 'large image' column if the image you have for the product has a width less than 550px as images displayed for the large image are automatically displayed at a width of 550 px

Product Weight

Product Weight applies if you will calculate shipping on your website by item weight (to calculate shipping in this way is optional). If you choose to calculate shipping in this way then when items are added into the cart by a site user, the total weight is calculated and this total along with the delivery location determines the final amount the user is charged for shipping on your website.

The unit of measurement used is Kg. Weight must be entered in the following formats: e.g.
200g = 0.2
1kg = 1
1.5kg = 1.5

Maximum product weight permissible is 25kg

Product price

No dollar symbol is required. This will be displayed as $NZ unless otherwise specified.

You may choose to display prices on your website as inclusive of, or excluding GST. You can find more information on this here.

If your prices exclude GST then enter the GST exclusive price. If your prices include GST then enter the GST inclusive price.

Import Products with Complex Pricing

Complex pricing is imported using additional columns in the normal product spreadsheet. Each column represents a different pricing level / tier. It includes quantity breaks, customer group assignment, and currency.

If a particular column doesn't apply to a product, then it can just be left empty. If there are many different quantity breaks or customer group tiers for different products, it is often best to import products in groups that have similar pricing levels to avoid ending up with tens or hundreds of pricing columns.

Click here to for a guide to import a products with complex pricing

Wholesale Price

You need only enter a wholesale price if depending upon customer status; your site sells products at different rates; for example at one price to customers and at a discounted price to dealers / retail customers.

Wholesale prices are only accessible to users who have been allocated a login.

Meta Title

Every page within your site should include Search engine Meta data. The product spreadsheet enables you to add metadata to each product page of the website via the 'meta title', 'meta description' and 'meta keyword' columns.

When users search for and find your site using search engines / directories then the Meta title (sometimes just called 'Title') is generally displayed as the search result title; often the Meta Description is displayed as text below the search result title. The 'Meta title' is also used as the 'title' for the webpage (usually shown in the title bar of the browser).

It is advisable to make the title no more than approximately 10 words long and try to put descriptive 'keyword' phrases about the webpage in the title, rather than just the product name. Also try to use keywords & phrases that are used in the page content.

Automated Meta Title generation

If the Meta fields are left empty initially the website manager system will automatically generate a Meta title based on the product title, product categories and the general website title.

For example the website 'Online Gift baskets' with a product titled ' piece chocolate gift box' within the category structure >Gift boxes >Mothers day >Chocolates, would have a Meta title automatically generated for it as follows Gift boxes - Mothers day - Chocolates - 6 piece chocolate gift box Online Gift baskets

Meta Description

For the meta description write one or two sentences, comprising a maximum of 26 or so words, describing the page content.

Include some of your keywords if you can as the more times your keywords appear on the page (whether as 'hidden' metadata or visible site content) the better your chances are of being found by search engines or directories. However do not repeat a word too many times and do not use more than 25 / 26 keywords. Search engines 'see' both of these strategies as spam (false attempts to increase search engine rankings) and will ignore the web pages / site.

Automatic Meta Description Generation

If the Meta Description is left blank then the Zeald system will automatically set the Meta Description as the text of the 'Product Description' field

Meta Keywords

Meta Keywords are not used by modern search engines, and we do not recommend spending time populating meta keywords.

Extra Images

Please note: the management of extra product images is not currently supported via the excel spreadsheet. If you have a large number of products and many of these products have more than one image associated with them then you may want to consider a small website customisation to allow you to link extra images with products on the product import and export.

Product Options

Please note: the management of 'Product options' are not currently supported via the excel spreadsheet method. Product options must be setup separately using the Item editor function of the website administration. If you have a large number of products to import, and many of these products have options associated with them, then you may want to consider a single small change to allow you to import product options.

Promotions

Product Promotions may be specified and updated using the excel spreadsheet. On the spreadsheet you can specify the promotion type, promo discount, promo type, promo text, promo start date, promo finish date for each product.

IMPORTANT: If you are adding percentage promotions, in the promotion type field in excel, you must type an apostrophe first, i.e. '20% - otherwise the system won't pick it up correctly.
promotion final

Please note: The update of 'Cross sells' are not currently supported via the excel method. Cross sells must be setup separately using the Item editor function of the website administration

Ongoing Product database updates

Once you have a product database in your Zeald website you may wish at some point to update some aspects of your product database. For one or two products it is best to edit the individual items in the Website manager. However when you are updating multiple products it is most efficient to do this using the Bulk Product Export / Import system. Doing this is a three step process

1. Export your items to a spreadsheet

Under the Items tab, there is a tab called Product Exporter. Simply press to export your items. The system will then generate a spreadsheet of your products. Choose to 'Save' this file and save the file to a location you will remember e.g. 'My Documents. As a further step we recommend that you make a copy of this spreadsheet and make your changes to the copy. That way if anything goes wrong in the update process you will be able to restore your product database to how it was by uploading the original spreadsheet.

2. Make your updates in the Product Spreadsheet

Open the spreadsheet you downloaded in step 1 and make whatever changes you need to.

The 'SKU' column is used to track the individual products. If the system can find a matching SKU then the matching product will be updated using the values in the spreadsheet. If the system cannot find a matching SKU then the spreadsheet line will be added as a new product.

If you are making a change to an image, then note that the spreadsheet includes only the image name - you will need to use the bulk image importer to upload any new images required.

3. Upload your spreadsheet

Use the Product Importer (under the 'Items' tab) to import the spreadsheet that you have amended as per step 2. Once the changes are made they cannot be undone. For this reason we recommend having a copy of the original product database, as described in step 1. If you do not have a backup and need your database re-set then this will generally be possible - a charge will apply for this service.

SKU	Title	Category	Sub Category	Subx2 Category
CH1234	6 piece chocolate gift box	Gift Boxes	Mothers Day	Chocolates