CSV remove commas in quotation marks with regexp

1
votes

I have a CSV file and we know excel does its thing with commas in a field by enclosing them in double quotation marks for instance i have a file

Product Name,Product Code
Product 1,AAA
"Prod,A,B",BBB

How can I use RegExp to replace the quotation marks with "." instead but only within quotation marks so i get

Product Name,Product Code
Product 1,AAA
Prod.A.B,BBB

as output

2
phpregexcsv
Why do you need to do this? PHP's CSV handling functions are able to work with optionally quote-enclosed fields. – Michael Berkowski
Read about this -> php.net/manual/en/function.preg-replace.php – Tudor
I read my csv directly from a database converted to text blob field, I dont want to write it to disc – Tarang
Related stackoverflow.com/questions/3615747/… – Qtax
turns out PHPs CSV functions are good, but make sure you encode the file properly, doesn't work properly with quotations with UTF8 encoding – Tarang

2 Answers

6
votes

CSV handling functions (fgetcsv(), fputcsv()) are much better for this - they will handle edge cases and will likely be far more reliable than any regex you can come up with.

// Open the file
$fp = fopen($pathToCsvFile, 'r+');

// Create an array of modified data
$tmp = array();
while (($row = fgetcsv($fp, 8192)) !== FALSE) {
  foreach ($row as &$field) $field = str_replace(',', '.', $field);
  $tmp[] = $row;
}

// Truncate the file and put the pointer at the beginning
ftruncate($fp, 0);
rewind($fp);

// Write the modified data back and close the file
foreach ($tmp as $row) {
  fputcsv($fp, $row);
}
fclose($fp);

EDIT Following your comment about not wanting to read from/write to disk, you can do this:

// Lets say the raw CSV data is held in this variable as a string
$rawCsvData = 'Product Name,Product Code
Product 1,AAA
"Prod,A,B",BBB';

// Open a virtual file pointer to memory and fill it with your data
$fp = fopen('php://memory', 'w+');
fwrite($fp, $rawCsvData);

// Start from the beginning of the pointer
rewind($fp);

// ... INSERT CODE FROM ABOVE HERE (minus the fopen()/fclose())

$modifiedCsvData = stream_get_contents($fp);
fclose($fp);
2
votes

This will do multiple replaces, and remove the quotes.

<?php
$data = 'Product Name,Product Code
Product 1,AAA
"Prod,A,B",BBB';

$rgx = '/"(.+?)"/';

preg_match_all($rgx, $data, $matches);
$x = 0; $max = count($matches[0]);
while($x < $max){
    $replace = str_replace(",", ".", $matches[1][$x]);
    $data = str_replace($matches[0][$x], $replace, $data);
    $x++;
}
echo $data;
?>