Editor's note: If you are using the latest version of CrystalCommerce's admin, this article is not applicable. Users of the new admin may export a CSV of the order items using the options on the full order details page. For more information, check out this article.
While it is fairly rare, there are a few situations where you may need to either add to or remove from your inventory all products on an order.
For example, if you move an "in checkout" direct website order to another status manually (which we typically don't recommend), none of the quantities on that order will be reduced from your inventory.
The following is a step by step guide on how you can quickly adjust quantities for all products from an order, no matter how large that order is:
Find the order, and copy and paste the line items into a spreadsheet.
2. Save the spreadsheet as a .csv file. Close out of it, then re-open it. This will remove the formatting and any images that copied over.
3. Remove the last three columns (after the one with price x qty). Insert a blank row at the top of the spreadsheet. Then, copy and paste the content of column A into the next open column (D). Make sure that you paste it so that column D starts at row 1, while A, B, and C start on row 2. Delete the last row of column A.
4. Delete the content in D1. Under column E, type a, b, a, b for rows 2 through 5. Copy it all the way down. Go to the “Data” tab and click on “Filter”. Filter column E to only show “b”. This should make every other row hidden. Delete the remaining visible rows, then turn off filtering. Finally, delete column E.
5. Rearrange the columns so that you switch column C and column D. Highlight column D (which will now be showing price x qty). Still under the “Data” tab, click on “Text to Columns”. Choose Delimited, then choose the “Other” option and enter ( into the field. Go to “Next”, then “Finish”.
Next, select column E and click on “Text to Columns” again. Select Delimited, uncheck the box for “Other” and check the box for “Space”. Click “Next”, then “Finish”
Afterward, delete the column before the quantities, and the two columns after it (should be D, then E & F).
6. Name the columns in the top row. A1 should be Product name. B1 should be Condition. C1 should be Category. D1 should be Add Qty.
Under the Condition column, use Find and Replace to replace the text “Condition: “ with nothing. (With MTG, you would also use find and replace to delete all instances of the “ | Language: English” text, then add a column for Language).
7. At the end of each product name, there is an extra space. In the first open column (column F in our example), use the following formula on row 2: =TRIM(A2). Copy the formula all the way down. Then select those cells with the formula. Copy them, then highlight A2. Right click, and select "Paste Special". Choose the option for "Values", and then click "OK". Once you are done, delete the extra column with the =TRIM formulas.
8. If you are needing to add quantities, you are done. If you are needing to remove the quantities for the order, in row 2 of the first open column (F2 in our example), enter the following formula: =-D2. Copy it all the way down.
Highlight the fields under that column with the formula and copy them. Then, select D2, right click, and choose “paste special”. Select “Values” and click OK.
Now you have a spreadsheet that you can use to upload negative quantities via csv, reducing them from your inventory.