X-Cart:CSV:Notes on importing

Typically, X-Cart's data import functionality is used for re-importing the data previously exported from an X-Cart store. Import of a CSV file generated by X-Cart usually goes without a hitch, provided that the format of the CSV file has not been corrupted after exporting. However, if, for some reason, you are importing a manually created CSV file or an X-Cart-generated CSV file that has been altered in some way, you need to be very careful: a file whose structure and/or format of data representation do not comply with X-Cart's requirements will not be imported or will be imported incorrectly causing damage to the data in the database.

For successful import, your data will need to be arranged in exactly the way that X-Cart will be able to "understand". In this guide, we tried to provide a comprehensive explanation of how different types of data need to be laid out in CSV format when being imported. However, because of the structural complexity of some data types (like product options), some of the explanations may still be confusing. For cases where the explanation of the import format provided by this guide does not seem clear enough, we recommend you use the user interface of your X-Cart store to manually enter into the database some sample data of the type you wish to import (for example, configure some product options using the appropriate page of the 'Product Management' section of X-Cart back-end), then export that data to a CSV file and study the format. Exporting existing data seems to be the easiest way to find out the import data format for your specific needs.

When preparing a CSV file for importing into X-Cart, be aware of the following considerations:

• X-Cart does not support importing of orders. You can export orders, but you cannot re-import them into X-Cart.
• Data pertaining to X-Cart modules and add-ons cannot be imported when the respective modules or add-ons are disabled or not installed. Before importing data for a module or add-on, ensure that the module/add-on is enabled.
• All the fields that can be imported through a certain import section are declared in the list of importable columns for the respective data type (See the table provided in the 'Import options' section of the 'Import data' dialog box). You should not expect X-Cart to be able to import any new fields (not declared in the list of importable fields for the section), unless your store has been customized in a way that enables it to import/export these fields.

• It is not necessary to import the complete set of fields importable through a certain section every time you import. Most data fields can be excluded from an import CSV file without any negative effect on X-Cart's ability to import the file.

Note: Two types of fields cannot be excluded from an import file: required fields and key fields. See an explanation further in this chapter.

Removal from a CSV file of fields that do not need to be updated as a result of importing is strongly recommended: it makes the CSV file more compact and helps you avoid accidental updating of the data that does not need to be updated. To remove a field from a CSV file means to remove the respective column completely, including the column heading, any values contained in that field in all the data records and all the instances of the delimiter symbol pertaining to the field.

For example, you have two products at your store:

Product name:	product1	product2
ProductId:	17560	17561
SKU:	SKU1	SKU11
Weight (lbs):	1	1
Short description:	Description text for product1	Description text for product2
Price ($):	15	20
Other product fields:	not defined	not defined

You wish to update the prices for these products to $14 and $19 respectively and to change the productcode for the second product from SKU11 to SKU2.

The said goal can be achieved by importing a CSV file arranged like this:

[PRODUCTS] !PRODUCTID;!PRODUCTCODE;!PRODUCT;!WEIGHT;!LIST_PRICE;!DESCR;!FULLDESCR;!KEYWORDS;!AVAIL;!RATING;!FORSALE;!SHIPPING_FREIGHT;!FREE_SHIPPING;!DISCOUNT_AVAIL;!MIN_AMOUNT;!DIM_X;!DIM_Y;!DIM_Z;!LOW_AVAIL_LIMIT;!FREE_TAX;!CATEGORYID;!CATEGORY;!MEMBERSHIP;!PRICE;!THUMBNAIL;!IMAGE;!TAXES;!ADD_DATE;!VIEWS_STATS;!SALES_STATS;!DEL_STATS;!RETURN_TIME;!MANUFACTURERID;!MANUFACTURER;!PRODUCT_TYPE 17560;SKU1;product1;1;;Description text for product1;;;;;;;;;;;;;;;;;;14;;;;;;;;;;; 17561;SKU2;product2;1;;Description text for product2;;;;;;;;;;;;;;;;;;19;;;;;;;;;;;

or like this:

[PRODUCTS] !PRODUCTID;!PRODUCTCODE;!PRICE 17560;SKU1;14 17561;SKU2;19

In our example, both the files provide the same result if imported. The principal difference is that the first file updates all the fields importable through the [PRODUCTS] section, while the second, more compact CSV file updates only the fields !PRODUCTCODE and !PRICE. The advantage of the second file over the first one is that it can be used safely regardless of whether the fields !PRODUCT, !WEIGHT, !LIST_PRICE, !DESCR, !FULLDESCR, !KEYWORDS, etc contain any data or not. (The first file is not safe because it will overwrite any data in the fields !FULLDESCR, !KEYWORDS, !AVAIL, !RATING, !FORSALE, !SHIPPING_FREIGHT, !FREE_SHIPPING, !DISCOUNT_AVAIL, !MIN_AMOUNT, !DIM_X, !DIM_Y, !DIM_Z, !LOW_AVAIL_LIMIT, !FREE_TAX, !CATEGORYID, !CATEGORY, !MEMBERSHIP, !THUMBNAIL, !IMAGE, !TAXES, !ADD_DATE, !VIEWS_STATS, !SALES_STATS, !DEL_STATS, !RETURN_TIME, !MANUFACTURERID, !MANUFACTURER and !PRODUCT_TYPE with empty values, removing all the data that might have been stored in these fields).

