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)'[email protected]',
    ),
);

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

Output I am getting:

"XXX XX XX",3,68878353,,[email protected]

Expected Output:

"XXX XX XX","3","68878353","","[email protected]"

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

Solution 1

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.'";');
  }

Solution 2

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));

Solution 3

Concat the string with this '"""';

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

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

Solution 4

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.

Solution 5

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;

View more solutions

Comments

[Anudeep GI]

I have changed code function test_fun(){ $array = array( array( $this->encodeFunc('xx x x'), $this->encodeFunc('3'), $this->encodeFunc('68878353'), $this->encodeFunc(''), $this->encodeFunc('[email protected]'), ), ); //fputcsv($handler, array_map(encodeFunc, $array), ',', chr(0)); //print_r($array); $this->load->helper('csv'); array_to_csv($array, 'blueform.csv'); }function encodeFunc($value) { return "\"$value\""; } output : """xxxx xx xxx""","""3""","""68878353""","""""","""[email protected]"""

[Anudeep GI]

I need double quotes when you open CSV file in text editor ...if you open this file in text editor it will show ""UI"" like this @bugfixer

[Bugfixer]

@Anudeep - But where did u mention that you want to open this file in text editor

[Bugfixer]

Read this link to better understand behavior of CSV file.

[Canis]

I really dont see why you need to add the clutter you do to your strings and then remove it again later... Why not just add PHP_EOL after the end of the inner foreach instead of adding |||? And the way you have set up your code you should never see |||, so why do you try to replace it with newline? You also set $writeDelimiter to true for every pass of the function, why not just see if the string is blank?

[Anudeep GI]

@Canis I have made code change ... please check I have remove '|||' this

[Marcello Perri]

this is the best and simplest solution to me

[Nick Tsai]

Adding chr(0) for fputcsv enclosure will actually generate ASCII 0 Garbled when there is a value that needs to be enclosed.