Difference between revisions of "X-Cart:Importing products (X-Cart v.3.5.x-4.0.x)"

From X-Cart 4 Classic
Jump to: navigation, search
(Added version tags)
Line 1: Line 1:
 +
{{XC 3.5}}
 
{{Note|This article describes the import process for X-Cart v.3.5.x-4.0.x. For instructions on importing to later versions, see [[X-Cart:Importing_and_Exporting_Data | this article]].}}
 
{{Note|This article describes the import process for X-Cart v.3.5.x-4.0.x. For instructions on importing to later versions, see [[X-Cart:Importing_and_Exporting_Data | this article]].}}
  

Revision as of 10:54, 8 November 2011

X-Cart 3.5
Note: This article describes the import process for X-Cart v.3.5.x-4.0.x. For instructions on importing to later versions, see this article.

X-Cart allows manual adding of products and defining each and every parameter for any entry in the product list. However, in many cases such maintenance of your catalog in its actual state often turns into a very complicated task, if not an impossible one. Imagine you'd like to offer hundreds, thousands or even more products to your visitors with some parameters (such as, for example, quantity or availability) changing daily or weekly. There are lots of e-shops having separate product database stored on a local workstation or in Intranet and usually incompatible with the common formats of World Wide Web databases. Anyway, in most situations this problem can be solved by importing the long list of items from a large file in a special format (CSV) that can be retrieved by exporting your data from any popular database or a spreadsheet processor.

Note: If you are running X-Cart PRO you should login as provider (to provider area) in order to access "Import products" section.

CSV file format

CSV is an abbreviation for Comma Separated Values, but it can use comma, tab or semicolon as a delimiter to separate values in a plain text file. Columns of data are separated by a delimiter (comma, tab or semicolon), and each row of data ends with a carriage return.

CSV file format is supported by most of grid calculators like Microsoft Excel, Lotus 1-2-3, Quattro Pro etc. You can import products into X-Cart store using CSV format lists. Thus you can keep shop information up to date by importing product list files instead of updating information through the web interface. Large imports can take some time, so please be patient.

As for Microsoft Excel, the most popular software for spreadsheet processing, the CSV saves only the text and values as they are displayed in cells of the active worksheet. All rows and all characters in each cell are saved. Columns of data are separated by commas, and each row of data ends with a carriage return. If a cell contains a comma, the cell contents are enclosed in double quotation marks.

If cells display formulas instead of formula values, the formulas are converted as text. All formatting, graphics, objects, and other worksheet contents are lost.

If your workbook contains special font characters such as a copyright symbol (©), and you will be using the converted text file on a computer with a different operating system, save the workbook in the text file format appropriate for that system. For example, if you are using Windows and want to use the text file on a Macintosh computer, save the file in the CSV (Macintosh) format. If you are using a Macintosh computer and want to use the text file on a system running Microsoft Windows, save the file in the CSV (Windows) format.

Importing a list of products

1. First of all, click on 'Import products' item in the Products menu. The 'Import products' form will appear on the screen.

Picture 1: 'Import products' form

Imp products.gif
Imp products1.gif

2. Set the columns order. It must be the same as the order used in the CSV file you are going to import.

YYou do not have to supply values for all the fields (to do this, just place a double delimiter (;;) instead of entering the value between two delimiters (nowiki>;value;</nowiki> )). However, filling out price, product, descr and avail fields is mandatory as they are required for the application to work correctly. If you are planning to use the "UPDATE INVENTORY" function for further quantity or price updating it's necessary to define product_code as updates are done only by the field product_code.

  • productid - Product id
  • productcode (recommended) - Product code or SKU.
  • product (required) - Product name.
  • distribution - Product distribution (for EGoods)
  • weight - Product weight
  • list_price - List price
  • descr (required) - Description
  • fulldescr - Detailed description
  • avail (required) - Quantity in stock (items)
  • forsale - Available for sale (Y or N)
  • shipping_freight - Shipping freight
  • free_shipping - Free shipping (Y or N)
  • discount_avail - Apply global discounts (Y or N)
  • min_amount - Min order amount
  • dim_x - x dimension of the product package (for shipping)
  • dim_y - y dimension of the product package (for shipping)
  • dim_z - z dimension of the product package (for shipping)
  • low_avail_limit - Low limit in stock
  • free_tax - Non-taxable (Y or N)
  • product_type - Product type
  • manufacturerid - Manufacturer id
  • return_time - Time during which the product can be returned to the store
  • category - Product category
  • price (required) - Price of the product
  • thumbnail - Thumbnail image file name and (if necessary) file location
  • product_options - Product options
  • extra_fields - Extra fields
  • manufacturer - Product manufacturer
  • applied_taxes - Taxes applied to the product

