Importing products from a CSV (Excel) file

For fast import of multiple products and categories to your online store, create a text file in CSV format using popular spreadsheet editors; e.g., OpenOffice/LibreOffice Calc, Microsoft Excel, iWork, etc.

Download a sample CSV file containing examples from this article.

To obtain a correctly formed example of a CSV file, manually add some categories and products and then export them to a file. Use the exported example to create your own CSV files.

CSV file importing procedure

  1. Create a CSV file.
  2. Upload the file in section Import / Export → Import products from CSV.
  3. Select category, product, and SKU properties to correctly match file columns. Check that automatic column detection has worked correctly.
  4. Select identification columns for categories/products and SKUs.
  5. If necessary, edit default advanced import settings.
  6. Click the button at the bottom to run the automatic file contents review.
  7. Complete the file import.
First test the import with a small number of rows in a file. Proceed to a larger number of rows once you have learned how to run the import correctly with a small file. This way you will be able to avoid major mistakes, which may be hard to fix.

1. Create a CSV file

The first row in the file must contain column names without empty cells between any of them. Enter product and category information in the 2nd and further rows.

Any row can represent either a category, or a product, or an SKU

How exactly a row will be imported can be defined in one of the two ways:

  • either automatically, by the row contents and import settings,
  • or manually, by the values in the “Row type” column.
During the file contents review and import routines, not all columns are taken into account but only those for which certain catalog properties have been selected on the import page.
Manual detection of a row type works as of version 8.19. This is a recommended method to detect a row type.
Category

Automatic row type detection

If values are contained only in the columns intended for categories then a row is imported as a category.

Manual row type detection

If the Row type column contains value “category” or “dynamic_category” then a row is imported as a category.

The “dynamic_category” value can be used only to update the properties of existing dynamic categories. You cannot create new fully-functional dynamic categories using the CSV file import because the import functionality does not allow specifying product filtering conditions for dynamic categories.
Product or SKU

Automatic row type detection

If at least one column intended for products or SKUs contains a value then a row is imported as a product or as an SKU.

If several successive rows contain values in columns intended for SKUs and contain equal values in a product identification column then the first of such rows is imported as a product and its first SKU and the remaining rows with group are imported as additional product SKUs. If compared with manual row type detection, the first row is imported as “product_variant” and the remaining rows are imported as “variant”.

In other cases product rows are imported as products.

Manual row type detection

  • If the Row type column contains value “product” then a row is imported as a product. SKU properties are ignored.
  • If the Row type column contains value “variant” then a row is imported as an SKU. Product properties are used only for identification to determine which product SKUs belong to.
  • If the Row type column contains value “product_variant” then:
    • product properties re saved as a product,
    • SKU properties are saved as an SKU,
    • feature values are saved
      • to SKUs if they are specified in special braces <{}>,
      • to a product otherwise.

File columns

Column titles do not have to be exactly as shown in this article. During the import, you can manually select which catalog property must match each of the columns.

If you are importing multiple images per product then all columns containing images must have equal titles. This is also valid for columns containing image descriptions. Each column with a image descriptions must directly follow the corresponding column with images.

Values from different columns can be imported into categories, products, and SKUs as shown in the table below.

CSV file column Description Example Where is imported
Row type

Defines how a row must be imported—as a category, product, or SKU:

  • category
  • dynamic_category
  • product
  • variant (SKU)
  • product_variant (product and SKU if both product and SKU properties are contained in a row)
