DataPort Import Product Variants CSV

From AbleCommerce Wiki
Revision as of 12:00, 10 December 2009 by Naveed (Talk | contribs)

Jump to: navigation, search

Product Variants Export/Import using DataPort

Product variants CSV export/import feature is implemented to provide users a way to batch add/edit/delete product options and respective variants. The export feature is integrated with "Customized Products Download", and users can take advantage of existing product filters to selectively export their data. To export product variants CSV select the "Product Variants CSV" format from 2nd step of "Customized Products Download" wizard.

List of fields

Here is the list of fields that will be exported:

ProductId
ProductName
ProductSku
ProductCategories
ProductPrice
ProductMSRP
ProductCostOfGoods
ProductInventoryMode
ProductInStock
ProductInStockWarningLevel
ProductAllowBackorder
ProductOptions
VariantName
VariantSku
VariantPrice
VariantPriceMode
VariantWeight
VariantWeightMode
VariantCostOfGoods
VariantInStock
VariantInStockWarningLevel
VariantAvailable


Each CSV record will represent one product variant. So, products having multiple variants will be exported as multiple records each for one individual variant. However data for the following product related fields will be repeated for each variant of one product:

ProductId
ProductName
ProductSku
ProductCategories
ProductPrice
ProductMSRP
ProductCostOfGoods
ProductInventoryMode
ProductInStock
ProductInStockWarningLevel
ProductAllowBackorder
ProductOptions


This duplication is there because of CSV document structure. I will define purpose of each field after a while.


Variants CSV export feature is pretty much simple, while the respective import feature has much more depth and offers more functionality. The import feature is added on a new screen and new menu items and links are added to respective locations like on home page, "Upload" top navigation menu and to left navigation menu.

Updating Existing Records

When importing variants CSV file "ProductId" is used as key field for existing products. So, it is a required field and must be provided. If a positive not zero value is provided for product Id field then import routine will try to find a product with the given id and if found the respective product will be updated. Otherwise an error is logged that no product is found for the given Id.

Importing New Products (with variants)

If a zero (0) value is provided for "ProductId" field then the import routine will think that user is trying to import a new product. As user can specify multiple new products for import and all of those will have value zero "0" for ProductId field, so the ProductId field can no longer be used as key field to identify such new products. So, in this case "ProductName + Categories" composite field will be used as key field to identify new products and their respective options and variants.

Key Fields and Required Fields

