Query for customer attribute data from Magento database to a csv file

This query will export customer attribute data from Magento database to a csv file.
The csv file will be export to /tmp/custAtts.csv nix like systems, modify this file location as you needed.

 
SELECT ce.*, ea.attribute_code, 
    CASE ea.backend_type 
       WHEN 'datetime' THEN ce_datetime.value
       WHEN 'decimal' THEN ce_decimal.value
	   WHEN 'int' THEN ce_int.value
       WHEN 'text' THEN ce_text.value
	   WHEN 'varchar' THEN ce_varchar.value
	   ELSE NULL
    END AS value
FROM customer_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN customer_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN customer_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'
LEFT JOIN customer_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN customer_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN customer_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
INTO OUTFILE "/tmp/custAtts.csv"
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

Another way to export customer attribute data is through the Magento admin panel.
Sign in to Magento admin panel.

Export all customer data, this will download a csv file to your download folder.
System -> Import/Export -> Export -> Select Customers for Entity Type -> Select CSV for Export File Format> -> Click Continue

Export through Dataflow Profile, this will create a csv file in MagentoHOME/var/export/
System -> Import/Export -> Dataflow Profile -> Select Export Customers -> Click Run Profile on the top left menu -> Click Run Profile in Popup

Search within Codexpedia

Custom Search

Search the entire web

Custom Search