Tuesday, April 17, 2012

Download mySQL table as CSV for Excel

I was given a short task today. I had give an employee a way to view and use information from a database table.

We use mySQL and the server running the mySQL also runs Apache and has web-access. The decision to use PHP was almost instantaneous.

I created a php page that made the query and displayed the data, but I wanted to make the task of retrieving the data as easy as possible. Not to mention with least chance of human errors. So I searched for a way to dump the table into a CSV file and found this short and easy to use piece of code by Luke Maciak from Terminally Incoherent

    <?php
    // (c) 2007 Lukasz Grzegorz Maciak
    // Code Snippet ID: 5e8cf864-db67-4a30-9857-2ce8f3fcb1d5
    
    // takes a database resource returned by a query
    function csv_from_mysql_resource($resource)
    {
        $output = "";
        $headers_printed = false;
    
        while($row = mysql_fetch_array($resource, MYSQL_ASSOC))
        {
            // print out column names as the first row
            if(!$headers_printed)
            {
                $output .= join(',', array_keys($row)) ."\n";
                $headers_printed = true;
            }
    
            // remove newlines from all the fields and
            // surround them with quote marks
            foreach ($row as &$value)
            {
                $value = str_replace("\r\n", "", $value);
                $value = "\"" . $value . "\"";
            }
    
            $output .= join(',', $row)."\n";
    
        }
    
        // set the headers
        $size_in_bytes = strlen($output);
        header("Content-type: application/vnd.ms-excel");
        header("Content-disposition:  attachment; filename=export_data.csv; size=$size_in_bytes");
    
        // send output
        print $output;
        exit;
    }
    
    ?>

The function receives the result from your mysql_query function and tells the browser the that this is an excel CSV file. The data from the table opens directly in the most comfortable environment for many employees - MS Excel!

Important note: Since the function uses the header to tell the browser this is a CSV file you must make sure it is called before anything is written to the page.

I combined this with a simple form to authenticate that only employees with password can download the data and the result was very similar to the following:

<?php
// takes a database resource returned by a query
function csv_from_mysql_resource($resource)
{
    $output = "";
    $headers_printed = false;

    while($row = mysql_fetch_array($resource, MYSQL_ASSOC))
    {
        // print out column names as the first row
        if(!$headers_printed)
        {
            $output .= join(',', array_keys($row)) ."\n";
            $headers_printed = true;
        }

        // remove newlines from all the fields and
        // surround them with quote marks
        foreach ($row as &$value)
        {
            $value = str_replace("\r\n", "", $value);
            $value = "\"" . $value . "\"";
        }

        $output .= join(',', $row)."\n";

    }

    // set the headers
    $size_in_bytes = strlen($output);
    header("Content-type: application/vnd.ms-excel");
    header("Content-disposition:  attachment; filename=export_data.csv; size=$size_in_bytes");

    // send output
    print $output;
    exit;
}

$authentication = $_POST['authentication'];
if ($authentication == "someboguspassword")
{
    $con = mysql_connect("some_host","database_name","database_password");
   
    if (!$con)
    {
        die();
    }
   
    mysql_select_db("database_name", $con);
   
    $query = "Select * from table_name";   
    $result = mysql_query( $query);
   
    csv_from_mysql_resource($result);
   
    mysql_close($con);
       
}
else
{ ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Get Data</title>
    </head>
    <body>
        <form action="" method="post">
        Password: <input type="password" name="authentication" />
        <input type="submit" />
        </form>
    </body>
</html>
<?php
}
?>

No comments:

Post a Comment