• Some fields in some import sections have the status required. Required fields are fields that contain indispensable information and, thus, cannot be excluded from an import CSV file. To find out which fields are required for a specific data type, refer to the 'Import options' section of the 'Import data' dialog box in your X-Cart back-end. In the table providing lists of importable fields for various data types, all required fields are marked bold. For example, a required field for [CATEGORIES] section is !CATEGORY (There is obviously no point in importing a category without a name.) To be imported successfully, a data section needs to contain all the required columns that it is supposed to contain, and the fields in the required columns need to contain valid values for each data record being imported.

• Besides required fields, there is another set of fields that plays an important role in the process of importing: key fields. Key fields are not marked in any way in the 'Import options' section, but can be easily remembered, as there are just five of them:

•  !PRODUCTID,
•  !PRODUCTCODE,
•  !PRODUCT,
•  !CATEGORYID,
•  !CATEGORY.

The fields !PRODUCTID, !PRODUCTCODE and !PRODUCT serve as key fields for all the sections in which they occur, including [PRODUCTS], [FEATURED_PRODUCTS], [DETAILED_IMAGES], [WHOLESALE_PRICES], [DISCOUNT_COUPONS], [PRODUCTS_EXTRA_FIELD_VALUES], [PRODUCT_OPTIONS], [MULTILANGUAGE_PRODUCT_OPTIONS], [MULTILANGUAGE_PRODUCT_OPTION_VALUES], [PRODUCT_VARIANTS], [PRODUCT_OPTION_EXCEPTIONS], [PRODUCT_OPTION_JSCRIPT], [CUSTOMER_REVIEWS] and [MULTILANGUAGE_PRODUCTS].

The fields !CATEGORYID and !CATEGORY serve as key fields for just one section: [MULTILANGUAGE_CATEGORIES]. (In the section [CATEGORIES], the required field !CATEGORY is the only key field).

The main function of key fields is to provide data record identifiers, telling X-Cart, for which product or category each specific data record is being imported. For example, you cannot import a product price or a product description without telling X-Cart, what product they are intended for; so, before importing a product price or description, you need to provide an ID, a productcode (SKU) or a product name of the product that is the owner of the price/description. From this viewpoint, the presence of key fields in a CSV file is required; however, you are not required to have all the key fields for every data record at the same time. For example, to identify a product, you often do not need to use all the three fields (!PRODUCTID, !PRODUCTCODE and !PRODUCT) - just two fields or even a single field may be enough. In any case, at least one key field per data record must be present and have a value.

The following are two examples of correct CSV files:

[PRODUCTS]
PRODUCTID;!PRODUCTCODE;!PRODUCT
17560;;product1
;;product2

[PRODUCTS]
PRODUCTID;!PRODUCTCODE;!PRODUCT
17560;;
17561;;

The following is an example of an incorrect file:

[PRODUCTS]
!PRODUCTID;;!PRODUCT
17560;;product1
17561;;product2

This file will cause an error, because the second semicolon delimiter after !PRODUCTID (probably left after the removal of the !PRODUCTCODE field) creates an empty column heading. Empty column headings are not allowed in import CSV files, no matter whether it is a key field or not.

• Depending on your needs, the data being imported can update the data in the database or simply be added to it. The result depends on the import options used when importing, on the nature of the data being imported and on the order in which the data is arranged in the file being imported.

As to import options, the table with importable data types located in the 'Import options' section of the 'Import data' dialog box provides check boxes allowing you to specify, whether you wish the data of a specific data type to be dropped from the database before import. If you select a check box, all the data of the respective data type will be removed from the database and replaced by the data imported from the CSV file. If, however, you leave the check box unselected, the data from the import CSV file will be added "on top" of the data stored in the database. This means that, if the values of all the key fields in a certain data record contained in the import CSV file coincide with the values of the key fields of a certain data record in the database, the record in the database will be overwritten by the record from the CSV file; if, however, the record identifiers contained in the key fields of a record in the import CSV file are found to have no correspondence with any records of the same type in the database, the record in the CSV file will be treated as completely new and will simply be added to the database.

Example 1:

You have one product in the database:

Product name:	product1
ProductId:	17560
SKU:	SKU1
Weight (lbs):	1
Short description:	Description text for product1
Price ($):	15
Other product fields:	not defined

