Product CSV Imports and Exports

Product CSV Import and Export formats are now compatible. This means that you can export your store products via CSV, edit the values in the CSV and import them back without changing column names. You can add new columns for new product attributes, extras, categories, options and custom fields. This document will walk you through how to use Product CSV Imports and Exports and talk about all the supported columns and functionalities our Product CSV file format supports. 

Exporting Products via CSV

LemonStand offers the ability to export the products in your product catalog products via a CSV file. This makes it easy to back all of your product catalog data up and archive it if needed. It also makes it easy for you to bulk update your product catalog by exporting it to CSV, updating the values in the CSV file and then importing the updated CSV as described below.

To export your products to CSV, navigate to Product Catalog > Products in your LemonStand admin panel and click on the Export button on the bottom right of the product list as shown in the screen capture below. This will initiate an export and you'll receive an email notification with details about the export job and a link to the file that you can then download once the export is complete.


If have trouble receiving import or export email notifications or finding one you received previously in your email, you can always find a record of all import and export jobs in your store's Audit log, which can be accessed from the Administration > Audit Log page in your store administration. An example of what this looks like is included below.

By Clicking on the job in the audit log, you can see details of the results of your export or import job, along with a link to the file that was exported or imported for the job, as shown in the example below.


 

Importing Products via CSV

LemonStand offers the ability to import products via a CSV file. Allowing users to import large batches of new products, as well as bulk update existing products, in a quick and efficient way. The product CSV import tool also supports the import of product images via a corresponding ZIP file and related column in your CSV file.

To better understand the products CSV import process, let's work through an example import that will include product images.

Example product CSV files

You will need to download our example CSV file as well as our example-images zip file. You can download these files here:

Structure

See the supported columns in the tutorial section.

Import the example files

To import a product's CSV into LemonStand, head over to the Product Catalog > Products page in your LemonStand store administration. 

You will now be on the Products Page. Click on the Import button located at the bottom right of your product catalog listing, as shown below.


This will now bring you to the Products CSV Import page.

The first section is the CSV File section, this is where we will select the CSV file that we want to import. Click on the upload button and find our example.csv file in the location you saved it to on your computer. LemonStand will now upload the CSV file, preparing it for import. Once this is complete you will see a notification next to the upload button that says File example.csv uploaded.

Update existing products

The next section on our Products CSV import page is the Update existing products section which contains a checkbox. Checking this box will cause the import to update any products in the system that match a record in the CSV file. This match is made based on the product SKU.

Create new categories

The next section is the Create new categories section, which also contains a checkbox. Selecting this checkbox will cause the import tool to create a new category in the LemonStand system when it encounters one in the CSV file that currently does not exist. Let's check this box for our example.

In our example CSV file we have indicated that we want our first record, on line 2, to import the product as belonging to the T-Shirt category which will be a child of the Women's category. This is done by setting our category in the CSV file as Women's/T-Shirt. The / symbol indicates the child/parent relationship, and in this case since the T-Shirt category does not exist, it will be created on import. For multiple categories, use columns to separate them. IE category_1, and category_2.

Importing images

The next section is the Images section.

The LemonStand Products CSV import tool allows for the import of associated product images. Let's have a look at our images.zip file and our example CSV file to examine how this is taking place.

In the images column of our CSV we will put the path of the image based on where it is inside our images.zip file.

In this case, the image we are referencing is not in the root of the zip archive but rather it is in a folder in the archive named images. So we indicate that the image to be used for this product is located at "/images/brushed-lemons-white.jpg".

Let's now click on the upload button in the images section and then locate our images.zip file. Once the upload is complete you will see a status message that says something like:

File images.zip uploaded

Note

The maximum file size for image archives for CSV import is 1.5GB. If you have a large number of images that exceeds this size, you may need to break your imports into more than one file.

Ready to import!

We are now ready to run the import.

Click the Import button at the top of the page. After the import process is complete you will receive an email that will reflect the outcome of the import process.  In our case, we should see that that the import was successful and added 25 items.

If you navigate to the front-end of our store you will now see your newly imported products, images and categories. 

Supported CSV Columns

Product CSV imports and exports have matching supported columns, except the images column is only supported on import. You can view all the supported columns below