category
dynamic_category
product
variant
product_variant
Category and product properties
Name Category or product name. Apple iPhone 5S category, product
Storefront link Individual portion of the URL of the main category or product page. iphone-5s category, product
Title This text will be added into HTML tag TITLE of the category or product page. Apple iPhone 5S category, product
META keywords This text will be added into META tag keywords of the category or product page. Apple, iPhone, mobile phone category, product
META description This text will be added into META tag description of the category or product page. iOS-based smartphone
multi-touch screen (capacitive)
screen size 4"...
category, product
Description Full product or category description, which may contain any HTML tags. <h3>Forward Thinking.</h3><div>iPhone 5s is purposefully imagined. Meticulously considered. Precision crafted... category, product
Status Whether product or category must be available via common navigation. Acceptable values: 1 (visible), 0 (hidden), -1 (unpublished). 1 category, product
Currency ISO3 code of the currency, in which product price is expressed. If not specified, then product price is imported in the default currency selected in store settings. USD product
Product type Name of product type which must be assigned to the product. If a new product type is specified which is not defined in store settings, then this new product type will be created only if the user importing a CSV file has sufficient access rights for that. Mobile phones product
Summary Brief description of a product to be displayed in various product listings such as categories, product sets, search results, etc. This field may contain HTML code. iPhone has always been forward thinking. And that’s exactly what iPhone 5s is now. 64-bit mobile architecture is pioneering... product
Badge One of the standard badge identifiers (new, bestseller, lowprice) or custom badge HTML code. new product
Tags List of tags associated with a product, comma-separated. iPhone,mobile phone,Apple product
Taxable Name of tax rule to be applied to a product. The specified tax rule must be defined in store settings screen “SettingsTaxes”. VAT product
Product images

Image URL or file name. If a URL is specified, then the image will be retrieved from that address and saved on the server. If a file name is specified, then the image file will be copied from the directory specified in import settings.

If more than one image must be imported for a product, create additional columns with the same name.

iphone-5s.jpg product
Product image description Description of the image specified in previous column. Front view. product
Product images Another product image. iphone-5s-back.jpg product
Product image description Another product image description. Back view. product
Size example of a product feature

Product feature:

  • Single value. E.g., string or number. If there is a measure unit in the value, then it must be specified with a whitespace character after the actual value. Example: 12 m.
  • Selection from multiple values. A drop-down list or a list of checkboxes. Values must be specified inside curly braces, separated by a comma. If one of the values contains a comma, then it must be specified inside double quotation marks. Example: {"38,500","38,700","38,900"}.
    The first of these values will be set as the default choice for features of type “select” (drop-down list).
  • Auto-generation of multiple SKUs from feature values. Feature values used for SKUs generation must be specified inside special double braces <{...}>. For example, if a product must be available for ordering via the choice of the “Size” feature values, then these value must be specified in a CSV file in this form: <{M,L,XL,XXL}>.
  • SKU selection by feature values. Specify special value <{}> for SKU import mode “Plain SKU list with selection by feature values”.
<{16Gb,32Gb,64Gb}> product
Product variety selection

One of the values for product setting Product variety selection in the storefront:

  • 1 — by SKU name,
  • 2 — by feature values.
product
Stock quantity unit Stock quantity unit code
only for the PREMIUM license
728 product
Base quantity unit Base quantity unit code
only for the PREMIUM license
055: numeric code if a base quantity unit is set in product properties
Not specified: if you need to disable the selection of a base quantity unit different from the product’s stock quantity unit
product
Stock to base quantity units ratio Stock to base quantity units ratio
only for the PREMIUM license
0.8: numeric value if the product’s base quantity unit differs from its stock quantity unit product
Add-to-cart step Product’s add-to-cart step value
only for the PREMIUM license
10 product
Quantity adjustment value via “+/-” buttons Product’s quantity adjustment value via “+/-” buttons
only for the PREMIUM license
2 product
Minimum orderable quantity Product’s minimum orderable quantity
only for the PREMIUM license
4 product
SKU properties
SKU Internal SKU code.
If a product has only one SKU, then you may nod need to include this column in your CSV file.
iphone-5s-white SKU
SKU name The name of an SKU displayed in the storefront.
If a product has only one SKU, then you may nod need to include this column in your CSV file.
White, 32GB SKU
Available for purchase Specify 0 in this column if you want to temporarily make an SKU not available for ordering. The SKU will remain visible in the storefront, but customers will not be able to add it to the shopping cart. To make an SKU available for ordering again, import 1 in this column. 1 SKU
Price SKU price. The price currency is defined by the value of the “Currency” field. 649 SKU
Compare at price “Compare at” price expressed in the currency specified in the “Currency” field. 749 SKU
Purchase price Purchase price expressed in the currency specified in the “Currency” field. 590 SKU
In stock Number of SKU items available in stock specified as an integer value. Ue this column to import SKU stock values, if you do not have stocks defined in your store settings or if you have only 1 stock set up. 24 SKU
In stock @stock1 Number of SKU items available in stock named “stock1”. If you have multiple stocks set up in your store settings, then, instead of one “In stock” column, create several columns, each for one of your stocks. To facilitate the automatic detection of CSV file columns during import, write column titles as shown in this example, with the @ character (without a white space) preceding the stock name. Add a separate column for each stock to your file. 18 SKU
Size example of an SKU feature

