Thread: How-to Use WSI to Bulk Update Products/Variants with Excel!

ML8.0.1.2

The marriage of Excel and WSI has been a long-time coming! Based on the lack of 'easy' options for us Excel data processing lovers, I put together this method that I have enjoyed over the last few months. Using this method, you will end up with a REUSABLE Excel file that you will simple open, click one button, and it will be populated with your exported product/variant data ready to edit and upload!

This method uses the WSI to get Product/Variant data, open it in Excel, edit it, and upload the changes via the product import tool in the admin. This method has been very helpful for me in certain instances, especially with large updates of 'rich' info like descriptions that cannot be done properly through SQL queries. Of course, having to upload through the admin means not ALL fields will be update-able, only the fields for which the admin allows imports. Having said that, read through this and hopefully you will be able to use the info to get started on your own WSI->Excel product/variant updating!

Initial Setup takes some time and is a bit tricky. Following/future exports/updates/uploads are quick much more simple!

INITIAL SETUP STEP 1:
First of all, using the WSI test program we get our product/variant data as per the WSI manual. I strongly suggest retrieving a small set for the first time to make Excel setup easier. I retrieve products/variants by manufacturer like so:

C#/VB.NET Code:

<AspDotNetStorefrontImport>
 <GetProduct ForEntityType="Manufacturer" ForEntityID="13" IncludeVariants="true"/>
</AspDotNetStorefrontImport> 


In some cases, this request times out. I have tried everything and more to bypass the time-out, but to no avail. Therefore, this bit of code works to get ALL the products WITHOUT timing out..... for no logical reason at all. Truly those productId's of 1 and 48 mean nothing and actually don't even go with manufacturer 13 in my database, but for some reason this allow a complete export of product/variant data with no time out!

C#/VB.NET Code:

<AspDotNetStorefrontImport>
<GetProduct ForEntityType="Manufacturer" ForEntityID="13" IncludeVariants="true"/>
<DefaultWhereClause> ProductId&gt; =1 and ProductId&lt;=48</DefaultWhereClause>
</AspDotNetStorefrontImport> 


INITIAL SETUP STEP 2:
Moving on, you now have your product/variant data in the WSI results. Copy the results exactly how they appear in the WSI readout and Paste them exactly the same into Notepad. Don't save yet. Create a folder where you will store the export data file each time you do an update. (You will NOT be able to move this folder without messing up the Excel process later on, so be sure you like the folder location and keep it put. I just use the "WSITestProgram" folder.) Now, save the Product/Variant data that you have in notepad as an XML file. This name will need to be the same for every future export, so make it generic. I called it "ProductImport.xml".

INITIAL SETUP STEP 3:
Now we start with Excel. I used Excel 2003 for this, haven't tried it on 2007 yet. This part is a pain, but only needs to be done once as long as you never change the folder name/location and export data file name as described above. Start with a blank workbook. Open up the ASPDNSF excel product import sample file and copy Sheet 1 to Sheet 1 of your new workbook. Delete all of the sample product information.

INITIAL SETUP STEP 4:
Now go to Sheet 2 of your new workbook. Select cell A1. Select "Data -> XML -> Import". You will be prompted for a file to import. Select your product/variant data XML file that you just saved in the step above. You will be told that it is not mapped to a schema and Excel will make one for you. You will be asked where you want the output to go and by default it will have cell A1 selected, accept/click OK. It may take a little while to load the data into your sheet depending on the size of your data file.

INITIAL SETUP STEP 5:
Once this is done, you will have Sheet 1 which is essentially the barebones import sample file from ASPDNSF and Sheet 2 which is your product/variant data. Now comes the tricky part. Good thing is that it only needs to be done ONCE, not for every export/update! Begin mapping the values/cells in Sheet 2 to Sheet 1 so that your exported data populates your ASPDNSF import sample file. Try to make the mapping generic so that it would work for any product/variant data export file, not just the one you are using now. This will save you LOTS of time in the future.

Here is what my mapping formulas look like, of course you may decide to map things differently depending on your needs. These mappings are in the order of the ASPDNSF sample file's cells, so my first formula goes in cell A4, second formula goes in cell B4, etc. "BLANK" means I left the cell empty altogether.

=ProductImport.xml!C2
=ProductImport.xml!W2
=ProductImport.xml!BN2
=ProductImport.xml!AR2
=ProductImport.xml!BO2
BLANK
BLANK
BLANK
BLANK
BLANK
BLANK
BLANK
=ProductImport.xml!F2
=ProductImport.xml!G2
=ProductImport.xml!Q2
=ProductImport.xml!R2
=ProductImport.xml!P2
=ProductImport.xml!D2
=ProductImport.xml!M2
=ProductImport.xml!AH2
=ProductImport.xml!AI2
=ProductImport.xml!AD2
=IF(ProductImport.xml!AZ2="True",1,0)
=IF(ProductImport.xml!BB2="True",1,0)
=ProductImport.xml!AN2
=ProductImport.xml!I2
=IF(ProductImport.xml!AQ2="True",1,0)
=AA4
=AA4
=IF(ProductImport.xml!AT2="True",1,0)
=IF(ProductImport.xml!AV2="True",1,0)
=ProductImport.xml!AW2
=ProductImport.xml!AM2
=ProductImport.xml!BS2
=ProductImport.xml!T2
BLANK
=ProductImport.xml!CA2
=IF(ProductImport.xml!CD2="True",1,0)
=ProductImport.xml!CB2
=ProductImport.xml!CK2
=ProductImport.xml!CE2
=ProductImport.xml!CG2
=ProductImport.xml!CH2
BLANK
=ProductImport.xml!CL2
=ProductImport.xml!CM2
=ProductImport.xml!CO2
=ProductImport.xml!CP2
=ProductImport.xml!CN2
=ProductImport.xml!CR2
=ProductImport.xml!CS2
=ProductImport.xml!CT2
=ProductImport.xml!DS2
BLANK
=ProductImport.xml!DR2
BLANK
=IF(ProductImport.xml!CV2="True",1,0)
=IF(ProductImport.xml!CW2="True",1,0)
=IF(ProductImport.xml!CX2="True",1,0)
=ProductImport.xml!CY2
=IF(ProductImport.xml!DA2="True",1,0)
=ProductImport.xml!DJ2
=ProductImport.xml!DM2
BLANK

