small glitch while generating csv with newline character in php

Question

I am simply generating a csv file based on data stored in a mysql table. The generated csv, when opened in excel, seems mostly ok, but whenever it has a newline character, excel puts the data on a new row. Any idea how to prevent that?

Sample data

line 1 some data
another data

CSV generation code:

header("Content-Type: text/csv; charset=UTF-8");
header("Content-Disposition: attachment; filename=\"".$MyFileName."\"");
$filename = $MyFileName;
$handle = fopen("temp_files/".$filename, "r");
$contents = fread($handle, filesize("temp_files/".$filename));
fclose($handle);
echo $contents;  
exit;

content snippet I used to get rid of new line(didn't work):

$pack_inst = str_replace(',',' ',$get_data->fields['pack_instruction']);
        $pack_inst = str_replace('\n',' ',$pack_inst);
        $pack_inst = str_replace('\r',' ',$pack_inst);
        $pack_inst = str_replace('\r\n',' ',$pack_inst);
        $pack_inst = str_replace('<br>',' ',$pack_inst);
        $pack_inst = str_replace('<br/>',' ',$pack_inst);
        $pack_inst = str_replace(PHP_EOL, '', $pack_inst);
        $pattern = '(?:[ \t\n\r\x0B\x00\x{A0}\x{AD}\x{2000}-\x{200F}\x{201F}\x{202F}\x{3000}\x{FEFF}]|&nbsp;|<br\s*\/?>)+';
        $pack_inst = preg_replace('/^' . $pattern . '|' . $pattern . '$/u', ' ', $pack_inst);
        $content .=','.$pack_inst;

Have you tried wrapping the strings containing a new-line character around double quotes? — Pateman, May 28 '12 at 08:21
might help: http://stackoverflow.com/questions/1241220/generating-csv-file-for-excel-how-to-have-a-newline-inside-a-value — mindandmedia, May 28 '12 at 08:21

score 2 · Accepted Answer · edited Oct 07 '21 at 05:57

According to RFC 4180, if a column's content contains the row delimiter (\r\n), the column delimiter (,) or the string delimiter (") then you must enclose the content inside double quotes ". When you do that, you must escape all " characters inside the content by preceding them with another ". So the following CSV content:

1: OK,2: this "might" work but not recommended,"3: new
line","4: comma, and text","5: new
line and ""double"" double quotes"
1: Line 2

Will produce 2 rows of CSV data, first one containing 5 columns.

Having said that, have a look at fputcsv() function. It will handle most gory details for you.

DaveRandom · Answer 2 · 2012-05-28T09:03:38.000

What you show is not the CSV generation code, it is simply the code that you have used to force a download to the browser. Regardless, the function that you need to sort this out is fputcsv(), which will automatically consider all sorts of edge cases that any code you write to convert tabular data to CSV format will likely not consider.

You say you are basing this on data in MySQL table, here is a basic framework for creating the CSV file, assuming the MySQLi extension used in a procedural manner:

<?php

  // Connect to database and generate file name here
  $fileName = 'file.csv';

  // Get the data from the database
  $query = "
    SELECT *
    FROM table_name
    WHERE some_column = 'Some Value'
    ORDER BY column_name
  ";
  if (!$result = mysqli_query($db, $query)) {
    // The query failed
    // You may want to handle this with a more meaningful error message
    header('HTTP/1.1 500 Internal Server Error');
    exit;
  } else if (!mysqli_num_rows($result)) {
    // The query returned no results
    // You may want to handle this with a more meaningful error message
    header('HTTP/1.1 404 Not Found');
    exit;
  }

  // Create a temporary file pointer for storing the CSV file
  $tmpFP = fopen('php://temp', 'w+');

  // We'll keep track of how much data we write to the file
  $fileLength = 0;

  // Create a column head row and write first row to file
  $firstRow = mysqli_fetch_assoc($result);
  $fileLength += fputcsv($tmpFP, array_keys($firstRow));
  $fileLength += fputcsv($tmpFP, array_values($firstRow));

  // Write the rest of the rows to the file
  while ($row = mysqli_fetch_row($result)) {
    $fileLength += fputcsv($tmpFP, $row);
  }

  // Send the download headers
  header('Content-Type: text/csv; charset=UTF-8');
  header('Content-Disposition: attachment; filename="'.$fileName.'"');
  header('Content-Length: '.$fileLength);

  // Free some unnecessary memory we are using
  // The data might take a while to transfer to the client
  mysqli_free_result($result);
  unset($query, $result, $firstRow, $row, $fileName, $fileLength);

  // Prevent timeouts on slow networks/large files
  set_time_limit(0);

  // Place the file pointer back at the beginning
  rewind(tmpFP);

  // Serve the file download
  fpassthru($tmpFP);

  // Close the file pointer
  fclose($tmpFP);

  // ...and we're done
  exit;

small glitch while generating csv with newline character in php

2 Answers2