In short "ProductId" is the only required field if your CSV data does not contain any new products. While "ProductName + ProductCategories" fields will also be required if your data contains new products (i.e. products with zero id's).

Upload Options

For the following two upload options details are provided on import screen UI:

1. Only import variants data 2. Import product data as well

Select "Only import variants data" if you only want to update variants data and do not want to update products data. Select the 2nd option if you want to update products data as well.

Creating/Preparing CSV Data

Adding/Editing/Removing Options for Existing Products

To create/add options for an existing product the CSV should be something like:

"ProductId","ProductOptions"
"44","Colors:Red,Green,blue"
"45","Size:Small,Medium"

And it will update the Product with Id "44" by creating option "Colors" with three choices "Red", "Green" and "blue". And for Product with Id 45 will create an option "Size" with two given choices. If the product 44 and 45 has some different set of options and choices the non-matching existing options will be deleted. In case if product 44 already has same options and choices then no change will occur. In case if product 44 already has two of the three choices only one new choice will be added. And respective variants data will be preserved for existing choices. Details for each option and choice added/deleted will be logged and log will be shown at the end of import process.

Adding Options for new Products

If your CSV contains a mix of new products and old products then syntax can be something like:

"ProductId","ProductName","ProductCategories","ProductOptions"
"44","","","Colors:Red,Green,blue"
"0","New product","Temp","Colors:Red,Green,blue"

Here for the CSV record having zero "0" as product Id a new product will be created and the respective options are added to the product.

Dealing with Multiple Options

If your product has multiple options, you can specify them under "ProductOptions" column/field by using pipe sign "|" as options delimiter using following syntax:

"ProductId","ProductOptions"
"44","Color:Red,Green,blue|Size:Small,Medium"
"45","Color:Red,Green,blue|Size:Small,Medium"

The above CSV will create options "Color" and "Size" with specified choices for product 44 and 45.

Importing/Updating Variants Data

For the above CSV each variant will use the default null values as we are not importing variants data. However if you want to provide custom data for any of the variant, like you want to provide an SKU or want to adjust price, weight values for any variant you can specify details for each variant. For example for the above products you can define the CSV like this:

"ProductId","ProductOptions","VariantName","VariantSku","VariantPrice","VariantPriceMode","VariantWeight","VariantWeightMode","VariantCostOfGoods","VariantInStock","VariantInStockWarningLevel","VariantAvailable"
"44","Colors:Red,Green,Blue","Red","PRD_RED","1","Modify","0","Modify","0","20","2","True"
"44","Colors:Red,Green,Blue","Green","PRD_GRN","2","Modify","0","Modify","0","20","2","True"
"44","Colors:Red,Green,Blue","Blue","PRD_BLU","3","Modify","0","Modify","0","20","2","True"
"45","Size:Small,Medium","Small","","0","Modify","0","Modify","0","20","2","True"
"45","Size:Small,Medium","Medium","","1","Modify","1","Modify","0","20","2","True"

When you are specifying the variant details in CSV the "VariantName" field is used to identify each variant. So, it must be provided. However other variant related CSV fields are optional and can be skipped. You can optionally import the data for product related fields as well.

Another important point to note is that we are not bound to provide data for each possible variant in CSV file. We can only provide the data for the variants we want to update, and we can skip CSV records for the variants which we do not want to change. The key point is if we provide less and to the point CSV data, the import will be faster and less resource intensive. So, while updating it is always better to filter out the CSV records for which there is no change.

Now consider we want to import some new products with some basic product and options data and variants data. The CSV data can be something like this:

"ProductId","ProductName","ProductSku","ProductCategories","ProductPrice","ProductMSRP","ProductCostOfGoods","ProductInventoryMode","ProductInStock","ProductInStockWarningLevel","ProductAllowBackorder","ProductOptions","VariantName","VariantSku","VariantPrice","VariantPriceMode","VariantWeight","VariantWeightMode","VariantCostOfGoods","VariantInStock","VariantInStockWarningLevel","VariantAvailable","MSExcelWorkAround"
"0","Personalized Gift Certificate","","Sample Category:Gift
Ideas","50.0000","0","0","None","0","0","False","Choose
Style:Purple,Green,Gold,Red","Purple","GFT_PURPLE","0","Modify","0","Modify","0","0","0","True","Do
not delete"
"0","Personalized Gift Certificate","","Sample Category:Gift
Ideas","50.0000","0","0","None","0","0","False","Choose
Style:Purple,Green,Gold,Red","Green","GFT_GREEN","0","Modify","0","Modify","0","0","0","True","Do
not delete"
"0","Personalized Gift Certificate","","Sample Category:Gift
Ideas","50.0000","0","0","None","0","0","False","Choose
Style:Purple,Green,Gold,Red","Gold","GFT_GOLD","0","Modify","0","Modify","0","0","0","True","Do
not delete"
"0","Personalized Gift Certificate","","Sample Category:Gift
Ideas","50.0000","0","0","None","0","0","False","Choose
Style:Purple,Green,Gold,Red","Red","GFT_RED","0","Modify","0","Modify","0","0","0","True","Do
not delete"

When you will import above CSV data a new product "Personalized Gift Certificate" will be created under category ""Sample Category -> Gift Ideas" and the respective options and variants data will be imported for that new product.

We can attach a product to multiple categories same way we were able to do so while importing normal products CSV data.