Formatting the csv file from php generator

Question

I got a function that creates a cvs file from a sql query.

function query_to_csv($db_conn, $query, $filename, $attachment = false, $headers = true) {

        if($attachment) {
            // send response headers to the browser
            header( 'Content-Type: text/csv' );
            header( 'Content-Disposition: attachment;filename='.$filename);
            $fp = fopen('php://output', 'w');
        } else {
            $fp = fopen($filename, 'w');
        }

        $result = mysql_query($query, $db_conn) or die( mysql_error( $db_conn ) );

        if($headers) {
            // output header row (if at least one row exists)
            $row = mysql_fetch_assoc($result);
            if($row) {
                fputcsv($fp, array_keys($row));
                // reset pointer back to beginning
                mysql_data_seek($result, 0);
            }
        }

        while($row = mysql_fetch_assoc($result)) {
            fputcsv($fp, $row);
        }

        fclose($fp);
    }

The thing is that the generated file looks like this

A1                                      | B1
2014-10-30,333333333333333333333334

How can I split that the date is in A1 and the number in B2? It would be nice if I can also name my headers (A1 to Date..)

Long live excel, you can't just open a csv file (anymore), you have to import it. Only then it will display correctly. — Flip Vernooij, Nov 03 '14 at 12:53
You need to know that your basic csv reader (like Microsoft Excel or Open Office Excel, ...) will notice the first "line" of your csv as a header. So you basically just have to put your file like this : `$csv = "A1;B1;\r2014-10-30;3333333334;\r2011-11-12;576666;";` etc... — Anwar, Nov 03 '14 at 13:17
Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Nov 03 '14 at 13:41

score 0 · Answer 1 · answered Nov 03 '14 at 13:42

By default fputcsv use a comma as delimiter, whereas Excel expects a semicolon delimiter. You can modify your function by adding the semicolon delimiter as third parameter to the fputcsv function to get "correct" excel csv files :

function query_to_csv($db_conn, $query, $filename, $attachment = false, $headers = true) {

        if($attachment) {
            // send response headers to the browser
            header( 'Content-Type: text/csv' );
            header( 'Content-Disposition: attachment;filename='.$filename);
            $fp = fopen('php://output', 'w');
        } else {
            $fp = fopen($filename, 'w');
        }

        $result = mysql_query($query, $db_conn) or die( mysql_error( $db_conn ) );

        if($headers) {
            // output header row (if at least one row exists)
            $row = mysql_fetch_assoc($result);
            if($row) {
                fputcsv($fp, array_keys($row), ';');
                // reset pointer back to beginning
                mysql_data_seek($result, 0);
            }
        }

        while($row = mysql_fetch_assoc($result)) {
            fputcsv($fp, $row, ';');
        }

        fclose($fp);
    }

Formatting the csv file from php generator

1 Answers1