Importing multiple categories on single products

My problem is getting the correct menu structure when importing my data.

I use Magento 1.9.2.2

I recieve my data in CSV format like this:

"sku","Cat1","Cat2","Cat3","Cat4"
"001","Plumbing","Pressfittings & pipes","Unipipe - MLC","Clutch"
"002","Tools","Handtools|Pipetools","Pipetools|Pipecutters & Scissors","Plastic scissors"
"003","Tools|Plumbing","Handtools|Pipetools|Pipes & Fittings","Pipetools|Calibration|Alupex fittings & tubes","Calibration tools|Tools  for alupex"

I have made a small program to strip away the "|" and what comes after, so that:

"002","Tools","Handtools|Pipetools","Pipetools|Pipecutters & Scissors","Plastic scissors"

becomes:

"002","Tools","Handtools","Pipetools","Plastic scissors"

But I would love to create all the underlaying categories, so I also get this for sku 002:

"002","Tools","Pipetools","Pipecutters & Scissors","Plastic scissors"

I believe that the structure is used by Magento somehow, but I have a hard time figuring out how to import it.

I have tried normal import for Magento after creating the categories manually, and this did not work. I have also tried creating them with Magmi, but I cannot get Magmi to work with the multiple main and subcategories either.

Has anyone seen this data format and have a clue in the right direction, on how to get it imported to a menu structure?

I have 2500 main and subcategories all together, so manually creating them simply wont work.

Ideas, questions or comments are welcome! :)

Solution

The MAGMI 'on-the-fly category importer' will certainly do this for you. I recommend you read the instructions on the MAGMI wiki very very carefully: all the information you need is there but I frequently find I have to double-check and re-read the details and examples on the MAGMI wiki.

MAGMI instructions for download install and running : http://wiki.magmi.org/index.php?title=Main_Page#Download_and_Install_Magmi

MAGMI instructions for on-the-fly category importer : http://wiki.magmi.org/index.php?title=On_the_fly_category_creator/importer

If you haven't used MAGMI before then work with a simple CSV to start with to ensure your install is set up correctly and you are able to import a csv that creates a simple product.

I think the main challenge you face is converting the CSV file format described in your question into a CSV format that MAGMI can work with. Alternatively you can write code in MAGMI to convert the CSV data but I think that might be overly complicated if this is a one-off process.

I'm not sure that I follow the syntax of your CSV example but if the first string is always a top category and then the pipe symbol means 'subcategory follows' then for

you seek to make a MAGMI CSV like this

"sku","categories" "003","Tools::1::1::1/Plumbing::1::0::1;;Handtools::1::1::1/Pipetools::1::0::1/Pipes & Fittings::1::0::1;;Pipetools::1::1::1/Calibration::1::0::1/Alupex fittings & tubes::1::0::1;;Calibration tools::1::1::1/Tools for alupex::1::0::1"

The obscure ::1::1::1 indicates is_active, is_anchor and include_in_menu respectively and can be omitted.

It's none of my business but I am deeply concerned by the two spaces in the subcategory label 'Tools for alupex' (don't start me on the lower-case Alupex or AluPEX)