Today I gave up on Magento. It’s a powerful piece of software but it’s still pretty rough around the edges, and the UI and architecture makes it a pain to dive in and debug if something goes wrong. It’s built on Zend, so someone who has spent more time with Zend than I have would probably have an easier go of it.
Anyway, I’m moving over to PrestaShop, and don’t want to lose all my customer and order information. Since I managed to trash my Magento installation, I’m migrating the data over manually via an exciting series of MySQL queries. I’m posting them here in case anyone else needs them.
This data is then imported into PrestaShop using the built in import tool. They have a fairly easy to use interface for assigning columns in the CSV to various PrestaShop information (name, address, etc).
Getting the customer’s ID, name, and email address:
SELECT DISTINCT ce.entity_id AS b, email, 'default_password', ( SELECT value FROM customer_entity_varchar WHERE attribute_id =7 AND customer_entity_varchar.entity_id = b ) AS l_name, ( SELECT value FROM customer_entity_varchar WHERE attribute_id =5 AND customer_entity_varchar.entity_id = b ) AS f_name, 1 FROM `customer_entity` AS ce JOIN customer_entity_varchar AS cev ON ce.entity_id = cev.entity_id WHERE 1
You’ll notice I select the string ‘default_password’. This is just to generate a column of dummy password data. I haven’t thought of any creative ways to migrate the password data, and instead am just resetting it. The downside is that users will have to request a new password in order to log in. You should not use default_password as the actual string, for reasons I hope are obvious.
Get the address books:
SELECT DISTINCT 'Home', cae.entity_id AS b, (select email from customer_entity where entity_id = parent_id) as email, ( SELECT code FROM customer_address_entity_int as mm1 join directory_country_region as mm2 on mm1.value = mm2.region_id WHERE mm1.attribute_id =27 AND mm1.entity_id = b ) AS state, ( SELECT value FROM customer_address_entity_varchar WHERE attribute_id =25 AND entity_id = b ) AS country, ( SELECT value FROM customer_address_entity_varchar WHERE attribute_id =24 AND entity_id = b ) AS city, ( SELECT value FROM customer_address_entity_varchar WHERE attribute_id =18 AND entity_id = b ) AS f_name, ( SELECT value FROM customer_address_entity_varchar WHERE attribute_id =20 AND entity_id = b ) AS l_name, (SELECT value FROM customer_address_entity_text WHERE attribute_id =23 AND entity_id = b ) AS addre1, (SELECT value FROM customer_address_entity_varchar WHERE attribute_id =28 AND entity_id = b ) AS postcode FROM `customer_address_entity` AS cae JOIN customer_address_entity_varchar AS caev ON cae.entity_id = caev.entity_id WHERE 1
Getting the order data over is another beast, one which I’ll tackle another day. There’s a convenient importer for products, but unfortunately the individual order data will have to be migrated painfully via SQL.
5 thoughts on “Importing Data from Magento to PrestaShop”
Thanks for sharing this. If it works you are my new hero.
Is it important to know what version of Magento or MySQL you were using?
Actually, I see now that this is customer data. I have done very little business with my Magento site (just getting rolling) but I have decided to abandon Magento (similar reasons, no doubt) and go with PrestaShop. What I really need to migrate is my products/catagories etc.
Any tips or places you can point me for into on doing this?
yes, i would really know me too what i need to migrate my products/categories from a Magento 1.2 to recent Prestashop vrs…
I know this post is old but i wanted to add something :
you should use the mysql function MD5(RAND()) to generate a new password instead of using the same password for each customers….
And sorry for my bad english, i’m french !
That’s a great migration solution! Thanks!
I guess I’m a bit too late with the news, but just in case someone is still trying to manage with orders transfer via SQL… You could try some sort of automated service as an alternative (cart2cart or something like that). It might cost a few bucks, but it’s a lot more easier and reliable than doing it via SQL after all.