INITIAL SETUP STEP 6:
After this, you should have importable data in Sheet 1, row 4 which matches your exported data file's first product/variant exactly. THIS IS THE TIME TO SAVE YOUR EXCEL FILE FOR FUTURE USE! Save it in the same folder as your product data XML file. I called it "ProductImport.xls". This is the file you will start with for FUTURE product/variant exports which I will explain below.

INITIAL SETUP STEP 7:
Continuing with the product/variant set at hand, select cells A4 through BL4 and auto-fill the following rows until you have all of your export file's products/rows in Sheet 1. You will get at least two rows for every product, this is because Excel is one-dimensional and your XML file is multi-dimensional. Basically, for each category and manufacturer the product is mapped to, you will get a row with different "Manufacturer" column value. This is normal and will be fixed shortly, so just move on for now.

INITIAL SETUP STEP 8:
Now open the original Excel import sample file in another Excel window and go back to your new workbook. Copy your newly populated "Sheet 1" - copy the entire sheet by right-clicking the little square to the left of column "A" and to the top of row "1" to select the entire sheet and select copy. Now right-click that same little square on the original Excel import sample file and click "Paste Special" and select "Values". Perform a "Save-As" on the import sample file that now has your product/variant data in it. Save it as anything you want, anywhere you want... like "Manufacturer13update.xls" or something. Now you can close the XML-mapped Excel file (i.e. "ProductImport.xls") and DO NOT save it as you need it to remain the way it was when you saved it as per the instructions above.

INITIAL SETUP STEP 9:
Continue to work with your new product/variant upload excel file (i.e. "Manufacturer13update.xls") as needed. The first step is to remove the duplicate product rows that have the incorrect category listed. Watch out for items mapped to more than one category though as there will be a row for each category. In that case, you need to compile the desired category mappings into one row before deleting the duplicate rows. (hope this makes sense, if not, it will when you look at your excel sheet if this applies to your data). Make sure that your manufacturer column has the correct values as well as the rows with the correct category info will have the INCORRECT manufacturer name. Also make sure to clean up any other imperfections in the sheet such as "0"s placed by excel where there were empty cell references in your mappings/formulas. Once your data is updated and all desired changes are made, you are ALMOST ready to upload. You should first repeat the method of opening another original sample import file, copying the update sheet, pasting as "values" to the original sample import file sheet, and saving that file as something like "Manufacturer13updateFINAL.xls". This helps to prevent the all-so-common Excel formatting errors that cause errors when uploading the file through the ASPDNSF Admin panel.

CAUTION! If you left the SKU(s), MPN(s), and Title(s) the same, the existing product SHOULD be updated rather than a new one created. BEWARE that if you changed the SKU(s), MPN(s), and/or Title(s) it is extremely likely that you will NOT update the existing product, but create a new one so it may be wise to delete the old products through your admin, WSI, or SQL before uploading the newly updated ones. THESE ARE THE SAME ISSUES AS WITH DOING UPDATES THROUGH ASPDNSF ADMIN EXCEL PRODUCT IMPORT NORMALLY.

At this point, you should be ready to upload and your first exported product/variant data set's update will be completed! Even better, next time IS VERY QUICK!

FOR FUTURE EXPORTS/UPDATES, FOLLOW THIS SIMPLE PROCESS!

1. Retrieve product/variant data through WSI, copy/paste results to notepad and overwrite your XML file from above (i.e. "ProductImport.xml").
2. Open the XML-mapped Excel file from above (i.e. "ProductImport.xls") and go to Sheet 2 Cell A1.
3. (the really cool part!) Click "Data -> XML -> Refresh XML Data" and watch Sheet 2 fill up with your newly exported products/variants! Easy as that!!
4. Follow the initial setup steps 7-9 above for relevant product/variant data updating/uploading instructions and you are done!

Using this process, I was able to just update 274 generic product descriptions in bulk with Excel and total time it took was about 10-15 minutes!

Hope this helps, and as always use this info at your own risk. You are responsible for any consequences resulting from the use of this info. I am simply sharing info on a method that works for me, whether all, some, or none of it helps you hopefully it has been informative!

Also, this method can be used to do exports of product/variant data to Excel WITHOUT USING WSI AT ALL. More detail may come later as I try this method more, but basically you start by creating a product feed that generates an XML file with all your product/variant data. Run the product feed and instead of linking Excel to your WSI generated XML file, link it to your product feed via FTP. This way you can execute your feed, open Excel, refresh your XML, and that's it - no WSI.

Cool! Thanks for the post. It should come in handy.