Magento export/import category product sort order

Question

I am looking for an easy way to be able to quickly update the product sort order on any category page via export or import. I have looked into several extensions but have been limited due to the issue of having 1000's products per category which by default would result in a CSV with over 150,000 characters in a particular cell. Even if I do find an extension it will take about the same amount of time to organize the data for import as if I was to organize data for a direct import into a database via a correctly formatted CSV (I dare say!).

I am familiar with MySQL and phpMyAdmin and I am very comfortable with exporting and importing directly into it, and even more comfortable with triple checking data before importing for precise characters, etc, but I am not so familiar with Magento's database handling and the indexing.

Is it viable to export the "catalog_category_product" table and amend it for re-import and does anyone know if there would be any other tables involved in this? Or which tables? And any further steps apart from a backup(!).

Answer 1

I don't like answering my own questions, but here goes! Just a different approach to sorting the products and making it more manage-able helped. My steps:

1. Disable the category sort order's ordering on the front end view (remove "position" from the sort order thanks to this nice post [http://prattski.com/2009/11/30/magento-how-to-remove-the-position-sort-option/][1])
2. Add a new Attribute for all products not visible/search-able etc, just sort-able and give that a label such as "Default".

The new attribute is easily changed along with all the other product data via magento's import/export data-flow profiles. The only set back is to have a reminder that if you update your theme, to go and repeat step 1 above.