3. Select the delimiter used in the CSV file you are going to import (comma, tab or semicolon).

4. Select the default category from the drop-down list. If the value of the category field is not specified in the CSV file, the default category will be applied.

5. Enter the category path separator for "category" field (e.g. Books///Software).

6. If you have thumbnails for your products, you should put them all into one directory and specify the absolute path to this directory in the field "Directory".

VERY IMPORTANT! Your images must be located on your Web server (not on your local computer if your access is remote), you should specify the full path from the system root (e.g. /home/mysite/pictures on UNIX or D:/Pictures on Windows).

7. Do one of the following:

- Enter the location of the CSV file if you have uploaded it before, or - Click on Browse and select the necessary CSV file.

8. Click on Import products.

If you want to drop all existing products in your store before you import new products, select the Drop all products before import check box. Be careful, this operation cannot be undone and all existing data in the product catalogue will be lost.

Practical examples

To make your work with and understanding of the CSV format easier, let us provide you with some examples regarding the usage and editing of such files (by the way, any plain text editor such as Notepad is fine for that).

Example 1. You have data in the following format:

10;SKU10;Leather jacket;250;5;Made of natural leather;Clothes/Jackets;jacket.gif
20;SKU20;Black gloves;30;0.5;Fine quality gloves;Clothes/Gloves;gloves.gif

You should set the columns order in the following way:

0: productid
1: productcode
2: product
3: price
4: weight
5: descr
6: category
7: thumbnail

The rest of the columns should be set to NULL.

If your file format is not the same, you should set a different columns order.

"Leather Jacket" will be placed into the subcategory "Jackets" of "Clothes" category and "Black Gloves" into the subcategory "Gloves" of the same category.

Please note that X-Cart also has the ability to import CSV files with field names in the first row. This helps to re-import products that have just been exported to a CSV file. To do this you need to select the option 'Get column names from the first row of CSV file' on the 'Import products' form. And the previous example in this case should be modified to look like this:

Example 2:

productid;productcode;product;price;weight;descr;category;thumbnail
10;SKU10;Leather jacket;250;5;Made of natural leather;Clothes/Jackets;jacket.gif
20;SKU20;Black gloves;30;0.5;Fine quality gloves;Clothes/Gloves;gloves.gif

In this case there is no need to set the order of columns for product import in accordance with the CSV file manually: all you need to do is to select the 'Get column names from the first row of CSV file' check box.

You can use product import for adding new products. It may be especially convenient if the products you want to add do not differ much from the products which are already in your database.

Example 3. You have exported your products to a CSV file of the following format:

productid;productcode;product;price;weight;descr;category;thumbnail
20;SKU20;Black gloves;30;0.5;Fine quality gloves;Clothes/Gloves;gloves.gif

You want to add a new product - 'Red gloves' - by importing it into X-Cart. 'Red gloves' are practically the same with 'Black gloves', but these two products differ in color, and you want the product 'Red gloves' to have its own productid, SKU, product name and thumbnail picture. There are two ways of creating the product 'Red gloves' based on X-Cart product import functionality:

1. Add to the existing CSV file a line about Red gloves, then re-import the CSV file into X-Cart.

In this case you will have either to invent a new productid and SKU for the new product:

productid;productcode;product;price;weight;descr;category;thumbnail
20;SKU20;Black gloves;30;0.5;Fine quality gloves;Clothes/Gloves;gloves.gif
30;SKU30;Red gloves;30;0.5;Fine quality gloves;Clothes/Gloves;gloves1.gif

or to leave the :confirm bd 'productid' and/or 'productcode' fields for 'Red gloves' empty:

productid;productcode;product;price;weight;descr;category;thumbnail
20;SKU20;Black gloves;30;0.5;Fine quality gloves;Clothes/Gloves;gloves.gif
;;Red gloves;30;0.5;Fine quality gloves;Clothes/Gloves;gloves1.gif

If you leave the first one or two fields for the new product empty, their values will be generated automatically when the new product is added to the database.

2. Create a CSV file containing information about just one product - 'Red gloves'. Do not use the fields 'productid' and 'productcode' at all:

product;price;weight;descr;category;thumbnail
Red gloves;30;0.5;Fine quality gloves;Clothes/Gloves;gloves1.gif

The necessary productid and SKU for 'Red gloves' will be created automatically.

Notes

on product categories import

You can have more than one category column in your CSV file - one for the product main category and the necessary number of columns for the additional categories (if any). For example, you are going to import a product which is a book and which you want to be located in two categories: 'Books/Internet' (main) and 'Books' (additional). If you have chosen to use /// as the category path separator, your CSV file should have two category columns:

category;category

Books///Internet;Books
Tip: If your CSV file contains several category fields, you can use the 'Get column names from the first row of CSV file' functionality to let X-Cart know the order in which fields are being imported.

on product prices import

You can have more than one price column in your CSV file - one for the basic product price and the necessary number of columns for the wholesale prices (if any). Basic product price is entered into the field 'as is' (If, for example, a product costs $0.99, you only need to enter 0.99 into the price field). For the import of membership-specific prices use the following format:

Membership:Quantity:Price

For example, if you want to describe a wholesale price according to which Wholesale' customers can purchase 5 or more items of some product at $0.89 per item, the contents of one of your price fields must look as follows: 'Wholesale:5:0.89'

If a price is applied to all membership groups, the 'Membership' part can be omitted: '5:0.89' means that any user can purchase 5 or more items of the product at $0.89 per item.

Tip: If your CSV file contains several price fields, you can use the 'Get column names from the first row of CSV file' functionality to let X-Cart know the order in which fields are being imported.

on extra fields import

For example, you need to import the following extra fields:

  • UPC
  • ISBN
  • Artist
  • Publisher
  • Pages

The extra_fields field in the CSV file for import into X-Cart should be filled in as follows :

field=UPC&&&value=%VALUE%###
field=ISBN&&&value=%VALUE%###
field=Artist&&&value=%VALUE%###
field=Publisher&&&value=%VALUE%###
field=Pages&&&value=%VALUE%

where {%VALUE%} must be replaced with the actual field values.

on product options import

First of all, please note that if a product has product options defined, they are deleted before import.

For Modificator type options use the following product_options field format:

/*
<Class>
<Name></Name> 'Option class name

<Text></Text> 'Text description of the option class
<Type></Type> 'Option type (Y for Modificator)
<Avail></Avail> 'Availability, Y or N
<OrderBy></OrderBy> 'Sorting position of the class
<Options>

<Option>

=<ID></ID> 'Option variant ID
<Name></Name> 'Option variant name
<Avail></Avail> 'Availability, Y or N
<OrderBy></OrderBy> 'Sorting position of the option variant

<PriceModifier></PriceModifier> 'Price modifier value (positive or negative)
<ModifierType></ModifierType> 'Modifier type ($ or % for Modificator)
</Option>

...

</Options>

</Class>

...
<Exception>

<ID></ID> 'ID of exception component
...
</Exception>
...
<JScode></JScode> 'JavaScript validation code
*/

Example 1. Modificator type options.

Imp products2.gif'Product options' form

Imp products3.gif'Add/Update option' form


Information about the product options from Example 1 shown in the picture above can be entered into the product_options field as follows:

<Class>

<Name>Cover</Name>
<Text>Choose cover type</Text>
<Type>Y</Type>
<Avail>Y</Avail>
<OrderBy>0</OrderBy>

<Options>
<Option>
<ID>10</ID>
<Name>Soft</Name>
<Avail>Y</Avail>
<OrderBy>10</OrderBy>

<PriceModifier>0.00</PriceModifier>
<ModifierType>$</ModifierType>
</Option>
<Option>
<ID>11</ID>
<Name>Hard</Name>

<Avail>Y</Avail>
<OrderBy>20</OrderBy>
<PriceModifier>5.00</PriceModifier>
<ModifierType>$</ModifierType>
</Option>

</Options>
</Class>
<JScode></JScode>

For Variant type options use the following product_options field format:

/*
<Class>

<Name></Name> 'Option class name
<Text></Text> 'Text description of the option class
<Type></Type> 'Option type (empty for Variant)
<Avail></Avail> 'Availability, Y or N
<OrderBy></OrderBy> 'Sorting position of the class

<Options>
<Option>
<ID></ID> 'Option variant ID
<Name></Name> 'Option variant name
<Avail></Avail> 'Availability, Y or N
<OrderBy></OrderBy> 'Sorting position of the option variant

<PriceModifier></PriceModifier> 'Price modifier value (0.00 for Variant)
<ModifierType></ModifierType> 'Modifier type (empty for Variant)
</Option>
...
</Options>
</Class>
...
<Exception>

<ID></ID> 'ID of exception component
...
</Exception>
...
<Variant>
<SKU></SKU> 'Product variant SKU
<Avail></Avail> 'Availability, Y or N

<Weight></Weight> 'Product variant weight
<Price></Price> 'Product variant price
<Items>
<ID></ID> 'ID of product variant component
...
</Items>
</Variant>

...
<JScode></JScode> 'JavaScript validation code
*/

Example 2. Variant type options.

Imp products4.gif'Product options' form

Imp products5.gif'Add/Update option' form (size)

Imp products6.gif'Add/Update option' form (color)

Imp products7.gif'Product variants' form

Information about the product options from Example 2 shown in the picture above can be entered into the product_options field as follows:

<Class>

<Name>Size</Name>
<Text>Choose size</Text>
<Type></Type>
<Avail>Y</Avail>
<OrderBy>10</OrderBy>

<Options>
<Option>
<ID>22</ID>
<Name>L</Name>
<Avail>Y</Avail>
<OrderBy>0</OrderBy>

<PriceModifier>0.00</PriceModifier>
<ModifierType></ModifierType>
</Option>
<Option>
<ID>23</ID>
<Name>XL</Name>

<Avail>Y</Avail>
<OrderBy>0</OrderBy>
<PriceModifier>0.00</PriceModifier>
<ModifierType></ModifierType>
</Option>

</Options>
</Class>
<Class>
<Name>Color</Name>
<Text>Choose color</Text>
<Type></Type>

<Avail>Y</Avail>
<OrderBy>20</OrderBy>
<Options>
<Option>
<ID>122</ID>
<Name>Red</Name>

<Avail>Y</Avail>
<OrderBy>0</OrderBy>
<PriceModifier>0.00</PriceModifier>
<ModifierType></ModifierType>
</Option>

<Option>
<ID>123</ID>
<Name>White</Name>
<Avail>Y</Avail>
<OrderBy>1</OrderBy>

<PriceModifier>0.00</PriceModifier>
<ModifierType></ModifierType>
</Option>
</Options>
</Class>
<Variant>
<SKU>SKU10</SKU>

<Avail>70</Avail>
<Weight>1</Weight>
<Price>59.99</Price>
<Items>
<ID>22</ID>

<ID>123</ID>
</Items>
</Variant>
<Variant>
<SKU>SKU100</SKU>
<Avail>100</Avail>

<Weight>1</Weight>
<Price>61.99</Price>
<Items>
<ID>23</ID>
<ID>123</ID>

</Items>
</Variant>
<Variant>
<SKU>SKU101</SKU>
<Avail>98</Avail>
<Weight>1</Weight>

<Price>59.99</Price>
<Items>
<ID>22</ID>
<ID>122</ID>
</Items>
</Variant>

<Variant>
<SKU>SKU102</SKU>
<Avail>36</Avail>
<Weight>1</Weight>
<Price>61.99</Price>

<Items>
<ID>23</ID>
<ID>122</ID>
</Items>
</Variant>
<JScode></JScode>

For Text field type options use the following product_options field format:

/*
<Class>
<Name></Name> 'Option class name
<Text></Text> 'Text description of the option class

<Type></Type> 'Option type (T for Text field)
<Avail></Avail> 'Availability, Y or N
<OrderBy></OrderBy> 'Sorting position of the class
<Options></Options> 'Options (Empty for Text field)
</Class>

<JScode></JScode> 'JavaScript validation code (Empty for Text field)
*/

Example 3. Text field type options.

Imp products8.gif'Product options' form

Imp products9.gif'Add/Update option' form

Information about the product options from Example 3 shown in the picture above can be entered into the product_options field as follows:

<Class>
<Name>Message</Name>

<Text>Enter your message</Text>
<Type>T</Type>
<Avail>Y</Avail>
<OrderBy>1</OrderBy>
<Options></Options>

</Class>
<JScode></JScode>

on applied taxes import

applied_taxes field can contain service names of taxes which must be applied to the product being imported. The service names must be delimited by &&&. For example, if the service names of the applied taxes are VAT and VAT17_5, the applied_taxes field should be filled in as follows:

VAT&&&VAT17_5
Tip: If you are still not sure what data format must be used for some field in the CSV file for import, use the provider/admin interface of your store to add a product with characteristics similar to those of the product(s) you want to import, then export the products and see what kind of format you've got for this field in the resulting CSV file.