You import the following CSV file:

[PRODUCTS]
!PRODUCTID;!PRODUCTCODE;!PRODUCT;!WEIGHT;!DESCR;!PRICE
17561;SKU2;product2;1;Description text for product2;17

If the check box telling X-Cart to drop all data of the type "PRODUCTS" is not selected at the time of importing, you get two products - "product1" (the product you had in the database before importing) and "product2" (a new product created by importing):

Product name:	product1	product2
ProductId:	17560	17561
SKU:	SKU1	SKU2
Weight (lbs):	1	1
Short description:	Description text for product1	Description text for product2
Price ($):	15	17
Other product fields:	not defined	not defined

If, however, the "PRODUCTS" check box is selected, you get a single product in your database, "product2" (17561, SKU2), because the product "product1" has been removed before importing.

In both cases, the product "product2" simply gets added to the database.

Example 2:

If you have one product in your database ("product1" from the previous example), and you import a CSV file like this:

[PRODUCTS]
!PRODUCTID;!PRODUCTCODE;!PRODUCT;!WEIGHT;!DESCR;!PRICE
17561;SKU1;product1;1;Description text for product1;15

(provided the check box for "PRODUCTS" is unselected) you also get two products:

Product name:	product1	product1
ProductId:	17560	17561
SKU:	SKU1	SKU
Weight (lbs):	1	1
Short description:	Description text for product1	Description text for product1
Price ($):	15	15
Other product fields:	not defined	not defined

You can see that this time both the products are called "product1". Although it may seem somewhat strange, two products with the same name here is not a bug; on the opposite, it is a good demonstration of X-Cart's import mechanism working correctly.

Let us take a closer look at what causes such unusual results. Despite that the values of all the fields (except the field !PRODUCTID) are the same for both the product in the database and the product from the import file, the product from the import file does not replace the product in the database. The trick is that the key fields !PRODUCTID, !PRODUCTCODE and !PRODUCT have different priority for product identification. During an import procedure, the named three key fields are processed successively (in the order !PRODUCTID -> !PRODUCTCODE -> !PRODUCT). As a result, when all the three key fields are present in the import file, the importing goes as follows:

1. X-Cart reads a product's ProductId in the CSV file being imported (which corresponds to the !PRODUCTID field of the respective product record) and checks whether it coincides with any of the ProductId's of the products in the database. If an equal ProductId is found in the database, X-Cart assumes that the product being imported already exists in the database and simply needs to be updated. Regardless of what is contained in the other two key fields of the product record in the import CSV file (!PRODUCTCODE and !PRODUCT), X-Cart overwrites the product record in the database using the data from the import CSV file. If, however, X-Cart finds the ProductId from the CSV file to have no equivalents in the database, it assumes, the product record from the CSV file needs to be added to the database as a new product.
2. In the latter case, X-Cart accepts the ProductId from the import file as the ProductId of the new product and proceeds to the next field - !PRODUCTCODE - to ensure that the Product code (SKU) of the product being imported is not already used for some other product in the database (in X-Cart PRO - for some other product of the same provider). If the Product code (SKU) of the product being imported is found to be unique, X-Cart uses it to overwrite the Product code (SKU) in the database. If, however, the database is found to contain a Product code (SKU) equal to the Product code (SKU) of the product being imported, X-Cart discards the Product code (SKU) being imported and generates a new unique Product code (SKU), which it assigns to the new product in the database.
3. After that, X-Cart moves on to the !PRODUCT field of the import file. Unlike Product codes, Product names in X-Cart do not have to be unique, so X-Cart simply overwrites the field in the database by the value being imported - without even checking the Product name for uniqueness.

In full accordance with the procedure described above, in our example, the new product in the database has been assigned the ProductId "17561", the Product code "SKU1" contained in the import file has been deemed non-unique and replaced by the automatically generated value "SKU", and the Product name "product1" has simply been imported "as is".

Example 3:

If you have not got any products in your database, and you import a CSV file like this:

[PRODUCTS]
!PRODUCTID;!PRODUCTCODE;!PRODUCT;!WEIGHT;!DESCR
17560;SKU1;product1;10;Description text for product1
17560;SKU2;product2;;Description text for product2

you get just one product:

Product name:	product2
ProductId:	17560
SKU:	SKU2
Weight (lbs):	not defined
Short description:	Description text for product2
Other product fields:	not defined

The result is due to that, after importing the first product record (product1-17560-SKU1), X-Cart checks the !PRODUCTID field of the second product record (product2-17560-SKU2) for uniqueness and finds that the ProductId 17560 is already contained in the database; after that, it simply overwrites the first product record with the second one.

Example 4:

If you have not got any products in your database, and you import a CSV file like this:

[PRODUCTS]
!PRODUCTID;!PRODUCTCODE;!PRODUCT;!WEIGHT;!DESCR
17560;SKU1;product1;10;Description text for product1
;SKU1;product2;;Description text for product2

