PHP: How can I get rid of commas inside double quotes in a multiple column row?

Question

I need a PHP solution to get rid of commas inside double quotes. I can't seem to figure out a solution using preg_replace.

I'm inserting data into a database from a text file that is coma delimited.

Certain columns from the text file contain multiple words that are surrounded in double quotes. Theses double quotes have comas inside, so when I attempt to insert the text into my database using coma delimited columns it reads in the quotes between the double quotes and turns them into columns.

Here is how a row in the text file looks:

partname,2035, "this is, desc,ription",qty, "another, description",

this is what happens when I attempt to separate it:

results partname, 2035, this is, desc, ription, qty, another, description, 

this is what I want:

partime, 2035, this is description, qty, another description,

As you can see, the parts surrounded in double quotes should not be split into separate columns. I don't know how to fix this; could someone point me in the right direction?

Answer 1

What you want is the str_getcsv() function. It's only available in PHP5.3.0+ though.

Answer 2

Try this function to split a single line into raw values. It tries to cope with pretty irregular CSVs

function csv_split( $src, $comma = ',', $esc = '\\' ){
    $a = array();
    while( $src ){
     $c = $src{0};
     switch( $c ){
     // permit empty values
     case ',':
      $a[] = '';
      $src = substr( $src, 1 );
      continue 2;
     // ignore whitespace
     case ' ':
     case "\t":
      preg_match('/^\s+/', $src, $r );
      $src = substr( $src, strlen($r[0]) );
      continue 2;
     // quoted values
     case '"':
     case "'":
     case '`':
      $reg = sprintf('/^%1$s((?:\\\\.|[^%1$s\\\\])*)%1$s\s*(?:,|$)/', $c );
      break;
     // naked values
     default:
      $reg = '/^((?:\\\\.|[^,\\\\])*)(?:,|$)/';
      $c = ',';
     }
     if( preg_match( $reg, $src, $r ) ){
      $a[] = empty($r[1]) ? '' : str_replace( '\\'.$c, $c, $r[1] );
      $src = substr( $src, strlen($r[0]) );
      continue;
     }
     // else fail
     trigger_error("csv_split failure", E_USER_WARNING );
     break;
    }
    return $a;
}

Answer 3

This may work for you:

$pieces = explode( '"', 'partname,2035, "this is, desc,ription",qty, "another, description",' );

// explode the ones we should split (will be even-# elements)
for ( $i=0; $i<= count($pieces); $i+=2 ) {
    $tmpArray  = explode(",", $pieces[$i]);
    foreach($tmpArray as $value) {
     if ( strlen(trim($value)) > 0 ) {
      $finalArray[] = $value;
     }
    }
}

// now add the ones we shouldn't split inside quotes (odd-# elements)
for ( $i=1; $i<= count($pieces); $i+=2 ) {
    if ( strlen(trim($pieces[$i])) > 0 ) { 
     $finalArray[] = $pieces[$i];
    }
}

// show the result
echo "<pre>";
print_r($finalArray);
echo "<pre>";

Answer 4

The general solution for the problem would be to escape the separator character while creating the line before assembling the coma separated data with a simple.

define(SEPARATOR,',');
$dataField = str_replace(SEPARATOR,'BSLASH'.SEPARATOR,$dataField);

Pls change BSLASH to backslash character (the comment input box is more intelligent than me as it behaves strangely when I try to write a backslash character and I can't figure out how to turn it off :) )

Of course it's not an option if you receive the data from 3rd party source.

Answer 5

Guys, PHP already has a function for this, fgetcsv (get comma sepparated values from files)

<?php
$r = array();
$fh = fopen('test.txt','r');
while($t = fgetcsv($fh)) {
    $r[] = $t;
}
var_dump($r);

Which is there since the PHP4 era.

Answer 6

function csv_string_to_array($str){
$expr="/,(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))/";
$results=preg_split($expr,trim($str));
return preg_replace("/^\"(.*)\"$/","$1",$results);
}

That does the trick.