CSV export without MYSQL FILE functions

Question

i'd like to make an export into CSV format, but the mutualised host i use has deactivate the FILE functions in mysql.

I did a simple SELECT and then a fopen and fwrite with PHP.

The problem is that in fields there is carriage returns or double quotes.

How to keep them and build a correct csv file?

Thanks a lot.

There's a `fputcsv` function, if your host hasn't disabled that - http://php.net/manual/en/function.fputcsv.php — andrewsi, Jun 18 '12 at 15:44

score 0 · Answer 1 · answered Jun 18 '12 at 15:50

You can download file.csv by use header()

Output everything you want (base on csv format) after header function, for example:

    $filename = "output";
    header('Content-Type: text/csv');
    header('Content-disposition: attachment;'.$filename.'=.csv');

    $separate = ","; //or ;
    $endline = "\r\n";
    foreach($data as $key => $item)
    {
        echo $key.$separate.$value.$endline;
    }

score 0 · Accepted Answer · edited Jun 18 '12 at 15:59

To build a best CSV. you can do following way.

   $filename ='data.csv';
   $csv_terminated = "\n";
   $csv_separator = ",";
   $csv_enclosed = '"';
   $csv_escaped = "\\";

$results  = array('1','2','3');// value

    $schema_insert = '';

    $header = array('a','b','c');// header


    for ($i = 0; $i< count($header); $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes($header[$i])) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for

    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;

    // Format the data
    for($i=0;$i<count($results);$i++)
    {
        $row = $results[$i];
        $schema_insert = '';
        for ($j = 0; $j < count($header); $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {

                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $schema_insert .= $csv_enclosed .
                    str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            } else
            {
                $schema_insert .= 'NULL';
            }

            if ($j < count($header) - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for

        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while

    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose <img src="http://thetechnofreak.com/wp-includes/images/smilies/icon_wink.gif" alt=";)" class="wp-smiley">
    header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    //header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;

Notice that, + when you make enclosed for description which has html code , you should use double quote. + Empty value --> Change to Null text or Zero value

They will make your CSV better.

Hiya welcome on SO. Please stop tagging your answers with "spam". It is frown upon. Please also read [this post](http://meta.stackexchange.com/questions/5029/are-taglines-signatures-disallowed) about this topic. Enjoy your further stay here. — PeeHaa, Jun 18 '12 at 16:30

score 0 · Answer 3 · answered Jun 18 '12 at 16:10

protected function getCsv( $fileName, array $data )
    {
        // alot of headers here, make force download work on IE8 over SSL
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Cache-Control: private",false);
        header("Content-Type: application/octet-stream");
        header('Content-Disposition: attachment; filename="'.$fileName.'"');
        header("Content-Transfer-Encoding: binary"); 

        // Joe Green says:
        // based on http://www.php.net/manual/en/function.fputcsv.php#100033

        $outStream = fopen("php://output", "r+");
        function __getCsv( &$vals, $key, $filehandler ) {
            fputcsv( $filehandler, $vals, ',', '"');
        }
        array_walk( $data, '__getCsv', $outStream );
        fclose($outStream);
    }

fputcsv is your friend. Also, I had to refine this function to get it to this point. Some of those headers are required by IE to force the csv to open as a csv, particularly over SSL. I seem to recall it had something to do with IE8 not recognising the 'text/csv' content type in the first instance and some security features around SSL downloads in the second.

CSV export without MYSQL FILE functions

3 Answers3