CSV Export - edit - import - Bulk update. How to guide.

CSV Export - edit - import - Bulk update. How to guide.

Although the bulk edit feature of My Products is extremely useful, there are times you may want to do something that's not there eg globally update prices by a % or $ amount, or simply sort your items by price and manipulate them manually.

When Trademe changed to the new Shipping Templates I needed to bulk edit mine.
Using procedure below I did this to several hundred listings in just a few minutes, so thought I'd share in case anyone else wants to convert a whole lot of MQL MP products/listings to the new shipping templates in bulk rather than one by one and hasn't used the csv export/import procedure before..
This may seem lengthy, but it's quite quick. Once you have done this, it give  you some confidence to maybe try other bulk edits to save you lots of time.
I've already had sales that have used the new shipping templates after I did this.

This is the procedure for Shipping Templates, but the export/edit/import process is the same. This is just one application for bulk editing. There are lots more eg manipulating prices, editing or adding SKUs.

1. Create new templates using the Shipping tab in My Products.
For trouble-free handling and to differentiate betweek old templates and new, name each template without spaces. eg new_3.95 , new_5.40 etc. You don't have to use the underline but it helps to clarify as MP shows old and new templates together in products.

2. In MP, clear all filters so all products are displayed. then click the Select all box here....

3. Click the Export button as here...

4. Depending on your browser setup, choose to save the exported csv file somewhere on your computer you remember eg your Documents folder.

5. For easy trouble-free editing of CSV files, I have always used Openoffice, not Excel, not Libreoffice. Oppenoffice is a free to use office application including Calc, which is their spreadsheet and csv handler. You can download Openoffice by searching on google. I have used the others mentioned, but have had occasional format problems so stick to Openoffice.

6. Locate the csv file where you saved it in step 4. and right-click to then choose Open With Oppenoffice Calc. You may be able to simply double click the csv to open it also, but you may have other programs associated with csv, so the right click "Open with" gives you the choice.
Open office will open and bring up the csv import tool.
!IMPORTANT - Pay particular attention to the settings below as you MUST change settings so all the options look like this, ie Character Set, Separated by Comma, Text delimiter " and all other boxes unticked.
Click ok to import.

7. You'll see a screen similar to this. Not much info and too much white sapce, so click the select all box as below, so everything is highlighted as in 2nd pic. You can also choose Select all from the Edit menu.

Then, with everything highlighted like here

, click the Format menu and choose Row, then Row height. Change row height to 0.5cm or 0.45cm so more is shown on screen.

8. From the menu choose File Save as to save a copy before you make any edits. Give it a memorable name eg 14012022-newtemplates. You'll get a warning for format - choose Keep Current Format as here.

9. While everything is highlighted still, click on menu - Data - Sort, then click the Options tab and put a tick in the box that says Range contains column labels, then click back to the Sort criteria tab.
In the Sort by dropdown, choose is_sold_in_multiple_quantities and click the Decending option.
Then BEFORE you click ok, in the secondary sort dropdown choose Delivery price (either ascending or descending doesn't matter). Now click ok.

10. Click in to any cell and move to the right till you get to the is_sold_in_multiple_quantities column. You will see the top items will be TRUE ie these ones are MQLs so can use the new shipping templates. Non MQLs will be further down the list and will in this column say FALSE, so those ones are non MQL auctions that can not use the new templates.
Scroll down the list until you come to the last row that has TRUE in it and take not of the number of the row at left. This will be needed as an indicator when we start editing. Scroll back up to top so you are in row 2 (row Seller says in the auction... being the column header labels).

11. So we can see the full shipping options we need to shrink the width of the column. To do this go to Menu - Format - Column - Width. Change width to 10cm and click ok. If you can now see the column to the right which is the payment_bank_deposit column, then all good, else change column width to another number. You can adjust this at any time to give you a better view of the columns and fine tune using the - and + buttons or the slider bottom right, or you can grab the line at the top beween AG and AH that separates the columns and hold and drag that to adjust the column width.

12. In the cell on row 2, you will see the current shipping options that are setup for that product, or, if you have an existing old template, you will see the name of that template there. As we have sorted by delivery price, you should see items that share the same or similar options under each other to make editing easier.

13. Editing. If for example you have 10 rows that have the same options, go to the first one, and type in the name of the new shipping template you want to replace the existing option with.
Go to the next one and repeat. Once you have chenaged 2 and you have more to make the same, you can drag over the 2 cells that contain the new template name, then release mouse button and while the 2 of them are highlighted, drag down the small black square to the bottom right of the highlighted cells. Drag down to all the cells you want to change, then release mouse button. You'll then see the name of the new template repeated down in those cells.
If you've made a mistake, Menu - edit - undo, else continue on with your edits in this same manor.
You may want to move to the left now and again to check what products you are changing shipping on, then just return to this column to continue. Keep checking the row number you noted down earlier of the last MQL product to edit.

14. Once finished with all your edits, Menu, File Save, or save as if you want to take yet another copy in case you want to go back to the previous one. Close Openoffice.

15. Updating products in My Products live on Trademe.
We now need to import the new changes to MP.
Go to MP page on Trademe and click on Import photos & products just to the right of the Create a new product blue button.

16. Click on Step 2 - Import CSV file button. A chooser box will come up asking you which csv file to upload. Locate and choose the latest edited file you want to import.
The import process will begin.
Once the import is finished, the changes will show in your My Products products.
The changes are not in your live listings yet.
Click in to a couple of products and check they are correct with the new shipping template names.

17. If you are happy to simply leave current listings as they are until they close and relist with the new templates, then you need do no more.

Updating live listings.
18. If you want to upgrade all your current listings to the new templates you have set up in the products, you can do this. This is also the way to freshen live listings from templates if you have made any other edits.
The most simple way to do this is to use the bulk edit - listing footer method.
If you currently have a common listing footer shared across all your listings, then it's a simple process to clear all filters in MP, Select all, Choose Bulk edit then Listing footer in the dropdown as here...

All you need to edit is to just add even a comma or a full stop, if you have no other edits to make.
If you don't currently use a listing footer, it might be a good time to start, else just put a single full stop in.
So, after you have brought up the Edit Listing footer, click Edit.
Make your changes, Save, then make sure to PUT A TICK in the box Update current listings for selected products (where possible).
Note - Listings with bids, or listings that have longer than the allowed number of chars will not be changed. Any failures will be shown on screen and you can manually update those if reqd or leave them to just update as the close and relist.

Finished.

If you have done something majorly wrong in your editing and updating process, you can always re-import the very first csv you exported from the beginning of this process to revert your products back how they were, but anything you have manually changed in MP or sales made in the interim will need to be adjusted manually again.

That about does it.

If anyone needs help on say how to edit prices globally by a % or add on, feel free to ask.