DataPort Import Product Variants CSV
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 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/Editing/Removing 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.
