Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Dealing with commas in CSV

I get a CSV data from a SOAP call in php. Unfortunately, the data may have commas in it. It is formatted correctly as in

1,name,2,lariat,3,"first, last",5,NMEA,...

I need to parse it to individual values in either php or javascript. I have browsed through threads on stack overflow and elsewhere but have not found a specific solution in php / javascript.

The approach I am currently using is

$subject = '123,name,456,lryyrt,123213,"first,last",8585,namea3';
$pattern = '/,|,"/';
$t2=preg_replace ('/,|(".*")/','$0*',$subject);
$t2=str_replace(',','*',$t2);
$t2=str_replace('*',',',$t2);

Where * is the deliminator, but the preg_replace generates an extra *. I have tried a couple of other approaches involving preg_match and other preg_ functions but did not succeed in having any kind of a clean split.

Any suggestion on how to split up CSV data that contains commas in it?

like image 376
Lord Loh. Avatar asked Jun 30 '11 15:06

Lord Loh.


People also ask

How do you handle commas in a CSV file?

Re: Handling 'comma' in the data while writing to a CSV. So for data fields that contain a comma, you should just be able to wrap them in a double quote. Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

Does CSV have to be comma separated?

Here are the rules for creating CSV files: This should be a single character. If you do not want to use a comma, you should choose something like a tab or a pipe (|) character. Keep each record on a separate line. Each record must start on its own line, but a single record can span multiple lines.

Should CSV have space after comma?

Because of this, it is important that when creating a CSV file, each value should only be separated by a comma without any unwanted whitespace before or after the commas, since they will not be removed automatically.


1 Answers

Don't attempt to do this with a regular expression. Just use str_getcsv()! The third parameter informs str_getcsv() to look for quote-enclosed fields.

$subject = '123,name,456,lryyrt,123213,"first,last",8585,namea3';
$array = str_getcsv($subject, ",", '"');

print_r($array);
// Prints:
Array
(
    [0] => 123
    [1] => name
    [2] => 456
    [3] => lryyrt
    [4] => 123213
    [5] => first,last
    [6] => 8585
    [7] => namea3
)
like image 63
Michael Berkowski Avatar answered Oct 03 '22 11:10

Michael Berkowski