X-Cart:CSV

From X-Cart 4 Classic
Jump to: navigation, search

General Notes on CSV Format

The file format used to import/export X-Cart store data is called CSV, where CSV stands for 'Comma Separated Values'. It is a popular delimited data format that nicely suits the purposes of exporting data from X-Cart database tables, editing the exported data in an external spreadsheet editor and re-importing the data back into X-Cart.

A CSV file is a text file where each line represents a separate data record. Each record consists of fields separated by a designated delimiter (a comma, a semicolon or a tabulation).

Being a text file, a file in the CSV format can be opened and edited with any decent text editor. However, editing large volumes of CSV-formatted data with a text editor is likely to turn out a frustrating experience, so you might prefer to use your favorite spreadsheet software application, like MS Excel, to arrange the contents of your CSV file as columns and rows. Arranging your store data as columns and rows in a spreadsheet software application is sure to make editing a whole lot easier, but please be aware that, depending on the spreadsheet application you use, you might need to readjust the cell format for some data types so the data is displayed properly. Before re-importing the data edited in an external spreadsheet program back into X-Cart, be prepared to check the CSV file for any extra spaces or punctuation marks that might have been inserted by the spreadsheet program.

Structure/Formatting of a CSV File for Use with X-Cart

Depending on your needs, a CSV file generated by X-Cart or intended for importing into X-Cart may contain data of one or more X-Cart data types (for example, categories, products, users, destination zones, etc). All types of X-Cart data imported/exported as CSV come as string or numeric values.

Note: Graphics (like category icons, product thumbnails, detailed product images) or other complex data objects (like digital media files for ESD products) do not get saved to the CSV file itself, they are just referenced by URLs or filepaths pointing to their location.

The principles of formatting are the same for both import and export CSV files:

  • Within the file, the data is grouped into sections according to the data type.
  • Each section has a title. A section title represents the name of the data type imported/exported in the section, written in capital letters and enclosed in square brackets. In data type names consisting of more than one word, the words are separated by underscores. For example, if you export your store's config data, information on users, user memberships and product extra fields, you will get sections titled [CONFIG], [USERS], [MEMBERSHIPS] and [EXTRA_FIELDS] respectively.

Exact section titles can be found in the chapters discussing the format of import/export sections for various types of data (See the section [import_export/csv_format_various_data_types.htm CSV Format for Various Data Types] of this guide).

  • Within any section in your import/export CSV file, the section title is followed by a header row - a row containing field names. Each field name serves as a column heading, is written in capital letters and is preceded by an exclamation mark. Below the header row goes the data imported/exported through the section. The data comes as a list of records, where each record begins on a new line and provides values for every field named in the header row. Both in the header row and in the data records, fields are separated with a delimiter. The delimiter may be a comma, a semicolon or a tabulation (The same delimiter is used throughout the file).

Example:

[ZONES] <- section title
!ZONEID;!ZONE;!COUNTRY;!STATE;!COUNTY;!CITY;!ADDRESS;!ZIP <- header row
1;New York;US;US_NY;;New Yo%;; <- data row


Names of import/export fields and information on the format of data that can be imported/exported through these fields can be found in the chapters discussing the format of import/export sections for various types of data (See the section [import_export/csv_format_various_data_types.htm CSV Format for Various Data Types] of this guide).

  • If a certain field contains the delimiter symbol as part of its value, the whole field is enclosed in double quotes ("). If a text field enclosed in double quotes contains another double quote symbol, that double quote symbol is doubled to be considered a symbol rather than the end of the string marking.

The following is a sample of a CSV file with a comma delimiter and double-quote text qualifier symbols enclosing the !DESCR field value (also note the duplicate double quote symbol after the double quote denoting inches):

[PRODUCTS]

!PRODUCTID,!PRODUCTCODE,!PRODUCT,!DESCR

182,SKU182,Hunting Knife w/sheath,"This 8 1/4 "" beauty will make a great addition to your knife collection or a great gift for that special person. A quality hunting knife with a heavy 4-inch blade and an overall length of 8.25-inches. Hand crafted from only the finest materials."

The same data will look like the example below if a semicolon delimiter is used (no double quotes around the !DESCR field, as well as no duplication of the "inches" double quote symbol):

[PRODUCTS]

!PRODUCTID;!PRODUCTCODE;!PRODUCT;!DESCR

182;SKU182;Hunting Knife w/sheath;This 8 1/4 " beauty will make a great addition to your knife collection or a great gift for that special person. A quality hunting knife with a heavy 4-inch blade and an overall length of 8.25-inches. Hand crafted from only the finest materials."

Fields may always be delimited with double quotes.

See also