Enclosing every field with double quotes in CSV file using PHP?

Question

I need to put all strings and number with double quotes in CSV file using PHP.

How can I create CSV file from PHP in all data within double quotes ?

I am using this code to generate CSV - I am using codeigniter framework

$array = array(
    array(
     (string)'XXX XX XX',
     (string)'3',
     (string)'68878353',
     (string)'',
     (string)'xxxx@xxxx.xxx.xx',
    ),
);

$this->load->helper('csv');
array_to_csv($array, 'blueform.csv'); 

Output I am getting:

"XXX XX XX",3,68878353,,xxxx@xxxx.xxx.xx

Expected Output:

"XXX XX XX","3","68878353","","xxxx@xxxx.xxx.xx"

Code of array_to_csv

if (!function_exists('array_to_csv')) {
    function array_to_csv($array, $download = "") {
        if ($download != "") {   
            header('Content-Type: application/csv');
            header('Content-Disposition: attachement; filename="' . $download . '"');
        }

        ob_start();
        $f = fopen('php://output', 'w') or show_error("Can't open php://output");
        $n = 0;
        foreach ($array as $line) {
            $n++;
            if (!fputcsv($f, $line)) {
                show_error("Can't write line $n: $line");
            }
        }
        fclose($f) or show_error("Can't close php://output");
        $str = ob_get_contents();
        ob_end_clean();

        if ($download == "") {
            return $str;
        } else {
            echo $str;
        }
    }
}

Thank you in advance

Answer 1

When building a CSV in PHP, you should use the fputcsv function available from PHP 5

From the documentation, there's the following parameters:

