Direct SQL query to transfer Magento 1.x customer data via CSV into Magento 2

Basicly see the title.

Googled to transfer customer data (address and passwords too) from magento 1.x into Magento 2, without using the migrate tool. Just a direct DB output into a CSV to be able to upload it into Magento 2.

But found only plugins which cost around $70. But I’m cheap so I build it myself and if anyone wants to use it in the future he/she has it now too:

SELECT "email", "_website", "_store", "confirmation", "created_at", "created_in", "disable_auto_group_change", "dob", "firstname", "gender", "group_id", "lastname", "middlename", "password_hash", "prefix", "rp_token", "rp_token_created_at", "store_id", "suffix", "taxvat", "website_id", "password", "_address_city", "_address_company", "_address_country_id", "_address_fax", "_address_firstname", "_address_lastname", "_address_middlename", "_address_postcode", "_address_prefix", "_address_region", "_address_street", "_address_suffix", "_address_telephone", "_address_vat_id", "_address_default_billing_", "_address_default_shipping_" UNION ALL select email, "base", "admin", "", a.created_at, l.value, "", "", m.value, "", group_id, n.value, "", concat(o.value,":0"), "", "", "", store_id, "", "", website_id, "", c.value, "", d.value, "", e.value, f.value, "", g.value, "", if(h.value is NULL,"",h.value), i.value, "", j.value, "", 1, 1 from customer_entity a join customer_address_entity b on a.entity_id=b.parent_id left join customer_address_entity_varchar c on b.entity_id=c.entity_id and c.attribute_id=26 left join customer_address_entity_varchar d on b.entity_id=d.entity_id and d.attribute_id=27 left join customer_address_entity_varchar e on b.entity_id=e.entity_id and e.attribute_id=20 left join customer_address_entity_varchar f on b.entity_id=f.entity_id and f.attribute_id=22 left join customer_address_entity_varchar g on b.entity_id=g.entity_id and g.attribute_id=30 left join customer_address_entity_varchar h on b.entity_id=h.entity_id and h.attribute_id=28 left join customer_address_entity_text i on b.entity_id=i.entity_id and i.attribute_id=25 left join customer_address_entity_varchar j on b.entity_id=j.entity_id and j.attribute_id=31 left join customer_address_entity_int k on b.entity_id=k.entity_id and k.attribute_id=29 left join customer_entity_varchar l on a.entity_id=l.entity_id and l.attribute_id=3 left join customer_entity_varchar m on a.entity_id=m.entity_id and m.attribute_id=5 left join customer_entity_varchar n on a.entity_id=n.entity_id and n.attribute_id=7 left join customer_entity_varchar o on a.entity_id=o.entity_id and o.attribute_id=12 INTO OUTFILE '/tmp/customers_combined.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n'; 

Hope you can live with no comments, if one needs other fields to which I left blank (“”) here, they are most likely in one of those customer_address_entity_*/customer_entity_* tables where you have to lookup up the attribute_id and add it to the query.

submitted by /u/JealousEntrepreneur
[link] [comments]