SKU feature:

  • Single value. E.g., string or number. If there is a measure unit in the value, then it must be specified with a whitespace character after the actual value. Example: 12 m.
  • Value for product variety selection in the storefront. Example for the Size feature: <{M}>.
SKU
Primary SKU Flag of the SKU which must become the primary one within a product. Only one SKU per product can be marked as primary. 1 SKU
Product images URL or name of a product image file which must be selected in this SKU’s properties. iphone-5s.jpg SKU
Stock to base quantity units ratio Product variant’s stock to base quantity units ratio
only for the PREMIUM license
0.8: numeric value if the variant’s base quantity unit differs from its stock quantity unit SKU
Quantity adjustment value via “+/-” buttons Product variant’s quantity adjustment value via “+/-” buttons
only for the PREMIUM license
2 SKU
Minimum orderable quantity Product variant’s minimum orderable quantity
only for the PREMIUM license
4 SKU

Categories and subcategories

By default, all imported categories are created at the same (highest) hierarchy level. If you need to place some categories at a lower level in the catalog, then add an exclamation mark before a category name; e.g.,:

A subcategory’s row must be placed below the row of its parent category and the rows of the parent category’s products.

Subcategories with more exclamation marks are nested inside categories with fewer exclamation marks.

Name Price
Mobile phones
!Apple

To create a new category to an even lower hierarchy level, add more exclamation marks. If further categories must be placed again at a higher level, reduce the number of exclamation marks before their names:

Name Price
Mobile phones
!Nokia
!!Series 60
!Apple

Adding products to categories

Newly imported products specified after category rows will be created inside those specific categories. Assignment of categories to products occurs only for newly imported products. When information of already existing products is updated via a CSV file, their category assignment will not be changed.

Example of adding products to various categories:

Name Price
Mobile phones
Apple iPhone 5S 649
!Sale
HTC One 599

In the above example, a product named “Apple iPhone 5S” will be created in category “Mobile phones”, and product “HTC One” inside subcategory “Sale”.

Adding a product to multiple categories

You can import a product into multiple categories. To do so, copy the product line under several category lines.

Example
Name Price
Category 1
Product 100
Category 2
Product 100

You can assign products only to static categories. To make a dynamic category display a different set of products, change that category’s settings in the store backend.

Importing products and SKUs

Product with 1 SKU

Automatic row type detection

Add 1 row to a file—with both product and SKU properties.

Manual row type detection

Add 2 rows:

  • A row with product properties. Row type column must contain value “product”.
  • A row with SKU properties. Row type column must contain value “product”. The product identification column must contain the same value as in the product row.

Product with multiple SKUs

Multiple SKUs can be imported in one of these three modes;

  1. Plain SKU list
  2. Plain SKU list with selection by feature values
  3. Automatic generation of SKUs from feature values

Select the mode which is most convenient for you to work with. For instance, the one that mostly resembles CSV files of your supplier.

Mode 1: Plain SKU list

Add several rows to a file:

  • A row with product properties.
  • Rows with SKU properties. The product identification column must contain the same values as in the product row. In the SKU identification column non-repeating values must be specified.

A group of rows containing data of a product and its SKUs is imported as follows:

  1. Product data from a product row are imported as a product.
  2. SKU data from a product row:
    • if automatic row type detection is used or “product_variant” value is contained in the Row type column then SKU data are imported as the first product SKU,
    • if manual row type detection is used then SKU data are ignored.
  3. Product data from SKU rows are not imported and are used only for the linking of SKUs to products.
  4. SKU data from SKU rows:
    • if automatic row type detection is used then SKU data are imported as the second and subsequent product SKUs,
    • if manual row type detection is used or “product_variant” value is contained in the Row type column then SKU data are imported as the first and subsequent product SKUs.

