Product Import Spreadsheet

OVERVIEW

As merchants, we use spreadsheet software on a regular basis.  It is for this reason and more that your store will come to you with a convenient way to download your product catalog and update it using a standard Excel spreadsheet.  You don’t have to spend time in the store admin area to update products unless you want to.

REQUIREMENTS

The following information is required to already be known:

  • Product details (e.g., price, types, variants, categories, images, etc.)
  • How to use Microsoft Excel compatible spreadsheet software

GETTING STARTED

This article will walk you through the spreadsheet that you have available to use in your Hotcakes Commerce store.  It would be useful to see the related articles as well to get more information about how to use this spreadsheet for product import. 

EXAMPLE SPREADSHEET

You can get a sample spreadsheet at any time by downloading it from your store admin area.  See our documentation on Product Export for specific details on how to do this.

PRODUCT SPREADSHEET

The product spreadsheet is an Excel spreadsheet that contains all of the details of your product catalog.  This includes: products, categories, product choices, product information tabs, and product properties.  Basically, everything you need to specify when adding or updating a product or service.  Each of the fields map back to the field of the same name in the product catalog view of your store admin.  The details of each tab are discussed below.

Main Tab

This tab contains the majority of the information for each of the items you sell in your product catalog.  It is split into 6 sections: Main, Pricing, Properties, SEO, Tax, Shipping, and Advanced.

Main Section
Column Name Value Description
Slug Any Text A unique value that identifies the product. It is also used to generate the URL for the page where the product is viewed. This value will be used to look-up the product in your catalog during import. If found, an update will occur. Otherwise, a new product will be added to the catalog.
Active YES, NO Toggles whether or not the product will be available to customers.
Featured YES, NO Tells the system whether this product is featured or not.
SKU Text This should be any unique identifier for your product.
Name Text The name or title of your product as you want it to appear to customers.
Product Type List This should match the name of a product type that you’ve added to your product catalog. It is used to change how products are edited and viewed for merchants and customers, respectively.

 

Pricing Section

These values should include the currency symbol of your store.  For example, if your store sells in U.S. dollars, one hundred dollars would be reflected as $100.00.  Euros would be 100.00€.

Column Name Value Description
MSRP Currency The manufacturer suggested retail price is often used to show the difference between the retail value and what you are charging.
Cost Currency This is the amount you paid to help with reporting. Customers will not see this.
Price Currency The price you are charging the customer.

 

Properties Section
Column Name Value Description
Manufacturer List This should match the name of a manufacturer that you’ve added to your product catalog.
Vendor List This should match the name of a Vendor that you’ve added to your product catalog.
Image File Name This value should match the file name of an existing product image that you’ve uploaded.
Description HTML Description is the HTML representation of the main text that is displayed next to your product on the product view.

 

SEO Section

SEO stands for search engine optimization.  These settings help you ensure that your SEO strategy is applied to your store product views.

Column Name Value Description
Search Keywords Text Keywords are useful to add terms that you think will be used to find the product. This is useful to specify things that are not in the other product details, such as misspelled terms, competitive terms, etc. Keywords can be separated by a space and/or comma. There is no benefit to using a comma over a space, or vice versa. Only the store search will see and use these terms.
Meta Title Text This value is used for the store search and will also be placed into the HTML title tag in the web page for your product. Search engines such as Google or Bing will see and use this value as well.
Meta Description Text This value is used for the store search and will also be placed into the description meta tag in the web page for your product. Search engines such as Google or Bing will see and use this value as well.
Meta Keywords Text This value is used for the store search and will also be placed into the keywords meta tag in the web page for your product. Search engines such as Google or Bing will see and use this value as well.

 

Tax Section
Column Name Value Description
Tax Schedule Default This value will tell the store which tax schedule should apply to this product. It generally should be “Default” in most stores. If it is blank, then the value has not been set yet in the edit product view.
Tax Exempt YES, NO This specified whether or not a product is exempt from calculating tax.

 

