asked on

Need Regular expression to pre process CSV file

( PHP 5.2 / Apache / WinXP )
I need to clean up a CSV file line by line before I process it.
The file has been mangled by Excel.

I'm new to regular expressions so I need some help.
The CSV file is actually a .txt file which is tab separated.
What I need to do is remove any tabs, commas and single quotes that are contained within a double quote.
And also remove the double quote as well.
So I end up with a clean CSV line separated by tabs.

Example this;
C05110200 "Trish, Ruducheerry" Cantonon TH18312 1973/0726
Should become
C05110200 Trish Ruducheerry Cantonon TH18312 1973/0726

Please provide a code example

ddrudik

You mention single quotes ' but not double quotes " but your result example above shows the double quotes gone.
If you want single-quotes removed:
<?php
$str="C05110200\t\"Trish, \tRuducheerry\"\tCantonon\tTH18312\t1973/0726";
echo "<pre>$str";
$str=preg_replace_callback('/"[^"]*"/','repfunc',$str);
function repfunc($match){
return preg_replace("/[\t,']/",'',$match[0]);
}
echo "<br>$str";
?>

If you want the double-quotes removed:
<?php
$str="C05110200\t\"Trish, \tRuducheerry\"\tCantonon\tTH18312\t1973/0726";
echo "<pre>$str";
$str=preg_replace_callback('/"[^"]*"/','repfunc',$str);
function repfunc($match){
return preg_replace('/[\t,"]/','',$match[0]);
}
echo "<br>$str";
?>

Matthew_Way

ASKER

Okay let me reword

Remove all single and double quotes.
Remove tab character only if it appears within a double quote.

ddrudik

Please confirm that you want to remove all single and double quotes, regardless of where they appear in the text, such as:
C05110200 "Trish Ruducheerry's Name" Cantonon's Test TH18312 1973/0726

Also confirm that the single quotes or double quotes to be removed are not escaped in any way in the text, such as this:
C05110200 "the following is a \"quote\" that someone said" Cantonon's Test TH18312 1973/0726

ASKER CERTIFIED SOLUTION

ddrudik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Matthew_Way

ASKER

Thank you v.much

ddrudik

Thanks for the question and the points.