>How to Export Customers Data in CSV File by Php Script

How to Export Customers Data in CSV File by Php Script

In Magento 1, you can follow customers data by going to Customers/Manage Customers or making simply statistical table of customer data in CSV file. However you can only show some data fields in default grid. So we will show you the simple way to export advance customer data in csv file by php script without installing any module.

By this way, you can create csv file with any data of customer. In this case, we’ll export some data from a time period: Customer ID, Email, Full name, Create at, Billing city, Shipping city, Total value of the order.

1. Initialize Magento by declaring the Mage.php

In file php script, declare the Mage php:

define('MAGENTOROOT', realpath(dirname(__FILE__)));
require_once(MAGENTOROOT.'/app/Mage.php');
umask(0); 
Mage::app('default');

2. Create data collection with all attributes selected and filter in a period time

$dateFrom = '2015-11-01 00:00:00';
 $dateTo = '2017-11-16 23:59:59';
$customerCollection = Mage::getModel('customer/customer')->getCollection(); 
$customerCollection->addAttributeToSelect('*');
$customerCollection->addAttributeToFilter('created_at', array(
        'from' => $dateFrom,
        'to' => $dateTo,
    ));

3. Define type of file and set file name

header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=export-customer.csv");
// Disable caching
header("Cache-Control: no-cache, no-store, must-revalidate"); // HTTP 1.1
header("Pragma: no-cache"); // HTTP 1.0
header("Expires: 0"); // Proxies
$fp = fopen('php://output', 'w');
    ‘'php://output’ is method to download file in local, not save to root folder.

4. Use function fputcsv to format lines and write it to an open file

// Create first line title of csv file
$csvHeader = array("Customer ID","Email","Full Name","Create At","Billing Address","Shipping Adress", "Total value of the order");
fputcsv( $fp, $csvHeader,",");
// Print all line with each field
foreach ($customerCollection as $customer) {
    $billing_address = Mage::getModel('customer/address')->load($customer->getDefaultBilling());
    $shipping_address = Mage::getModel('customer/address')->load($customer->getDefaultShipping());
    $customerId =  $customer->getId();
    $customerEmail = $customer->getData('email');
    $billingStreetArray = $billingAddress->getStreet();
    $billingStreet =$billingStreetArray[0];
    $shippingStreetArray = $shippingAddress->getStreet();
    $shippingStreet =$shippingStreetArray[0];
    $customerCreatedAt = $customer->getData('created_at');
    $customerFullname = $customer->getData('firstname')." ".$customer->getData('lastname');
    // Create order collection of each customer to get Total order
        $coreResource  = Mage::getModel('core/resource');
        $read           = $coreResource->getConnection('core_read');
        $sql    = 'SELECT sum(grand_total) as total FROM ' . $core_resource->getTableName('sales/order') . ' ' .
        'WHERE customer_id = ?';
        $query  = $read->query($sql,$customer->getId());        
        $result = $query->execute();
        $row = $query->fetch();    
        
        $totalOrder = array_key_exists('total', $row) ? $row['total'] : 0;
// Print customer data
 fputcsv($fp, array($customerId,$customerEmail,$customerFullname,$customerCreatedAt,$billingStreet,$shippingStreet, $totalOrder), ",");
}
fclose($fp); 

In some case, we would like to suggest you create a simple login form by getting information from database with security purpose.

if (isset($_POST['submit'])) {
if(Mage::app()->getRequest()->getParam('username')==='username' && Mage::app()->getRequest()->getParam('password')==='sRef2J8P'){
// Put all output function to this area
}else{
 echo "Invalid login or password.<br>";
 echo "Please Login Again!";
 exit();
}
}
else{
echo '<h2>Login</h2>
<form action="" method="post">
<label>UserName :>/label>
<input id="name" name="username" placeholder="username" type="text"
<label>Password :</label>
<input id="password" name="password" placeholder="**********" type="password">
<input name="submit" type="submit" value=" Login ">
<span><?php echo $error; ?></span>
</form>';
}

You must submit the login form before creating csv file.

5. The result

Finally, we created a complete csv file including customers data.

Please to download full file here: export-customer.php.

< Previous Post
Next Post >