Shipping Section
Column Name Value Description
Weight Decimal The weight should be represented as a decimal representation of how much your product weighs, in pounds. This is used to help calculate shipping costs.
Length Decimal The length should be represented as a decimal representation of how long your product is, in inches. This is used to help calculate shipping costs.
Width Decimal The length should be represented as a decimal representation of how wide your product is, in inches. This is used to help calculate shipping costs.
Height Decimal The length should be represented as a decimal representation of how tall your product is, in inches. This is used to help calculate shipping costs.
Extra Ship Fee Currency Extra Ship Fee is often called a handling charge. A value should be put here if you plan to charge a fee in addition to the shipping charge.
Ship Mode List

This field is used to help calculate shipping costs, depending on whether the product needs to be shipped and from where. One of following values should be entered:

  • None – Use this value if your product doesn’t require shipping. This is common for products like online services and software.
  • ShipFromSite – Use this value if the product is coming from your location.
  • ShipFromVendor – Use this value if the product is coming from the vendor’s location.
  • ShipFromManufacturer – Use this value if the product is coming from the manufacturer’s location.
Non-Shipping Product YES, NO This value tells the store whether the product needs to be shipped to the customer. Physical products typically should use YES unless it’s something that gets picked up by the customer. NO should be used for everything else, including products similar to online services, software, and events.
Ships in a Separate Box YES, NO Like the other shipping fields, this also helps determine shipping cost. If this product cannot be shipped with other products, specify YES.

 

Advanced Section
Column Name Value Description
Allow Reviews YES, NO Use this value to determine whether you want to allow customers to review the product on your store. Typically, YES is used to help drive more sales.
Minimum Qty Number This setting allows you to specify a minimum quantity that should be used when the customer adds this product to their shopping cart. If zero (0) is specified, then the user specified quantity is used. The user specified quantity will also be used when they select more than the minimum quantity specified here.
Inventory Mode List

This value will help the store determine what to do when a product is found to be out of stock. One of the following values should be entered:

  • AlwaysInStock – This is the default value if not specified. “In Stock” will be shown next to the product at all times.
  • WhenOutOfStockHide – If out of stock, the product will not be shown to customers.
  • WhenOutOfStockShow – If out of stock, the product will be shown with an “Out of Stock” message, but customers will not be able to order the product.
  • WhenOutOfStockAllowBackorders – If out of stock, “Backordered” will be displayed to the customer and they will be allowed to purchase the product.
Inventory Number This should be a positive number of how many products you have on-hand or how many you can sell. Zero (0) is an acceptable value to still sell the product.
StockOut Number This is the value that should be used to determine if the product is out of stock. In most cases, this value should be zero (0).
Low Stock at Number The value entered here will be used to help build reports in the store admin for you to identify products that need to be ordered.
Roles List If the product is assigned to one of more specific security roles, they'll be listed here as a comma-delimited list of their names.  If the product is not assigned to be exclusive to any specific role(s), then this should be empty.
Searchable YES, NO If you enter YES, the product will be available in the search and drill down views.  Entering NO will be the same as unchecking the search checkbox when editing a product in the administration area.

 

Categories Tab

Every product can belong to as many categories as you’d like.  This tab allows you to specify a product and the categories you want it to below to.  This will allow the product to appear on the appropriate pages and listings in your store.

All you have to do is ensure that there is a line for each product that you want to categorize and specify at least one category.

Column Name Value Description
Product Slug Text This is the unique identifier of the product, also called “Slug.” If it doesn’t match a product in your catalog, it will be ignored.
Categories Slugs Text Columns B and higher can be used to specify a category for the product to belong to. Each column should contain a single category name. If the category you specified isn’t found, a new category is created for you.

 

Example of products with one and many categories

Choices Tab

The choices tab functions very similar to how the categories tab does.  First, you add a row for the product that you want to specify choices for.  Then, each row below can be a new set of choices, until another product is specified.  As long as the product slug column is empty, any choices specified will be applied to the last product specified above the choice.  The choices you provide will be displayed to the customer on the product details page in the order that they appear in the spreadsheet.

Choices tab example

The table below describes the fields that you see in this area.

Column Name Value Description
Product Slug Text This is the unique identifier of the product, also called “Slug.” If it doesn’t match a product in your catalog, the choices you attempt to add will be ignored.
Choice Text This is the name of the choice as you would like a customer to see it.
Choice Type List

