2

I am looking for a query with all the customer info that I can dump into a CSV from SQL. I am working with a broken site and the customer wants all the customer info.

Please don't write one yourself as I can do that, just seeing if anyone has one already written.

  • Let me add that I have my data base on a generic version of Magento already. When I go to dataflow I get an error on a node when exporting all the customers groups, and when I choose the customer group that everyone is in I get no customers. – brentwpeterson Mar 11 '14 at 12:48
  • Did you find a solution to this? Please share it or mark one of the answers as solution :-) – Fabian Blechschmidt Apr 10 '14 at 9:49
2

Just dump the SQL from

Mage::getResourceModel('customer/customer_collection')->addAttributeToSelect('*')->load(true);
2

Have you tried using the Magento dataflow exporter for customers? Its in the admin interface under SYSTEM -> IMPORT/EXPORT -> DATAFLOW PROFILES. That will give you a CSV with all the customers and their details. But if you're looking to get all the orders for the customers as well as their details, you might want to try a free module - something like Blue Jaleppeno will do the trick: http://www.magentocommerce.com/magento-connect/blue-jalappeno-order-export.html

  • Yes this is what I am using. I get a error on a node when I choose all groups and I get 0 customers out of 1015 when I choose the group that all the customers are in – brentwpeterson Mar 11 '14 at 12:47
1

If the installation is broken. Copy the database, and run the database against a "vanilla install" of the same version of magento. Then use the typical dataflow profiles or Magmi or another tool to export your data you need.

Writing SQL to export data from Magento is very complex, and requires much more work than just fixing the problem using the above method. I would advise against writing SQL by hand for this task.

  • Did you take a look at the customer tables, and see if the schema matches the original? Another option would be to pull just the customer tables into a fresh and clean install of the same version of magento, and then again exporting. This would be done by exporting the customer_ tables from mysql and back in via mysql. – Aepod Mar 11 '14 at 13:29
1

Here y'go:

$customerCollection = Mage::getResourceModel('customer/customer_collection')
    ->addAttributeToSelect('*')
    ->load(true);

$customerSqlQuery = $customerCollection
    ->getSelect()
    ->__toString();

That gives us the following SQL query:

SELECT `e`.* FROM `customer_entity` AS `e` WHERE (`e`.`entity_type_id` = '1');

That what you needed?

  • Sadly this SQL is wrong, because magento builts the SQL for eav collections in load() and therefore getSelect() gives you not the entire query – Fabian Blechschmidt May 30 '14 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.