you get two products:

Product name:	product1	product2
ProductId:	17560	17561
SKU:	SKU1	SKU11
Weight (lbs):	10	not defined
Short description:	Description text for product1	Description text for product2
Other product fields:	not defined	not defined

X-Cart imports the first product record as usual; then, as it begins importing the second product record, it becomes evident that a value for the !PRODUCTID field in the second record is missing. The absence of a value in the !PRODUCTID field does not harm the process of importing, as there are still two other key fields that do have values. However, X-Cart cannot just ignore the absence of a ProductId in the second product record, as the !PRODUCTID column is present in the CSV file. As a result, X-Cart generates a new ProductId (17561) and uses it as the ProductId for the second product record. Then, X-Cart proceeds to the next field being imported (!PRODUCTCODE) and checks it for uniqueness. As the code "SKU1" has just been imported into the database with the first product record, X-Cart rejects the value "SKU1" that we are trying to import with the second product record and, again, generates a new value: "SKU11". This value gets written to the product's SKU field in the database, so thus we get the results cited above.

• The columns within an import section may go in any order. The following two CSV files are identical:

[PRODUCTS]
!PRODUCTID;!PRODUCTCODE;!PRODUCT;!WEIGHT
10;SKU10;productX;30
20;SKU20;productY;40

[PRODUCTS]
!WEIGHT;!PRODUCTID;!PRODUCT;!PRODUCTCODE
30;10;productX;SKU10
40;20;productY;SKU20

• The presence of empty lines in a CSV file does not have any negative effect on X-Cart's ability to read/import the file, no matter whether there is just one line or multiple empty lines that go together, and whether the empty line(s) appear between any two sections or within a section. However, you should understand that a sequence of empty fields is not an empty line and will cause an error.

For example, you will be able to import the following file:

[PRODUCTS]
!PRODUCTID;!PRODUCTCODE;!PRODUCT
17560;;product1
17561;;product2

without errors, but a file like this:

[PRODUCTS]
!PRODUCTID;!PRODUCTCODE;!PRODUCT
17560;;product1
;;

will cause an error because the second import line represents a sequence of empty fields (not a single key field value is present).

• Importing of data from a CSV file goes line by line, from the top of the CSV file to the bottom, in the same order in which the lines are arranged in the file being imported. The order of importing may affect the order in which the data is displayed in the user interface of your store, so you should consider this when importing data that should be sorted in a certain way.

For example, if you import a product option group "Size" like this:

[PRODUCT_OPTIONS]

!PRODUCT;!CLASS;!TYPE;!DESCR;!OPTION

product1;Size;;Choose size;M

product1;Size;;Choose size;S

product1;Size;;Choose size;XL

product1;Size;;Choose size;L

individual option values will appear in the user interface in the order "M, S, XL, L", which may not be convenient to handle if you are more used to the traditional order "S, M, L, XL".

Note that the order of lines in the import CSV file would not matter if you imported the above option values specifying the position of each value in the !OPTION_ORDERBY field like so:

[PRODUCT_OPTIONS]

!PRODUCT;!CLASS;!TYPE;!DESCR;!OPTION;!OPTION_ORDERBY

product1;Size;;Choose size;M;20

product1;Size;;Choose size;S;10

product1;Size;;Choose size;XL;40

product1;Size;;Choose size;L;30

• Individual sections in your import CSV file may go in any order, as long as the following condition is satisfied: sections containing data dependent on other data types may be imported no sooner than the data on which they are dependent appears in the database. For example, if you wish to import product options, product extra field values, detailed product images or any other type of data associated with products, you first need to ensure that the products for which they are intended are already in the database or are being imported by the section [PRODUCTS] earlier in the same file.
• Data import procedure alters the formatting of text pieces that contain leading and/or trailing space characters adjacent to field separators: any space characters adjacent to field separators are dropped. If you import a file like this:

[PRODUCTS]

!PRODUCT;!DESCR;!PRICE

product1;    Description text for product1    ;15

the space characters around the text "Description text for product1" will be removed, so if you look at the product's 'Short description' field in the store's user interface after import, there will be just the text "Description text for product1", without the spaces.

This means that if you want space characters at the beginning or at the end of a certain field value, you need to use the   entity in the place of every space character that needs to be inserted.

• In formatted text pieces (product/category descriptions and language variables) being imported, newlines need to be represented by <EOL> (end of line) service tags.
• Sections with multilanguage data may be imported from a separate file or, if the language charset of the multilanguage data that needs to be imported is the same as the charset of the default language, from the same file from which all the data in the default language is imported (In other words, putting multilanguage data in a separate file - the way it is done in X-Cart's data export procedure - is not necessary, as long as you can write data for different languages using the same charset in one file).