This value determines how the choice will be displayed to your customer. The following options are available for you to use:

  • CheckBoxes – This type will display a checkbox next to each choice item, allowing the customer to select multiple options at the same time.
  • DropDownList – This type will display the list of options in a drop down list, allowing the customer to only select one while using less space on the product page.
  • Html – This type is not really for the customer, but for you. It allows you to specify HTML. This is useful to provide additional help to your customer when choices are not easily understood.
  • RadioButtonList – This type lists the choice items using radio buttons, allowing the customer to only choose one of the options.
  • TextInput – This type allows the customer to type in text as their option. This is useful for options where the customer is allowed to personalize the product.
Shared YES, NO Shared choices are useful to allow you to create them once and then apply them to multiple products. For example, shirt sizes are the same for most varieties of shirts. If you specify YES, the option will be shared to other options. If the option doesn’t exist already, it will be created.
Choice Items Text You are able to offer multiple options (or items) for each choice your product has. You’ll add an item in column E and higher for each selection that you want a customer to be able to choose from. Each column should only have one option in it.

 

An example using the information from above could be applied to color options.  Let’s say that we are selling a shirt.  If you want to create a choice called colors and offer the shirt in red, white or blue, it would look like the image below.

A sample product with 3 colors

If you want that same product to also have a choice of size and for the customer to provide a name to put onto the shirt, the new entries would look like the next image.

A sample product with sizes and text now

Info Tabs Tab

Info tabs are a very useful feature for your products.  It allows you to be able to provide a large amount of detail about a product in a user friendly way.  Imagine you were selling laptops.  Info tabs would allow you to have a set of tabs below the various laptops you’re selling with tabbed information that might include an Overview, User Guides, Frequently Asked Questions, and Specifications. 

Again, this tab functions similar to the last one discussed, Choices.  The first row will include your product slug and the first info tab that you want your product to have.  The next row will contain an info tab for the same product, provided that you do not put another slug into column A.  Each additional row will continue to add an info tab to the previous product until another slug is specified.  It should be noted that while you are not limited to the number of info tabs a product can have, too many will begin to adversely affect the way the product details page is displayed to customers.

The next table illustrates what the fields are that allow you to add info tabs to your products.

Column Name Value Description
Product Slug Text This is the unique identifier of the product, also called “Slug.” If it doesn’t match a product in your catalog, the tabs you attempt to add will be ignored.
Tab Name Text This should be the name of the tab as you would like a customer to see it.
Tab Description HTML This should be an HTML representation of the information that you want to have displayed below the product when this tab is selected by the customer.

 

Type Properties Tab

Type Properties are found in the store admin as “Product Type Properties” in the Catalog menu and directly relate to Product Types.  Type properties can be added to product types to allow you to customize the edit and display views for groups of products that have different attributes and to properly maintain inventory over those different types of the same product.   For example, a product type of bicycle frame might have aluminum and carbon as options. 

The type properties tab allows you to specify the type properties for the product matching the slug for each row.  Each product can have multiple type properties specified, just like the previous tabs.  This allows you to add rows below the first row of the product and those type properties will be applied to that same product until another slug is added.  From that point, new rows will apply to the new slug, and so on.

The table below describes the columns that you have available to you.

Column Name Value Description
Product Slug Text This is the unique identifier of the product, also called “Slug.” If it doesn’t match a product in your catalog, the type properties you attempt to add will be ignored.
Property Name List The name specified here must match a property name that you have created in the store already. Otherwise, it will be ignored.
Value Text This field allows you to specify the value of the type property. Ideally, this value should match an existing one in your store. If it doesn’t already exist, it will be added.

 

Category Tree Tab

Categories are defined on the Categories tab, but that doesn't give you the ability to define the hierarchy and sorting of those categories.  That's where this tab helps you.  

All you have to do is ensure that there is a line for each category that you want to sort and put them in the order you want them to appear to your customers.  Then, define them as specified below.

Column Name Value Description
Slug Text This is the unique identifier of the category, also called “Slug.” If it doesn’t match a category in your catalog, it will be ignored.
Parent Slug Text This is the identified of the category that this one might be nested under.  Leave it blank if you want this to be a top-level category.  Otherwise, it might match the slug value of the category just above it.
Category Name Text This is the name that you wish the category to have when working with it in your administration area, and it's what your customers will see as well.

 

 

 

Have more questions? Submit a request

Need More Help?

Do you need more assistance with this article? Please review your support options.