Importing products lists, specifying category, not overwriting and ending up with duplicates, unable to merge products etc. etc.
I have a product list that I am working with. Because QB online has no nice way to make batch edits, I sometimes export the product list to a spreadsheet for manipulation. Then I re-upload the product list. (Sometimes I just upload only wholly new products, as that's a reasonable way to create several new products at once in QB because it's much easier to manipulate prices and costs and other data in a spreadsheet, again because QB annoyingly has no batch edit functions.)
Here's what actions I took to end up in my current situation:
1) I went into QB online and manually edited about half of my products and included them in a new category, which I named "Parts and Accessories".
2) I filtered by the new category, and then exported the resulting product list to a spreadsheet.
3) Problem1: is that when exported that way, every single item in the first column(Product/Service Name) has the words Parts and Accessories: added in front of the product name! (i.e. so the cell might read in total Parts and Accessories:Battery Charger, whereas the actual product name is just Battery Charger).
a. This can be solved a couple of ways in a spreadsheet by using a Split Column command or other methods, which I did.
b. Note if the product doesn't have a category assigned, this doesn't happen.
4) So after splitting off the category into it's own column on the spreadsheet, I deleted that column of data.
5) PROBLEM2: There is no way to have a column for Category in a spreadsheet and upload that data back to QB online! There are actually several fields of data under each product in QB that you just can't upload via spreadsheet data, the only way to change them is to edit each product individually (because no batch edit). It's highly, highly annoying.
5) After changing some pricing information and inventory quantities I saved the spreadsheet and uploaded that data back to QB. The product names remained exactly the same.
a. I did check the box to overwrite existing data
6) PROBLEM3: This is the big problem, instead of overwriting the old products with new data as I expected, QB created an entire new second set of duplicate products!
a. The result is now I have a duplicate set of the same product, one with correct category, but the newer one with updated pricing information.
7) These duplicate products do not have any category. When I try to change their category to Parts and Accessories I get the message popup that I need to merge them with an existing product,
8) BUT PROBLEM4: the merge function does not work for inventory products.
9) So now I'm sitting here contemplating a ton of work because I need to delete(make inactive) every single old product, then go in and update every single new entry with the correct category. And I have to do this one-by-one to each individual item.
ARRRGH This is very very frustrating. There is a serious mismatch and inconsistency with the way QB Online treats export and import of product lists, which data is imported or exported, duplicate items, and it is creating a ton of extra work for me.
I would love a solution. Or to know if I did something wrong on one of my steps above and could have avoided this situation. The only thing I can think of is to simply not use any product categories, which is my previous situation and I had no problems* downloading/uploading spreadsheets and overwriting data in QB. But I want to be sure, because ending up with complete duplicate sets of products every time is totally unacceptable, let alone the other issues.
Thank you.
*Actually I had lots of problems, like QB screwing up the hyphen in "noninventory" which screws up upload, not allowing actual deletion of products, just deactivation, trying to upload the formula present in a cell, not the value, etc.