Examples

Automatic row type detection

Name SKU Price
Jet Snowboard Boots jet-6-white 156
Brave Jacket jet-7.5-black 157
Brave Jacket jet-8-white 160

Manual row type detection

Row type Name SKU Price
product Jet Snowboard Boots
variant Jet Snowboard Boots jet-6-white 156
variant Jet Snowboard Boots jet-7.5-black 157
variant Jet Snowboard Boots jet-8-white 160
Mode 2. Plain SKU list with selection by feature values


This mode is used to support the Features used for product variety selection in the storefront setting in the product editor. This mode requires the use of the Row type column.

In this mode, only SKUs specified in a CSV file are imported to the catalog.

There are 2 import options for this mode:

  • product row + SKUs rows,
  • only SKUs rows.

Import option “product row + SKUs rows”

  1. Add a row with product data. In the columns with features which must be used for the selection of product varieties in the storefront specify special value <{}>.
  2. Add SKUs data rows. In the same feature columns specify their single values which must correspond to each of the SKUs.

Example

Row type Name SKU name Price Color Memory
product Apple iPhone 5S <{}> <{}>
variant Apple iPhone 5S gray, 32 GB 649 gray 32 GB
variant Apple iPhone 5S gold, 64 GB 649 gold 64 GB

Import option “only SKUs rows”

Add rows containing both product data and SKU data. In the Row type column, specify value “variant”. In the columns with feature values, specify single feature values, corresponding to each of the SKU, within special braces <{...}>.

Example

Row type Name SKU name Price Color Memory
variant Apple iPhone 5S gray, 32 GB 649 <{gray}> <{32 GB}>
variant Apple iPhone 5S gold, 64 GB 649 <{gold}> <{64 GB}>
Mode 3: Automatic generation of SKUs from feature values

As opposed to mode “2. Plain SKU list with selection by feature values”, this mode allows you to automatically generate multiple SKUs from 1 product row containing several feature values. For each combination of feature values, a new SKU is generated if it does not exist before the import.

Specify feature values within special braces <{...}>. Only features with the format “Multiple values selection from a list” are supported in this mode.

As the SKU identification column, select the one with SKU names.

Name Price Available for purchase In stock Color
Apple iPhone 5S 649 1 200 <{gray,silver,gold}>

SKU-specific properties specified in such a row will be equally distributed among all generated SKUs. In the above example, the “Available for purchase” field value will be assigned and price 36990 will be set for all generated SKUs.

In stock value can be imported in different methods for automatically generated SKUs. You can select the desired method in import settings:

  • Equally distribute stock levels among all SKUs of this product (default choice).
  • Set provided stock count for all imported SKUs.
  • Don’t import stock information for automatically generated SKUs.

Should you need to generate SKUs based on the values of multiple features, then you need to specify their values in the same way. In the following example 6 SKUs will be generated, for all possible combinations of feature values specified inside special double braces <{}>.

Name Price Color Built-in memory capacity
Apple iPhone 5S 649 <{gray,silver,gold}> <{16GB,32GB,64GB}>

When it is necessary to specify individual values of price, availability for purchase, and stock count for automatically generated SKUs, they must be specified in separate rows like for product selling mode 1. In these rows, the SKU identification column must contain the feature values of each SKU, separated by comma & space, and the same values also specified in the corresponding features' columns as shown below.

Name SKU name Price In stock Color Built-in memory capacity
Apple iPhone 5S 649 <{gray,silver,gold}> <{16GB,32GB,64GB}>
Apple iPhone 5S Gray, 32GB 749 12 gray 32GB
Apple iPhone 5S Golden, 64GB 849 34 gold 64GB

When importing SKUs in mode 2 "Feature value selection", for SKU identification, select the column containing SKU names.

2. File upload

To import a CSV file, open backend section “Import/Export → Import from CSV”.

Select the value delimiter used in your file. Default choice is the semicolon (;).

Select the file encoding. It is usually UTF-8 if your file created with OpenOffice or LibreOffice, or might be a national character encoding if the file was created with Microsoft Excel.

