CODEWITHSUNDEEP
phpexcelcsv
pheromix
pheromix

Reputation: 19317

Excel data are strange when MySQL column data has breaklines

I exported MySQL columns data into an excel file through the download concept using the header mechanisms. The problem is that if a column having text as its datatype has data containing breaklines like enumeration then the csv file is strange : the enumerated data are placed into different lines in the excel file. So how to make the data placed inside one cell ?

Here is the code of the download :

function downloadFile($outPut, $fileName){

        $filesize = strlen($outPut);

        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
        header("Content-type: text/enriched");
        header("Content-length: $filesize");
        header("Content-disposition: attachement; filename=$fileName");

        ob_clean();
        flush();

        print($outPut);

        exit();
    }

Upvotes: 0

Views: 156

Answers (2)

Mark Baker
Mark Baker

Reputation: 212412

A dirty method is to use a "\r\n" as the actual row ending in the CSV file; and a simple "\n" as the breakline within a cell, making sure that you quote any cell values that contain the breakline.

$fh = fopen('test.csv', 'w+');
fwrite($fh, "sep=\t" . "\r\n");
fwrite($fh, 'A' ."\t" . 'B' . "\t" . 'C' . "\r\n");
fwrite($fh, 'D' ."\t" . "\"E\nF\nG\"" . "\t" . 'H' . "\r\n");
fclose($fh);

This example uses a tab separator between cells; and shows a three-line value in cell B2

EDIT

Or using fputcsv()

$fh = fopen('test2.csv', 'w+');
fwrite($fh, "sep=\t" . PHP_EOL);
fputcsv($fh, array('A', 'B', 'C'), "\t");
fputcsv($fh, array('D', "E\nF\nG", 'H'), "\t");
fclose($fh);

Upvotes: 0

ChrisProsser
ChrisProsser

Reputation: 13088

My first suggestion would be to ensure that each of the fields you are exporting are within double quotes if they are not already, this will save a lot of formatting issues e.g. with commas within a field etc. If you are still having problems I would suggest trying to replace the newline characters with something that you will interpret as a new line (or just as space). Try this in your sql (around the field that ,ay have newline characters:

REPLACE(your_column_name,'\n',' ')

Upvotes: 1

Related Questions