int fputcsv ( resource $handle , array $fields [, string $delimiter = "," [, string $enclosure = '"' [, string $escape_char = "\" ]]] )

In that you have:

• the file resource you're writing to
• The data to put into the CSV
• The delimiter (the commas usually)
• The string enclosure (this is the bit you want for your quotes)
• Escape string (so if your data contains the enclosure character, you can escape it to avoid it looking like a new field)

As the defaults for that are , for the delimiter, and " for the enclosure, you'll not need to add more. That will correctly cover the strings. The numbers are a different matter. There's a work around on this SO question which I found after typing most of this and then having issues with numbers still :

Not happy with this solution but it is what I did and worked. The idea is to set an empty char as enclosure character on fputcsv and add some quotes on every element of your array.

function encodeFunc($value) {
    return "\"$value\"";
}

fputcsv($handler, array_map(encodeFunc, $array), ',', chr(0));

Answer 2

Concat the string with this '"""';

Example: '"""' . {{String}} . '"""';

This should work, it worked for me. This is a way to enclose the string.

Answer 3

I m using CSV helper here.

For Generating result from db query for ex.

$sTable = "MY_TABLE_NAME";

$query = $this->db->get($sTable);

$this->load->helper('csv');

query_to_csv($query, TRUE, 'records.csv');

In CSV Helper onLine number 65 There is the code for query_to_csv.

here is csv_helper.php.

I made a small edit there if you want everything with double inverted comma.

    <?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

// ------------------------------------------------------------------------

/**
 * CSV Helpers
 * Inspiration from PHP Cookbook by David Sklar and Adam Trachtenberg
 * 
 * @author      Jérôme Jaglale
 * @link        http://maestric.com/en/doc/php/codeigniter_csv
 */

// ------------------------------------------------------------------------

/**
 * Array to CSV
 *
 * download == "" -> return CSV string
 * download == "toto.csv" -> download file toto.csv
 */
if ( ! function_exists('array_to_csv'))
{
    function array_to_csv($array, $download = "")
    {
        if ($download != "")
        {   
            header('Content-Type: application/csv');
            header('Content-Disposition: attachement; filename="' . $download . '"');
        }       

        ob_start();
        $f = fopen('php://output', 'w') or show_error("Can't open php://output");
        $n = 0;     
        foreach ($array as $line)
        {
            $n++;
            if ( ! fputcsv($f, $line))
            {
                show_error("Can't write line $n: $line");
            }
        }
        fclose($f) or show_error("Can't close php://output");
        $str = ob_get_contents();
        ob_end_clean();

        if ($download == "")
        {
            return $str;    
        }
        else
        {   
            echo $str;
        }       
    }
}

// ------------------------------------------------------------------------

/**
 * Query to CSV
 *
 * download == "" -> return CSV string
 * download == "toto.csv" -> download file toto.csv
 */
if ( ! function_exists('query_to_csv'))
{
    function query_to_csv($query, $headers = TRUE, $download = "")
    {
        if ( ! is_object($query) OR ! method_exists($query, 'list_fields'))
        {
            show_error('invalid query');
        }

        $array = array();

        if ($headers)
        {
            $line = array();
            foreach ($query->list_fields() as $name)
            {
                $line[] = '"'.$name.'"';
            }
            $array[] = $line;
        }

        foreach ($query->result_array() as $row)
        {
            $line = array();
            foreach ($row as $item)
            {
                $line[] = '"'.$item.'"';
            }
            $array[] = $line;
        }

        echo array_to_csv($array, $download);
    }
}

/* End of file csv_helper.php */
/* Location: ./system/helpers/csv_helper.php */

This will give Insert double inverted comma in column name as well as rows. You can also remove "" this from column name.

Answer 4

You could use the implode() function to make the code above even more simple:

$delimiter = ';';
$enclosure = '"';
$linefeed  = "\n";

$lines = array();
// traversing data, one row at a time
foreach ($data as $row) {
    $arr = array();
    // traversing row, col after col
    foreach ($row as $col => $val) {
        // MySQL-style escaping double quotes
        $val = str_replace('"','""',$val);
        // wrapping each value in double quotes
        $arr[] = sprintf('%s%s%s',$enclosure,$val,$enclosure);
    }
    // generating formatted line and storing it
    $lines[] = implode($delimiter,$arr);
}
// join each lines to have the final output
$output = implode($linefeed,$lines);
echo $output;

Answer 5

Change your code little bit.

fputcsv($f, $line)

To

fputcsv($f,array_map('strval', $line), ',', '"')

Hope this will help you.

FYI: its in array_to_csv

Answer 6

I would probably just replace fputcsv with a home grown version. Now I'm taking your data-structure to always be a grid like traditional spreadshees and CSV-files.

function getQuotedString($arr){
    $buffer = "";
    foreach($arr as $line){
        $string = "";
        foreach($line as $data){
            // See if $string is no longer empty, meaning we need a comma to separate data
            if($string !== "") $string .= ',';
            $string .= '"' . $data . '"';
        }
        $buffer .= $string . PHP_EOL;
    }
    return $buffer;
}

Now use that in your code:

...
ob_start();
$f = fopen('php://output', 'w') or show_error("Can't open php://output");
$quotedData = getQuotedString($arr);

for ($written = 0; $written < strlen($data); $written += $fwrite) {
    $fwrite = fwrite($f, substr($data, $written));
    if ($fwrite === false) {
        return $written;
    }
}

fclose($f) or show_error("Can't close php://output");
$str = ob_get_contents();
ob_end_clean();
...

You can then use $written to see how many bytes were written, or if you want to know how many lines there were you can just use sizeof($arr).

Hope this helps!

Answer 7

Wouldn't the implode function work beautifully for this?

$array = [ 
 (string)'XXX XX XX',
 (string)'3',
 (string)'68878353',
 (string)'',
 (string)'xxxx@xxxx.xxx.xx'
];
$csvData = '"' . implode('","', $array) . '"';

and then you can just write $csvData out to your file?

Answer 8

Ofcourse there are other ways to handle it, but by using fputcsv and works for most scenarios.

if(!function_exists('array_to_csv')) {
    function array_to_csv($array, $download = "") {
        ob_start();
        $f = fopen('php://output', 'w') or die("Can't open php://output");
        $counter = count($array);
        for ($i = 0; $i < $counter; $i++) { 
            if(!fputcsv($f, array_map(function($value) {  return '"'.$value.'"';  }, $array[$i]), ",", "\"")) {
                die("Can't write line $i: ".print_r($array[$i], true));
            }
        }
        fclose($f) or die("Can't close php://output");
        $str = str_replace('"""', '"', ob_get_contents());
        ob_end_clean();

        if(strlen($download) > 0) {
            header('Content-Type: application/csv');
            header('Content-Disposition: attachment; filename="'.$download.'"');
            echo $str;
        } else {
            return $str;
        }
    }
}

Answer 9

Don't take the effort of properly enclosing every filed with double quotes. There are in-build php functions available to do the job without error.

Not only double quotes, you will be in need for single quote ('), double quote ("), backslash (\) and NULL (the NULL byte).

Use fputcsv() to write, and fgetcsv() to read, which will take care of all.

Answer 10

I have got solution this function convert multi dimension array into CSV with double quote and

function arr_to_csv($arr)
  {
        $filePointer="export.csv";
        $delimiter=",";
        $enclosure='"';
        $dataArray =$arr;

        $string = "";

        // No leading delimiter
        $writeDelimiter = FALSE;
        //foreach($dataArray as $dataElement)
        foreach ($dataArray as $key1 => $value){
        foreach ($value as $key => $dataElement) 

         {
          // Replaces a double quote with two double quotes
          $dataElement=str_replace("\"", "\"\"", $dataElement);
          // Adds a delimiter before each field (except the first)
          // Encloses each field with $enclosure and adds it to the string
          if($writeDelimiter) $string .= $delimiter;
          // $new_string = $enclosure . $dataElement . $enclosure;
          $string .= $enclosure . $dataElement . $enclosure;
         // Delimiters are used every time except the first.
          $writeDelimiter = TRUE;
         } // end foreach($dataArray as $dataElement)

                $string .= "\n";

         }      
        // Append new line
        $string .= "\n";    
         //$string = "An infinite number of monkeys";
        print($newstring);
       // Write the string to the file
      // fwrite($filePointer,$string);
       header('Content-Type: application/csv');
      header('Content-Disposition: attachment; filename="'.$filePointer.'";');
  }