If you are using special or national characters (e.g., ©, ±, ö, and others) in your product names and descriptions, then it is recommended to save a CSV file using international encoding UTF-8 to avoid the loss of such characters.

Browse the file on your computer to upload it. After the upload is completed, all columns found in your CSV file will be displayed as a table.

When importing a large CSV file, it may be convenient to compress it into an archive before uploading. Supported archive formats are ZIP and GZ.

After a file has been uploaded, a preview table showing all file columns will appear on the page.


3. Select column associations

Shop-Script will attempt to “guess” which file columns with which properties should be associated. Manually adjust the associations where necessary.

4. Select identification columns

Select the category/product/SKU properties by which you want the import script to identify catalog entries.

Identification is very important as it “tells” the import script how categories, products, and their SKUs listed in a CSV file must be differentiated between each other. For the identification, select columns with values which must be unique for the corresponding type of catalog entries.

Products & categories identification options

Select the option which is appropriate for your case:

  • Product name: products and their categories will be identified by the values in the column for which you have selected the “Product name” property.
  • Public link: products and their categories will be identified by the values in the column for which you have selected the “Storefront link” property.
  • Product ID: categories will not be imported or updated, and products in the file will be identified by the values in the column for which you have selected the “Product ID” property. Product ID товара is a numeric ID of a product stored in database table shop_product. It is displayed on the product-editing page in the store backend.
  • SKU identification column: categories will not be imported or updated, and products in the file will be identified by the values in the column which you will select for SKU identification.
  • (skip products and categories, import only SKUs): neither categories nor products will imported or updated; only product SKUs will be imported or updated, they will be identified by the values in the column which you will select for SKU identification.

SKU identification options

Select the option which is appropriate for your case:

  • By SKU code and features used for product variety selection: this option is appropriate when you need to identify SKUs either by their codes (the Product variety selection column contains value 1) or by code and features for product variety selection in the storefront (the Product variety selection column contains value 2).
  • SKU code: product SKUs will be identified by the values in the column for which you have selected the “SKU code” property.
  • SKU name: product SKUs will be identified by the values in the column for which you have selected the “SKU name” property.
  • SKU ID: product SKUs will be identified by the values in the column for which you have selected the “SKU ID” property. SKU ID is a numeric ID of a product SKU stored in database table shop_product_skus.

5. Advanced settings

If necessary, specify advanced import settings:

  • Ignore category nesting when searching for matching products. When this option is enabled (recommended), existing files during import will be detected within the entire catalog, regardless of their category assignment, by the values in the product identification column. When this option is disabled, then existing products will be detected only inside the static categories in which those products are specified in a CSV file. The visibility of products in dynamic categories is not checked.
  • Product type. The selected product type will be assigned to all new products which do not have a product type specified in a CSV file.
  • Non-HTML product descriptions. Enable to automatically add HTML tags <br> to exported product descriptions, if none of your product descriptions contain any HTML tags.
  • Product images import local path. if you have specified file names, rather than URLs, in image columns in your CSV file, then upload those files prior to import to a subdirectory of one of the root directories available for selection in this drop-down list. Then enter the path to the subdirectory with uploaded files next to the name of the selected root directory. Note that you have to upload product image files to a directory located on your server rather than your PC.
  • Unique product image URLs. If in your product image URLs equal file names are used and only the path to a file is different, then select “by file path” option. If both file path and name are the same and only the domain name differs, then select “by file path and domain name”.
  • Stock level for products with selectable features . Select one of the options to import stock count values for automatically generated SKUs, based on selectable feature values specified inside special double braces <{}>.

6. Import options review

After specifying the desired import parameters, click on the review button at the page bottom in order to analyze your CSV file contents before it is actually imported and to evaluate the number of categories, products, and SKUs which will be imported into your store. If you are not satisfied with the review results, change import settings and repeat the review action.

7. File import

To complete the data import, click on the confirmation button at the page bottom. Keep the import screen open in your browser until the operation is completed!

0 comments

    Add comment

    To add a comment please sign up or login

    Раздел помощи работает на основе приложения «Хаб»