(Click on rows for an example of that column's value)

Column Usage
name String, name of product.
Brushed Lemons
type String, if the product type entered does not exist the default product type will be used.
Goods
category_1 String, on import it will use existing categories or create new categories depending on if the create new categories checkbox is checked.
Men's
title String, refers to SEO title tag.
Lemons
url_name String, must be a valid url. So no special characters
brushed-lemons-white
sort_order Number
5
tax_class String, refer to columns with special considerations below for more information.
Product
manufacturer String, refer to columns with special considerations below for more information.
LemonStand
sku String, used to identify existing products on import.
brushed-lemons-white
parent_sku String, only used by product variant rows. Identifies the parent of the product variant.
brushed-lemons-white
in_stock_amount Number, refer to columns with special considerations below for more information.
Example
barcode String
123123
base_price Number, base price of product.
20.00
cost Number
5.00
depth Number
1.00
width Number
1.00
height Number
2.00
weight Number
1.50
enabled Boolean
1
track_inventory Boolean
0
is_on_sale Boolean
0
sale_price_or_discount String, can be a number or percent value.
5.00%
enabled_backend Boolean
1
allow_preorder Boolean
0
hide_out_of_stock Boolean, hide when out of stock.
1
out_of_stock_threshold Number, stock amount to signal when out of stock.
15
allow_negative_stock Boolean
0
is_catalog_visible Boolean
1
is_search_visible Boolean
1
enable_product_shipping_rates Boolean
0
description Rich Text String. You can include HTML tags to style the text.
<p>When life gives you lemons, make a cool shirt! Figure flattering and fresh, this crisp white shirt is overlaid with an artful watercolor-brushed lemon and speckled yellow pattern. Dress up this feminine fit with a skirt for Spring, or pair with jeans to keep it casual.</p><p>• Women's fit • 100% organic cotton • Fairtrade</p>
short_description String
Best T-Shirt for hot summer months.
meta_description String
T-Shirt for the summer
meta_keywords String
T-Shirt
images String, refer to columns with special considerations below for more information.
/fruits/banana.jpg


Columns with special considerations

type: if column is not supplied or is supplied but the value is empty, the default product type is used for new products and the product type of existing products is unchanged; if the supplied product type does not exist, it will not be updated (for existing products) or the product will not be created (for new products)

tax_class: same as product_type, if column is not supplied or is supplied but the value is empty, the default tax class is used for new products and the tax class of existing products is unchanged; if the supplied tax class does not exist, it will not be updated (for existing products) or the product will not be created (for new products)

manufacturer: use manufacturer name, if a manufacturer with the specified name (case insensitive) is not found, a new one is created and used

images: Note, the images column is the only column that only works on import. To use this column make a zip file containing the images you wish to upload during CSV import, multiple images should be separated with a comma, the image files can include a folder name if the folder is included in the archive (eg. "/fruits/banana.jpg"). If the column is empty, images are not updated. If value is set to dash (-), all existing images are removed. If the value is an image or multiple, any existing product images are deleted and replaced with the listed images.

in_stock_amount: If this column is missing or has no value, stock will not be updated; if a value is set and is different than the current stock amount (assumed 0 for new products), an inventory correction will be created for the difference between the current stock amount and new stock amount

parent_sku: the SKU of a product variant's base product

track_variant_inventory: enables inventory tracking for the product variant


Columns with keys

Keys start at 1 and increment individually for categories, options, attributes, product extras, and custom fields. For example if one product in your CSV import has 2 categories and the rest have just 1 category. You will need 2 category columns, however you will leave the column category_2 blank for all the products without a second category. Here is an example that uses keys to import all of the following values.

Categories: Each category should include the full category path, using the "/" character sequence (eg. "Main category/Child category"); multiple categories should be separated by columns. IE category_1, category_2.

Options: Has 2 columns per option. option_name_key and option_value_key. Start at key = 1 and increment for each subsequent option. The option_name_key always holds the name of the option. However, the option_value_key column changes depending on if the product has variants or not. If a product has variants. Only populate the option_name_key column for the Parent Product and don't populate the option_value_key column for the Parent Product. For each variant place the value for the option_name_key under the corresponding option_value_key. If a product has no variants. Populate the option_name_key with the name of the option and the option_value_key with the allowed option values separated by "|". eg. "Red|Blue|Green".

Attributes: Has 2 columns, attribute_name_key for the name of the attribute and attribute_value_key for the value of the attribute.

Product Extras: Supports 6 columns per Product Extra. The Product Extra is identified by its value in the column product_extra_name_1.

The columns are:

  • product_extra_name_1
  • product_extra_base_price_1
  • product_extra_sku_1
  • product_extra_enabled_1
  • product_extra_short_description_1
  • product_extra_description_1

The key in this case is 1.

Custom Fields: If the custom field name doesn't exist it will not make a new custom field. Custom field's have 2 columns, custom_field_name_1 and custom_field_value_1. Where 1 is the key in this case. custom_field_name_1 is used to identify the custom field that is going to be updated with the value of custom_field_value_1.

Required Columns on CSV Import

1. SKU for existing products. (Product variants do not require a SKU if they have their options)

2. Sku, name and base_price for new products.

Boolean Columns

The following values will be considered as 'on': yes, y, enabled, active, 1, true

The following values will be considered as 'off': no, n, disabled, inactive, 0, false

Supported Product Variant Columns

Rows are considered to be product variants only if the parent_sku column is populated.

For product variant rows, only the columns below will be looked at. All values in columns that are not in the list below will be ignored.

  • parent_sku
  • sku
  • in_stock_amount
  • options
  • barcode
  • base_price
  • cost
  • depth
  • width
  • height
  • weight
  • enabled
  • track_inventory
  • is_on_sale
  • sale_price_or_discount
  • images

Product variants can be identified by their SKU, or by their option combination. Variants must have a value for each option their parent has. 

Making Compatible Imports and Exports

Product CSV imports and exports are compatible. To mass edit products you can export your products, edit them in a spreadsheet and import them using the edited CSV file.

Test