Category: Software

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.