Working with a CSV file with odd encapsulation // PHP

Question

I have a CSV file that I'm working with, and all the fields are comma separated. But some of the fields themselves, contain commas. In the raw CSV file, the fields that contain commas, are encapsulated with quotes, as seen here;

"Doctor Such and Such, Medical Center","555 Scruff McGruff, Suite 103, Chicago IL 60652",(555) 555-5555,,,,something else

the code I'm using is below

<?PHP
    $file_handle = fopen("file.csv", "r");
    $i=0;
    while (!feof($file_handle) ) {
        $line = fgetcsv($file_handle, 1024);
        $c=0;
        foreach($line AS $key=>$value){
            if($i != 0){
                if($c == 0){
                    echo "[ROW $i][COL $c] - $value"; //First field in row, show row #
                }else{
                    echo "[COL $c] - $value"; // Remaining fields in row
                }
            }
            $c++;
        }
        echo "<br>"; // Line Break to next line
        $i++;
    }
    fclose($file_handle);
?>

The problem is I'm getting the fields with the commas split into two fields, which messes up the number of columns I'm supposed to have.

Is there any way I could search for commas within quotes and convert them, or another way to deal with this?

Note this isn't ‘odd’, but absolutely standard CSV format. `fgetcsv` is *supposed* to default to using `"` for enclosures so it *should* be OK (assuming a new enough PHP). In any case, `fgetcsv` is still wrong in that it tries to use backslash-escaping, which is not and has never been a part of CSV format. — bobince, Apr 10 '10 at 10:57
it was odd to me, in that i havnt seen the use of "selective" encapsulation like that — mrpatg, Apr 10 '10 at 18:54

score 6 · Accepted Answer · edited Apr 11 '10 at 09:15

6

You can use the "enclosure" parameter. See the fgetcsv documentation.

$handle = fopen("file", "r");
if ($handle ) {
    while (($line = fgetcsv($handle, 2048, ",", '"')) !== FALSE) {
      print_r( $line)."\n";
    }
    fclose($handle);
}

Output:

$ cat file
"Doctor Such and Such, Medical Center","555 Scruff McGruff, Suite 103, Chicago IL 60652",(555) 555-5555,,,,something else

$ php test.php
Array
(
    [0] => Doctor Such and Such, Medical Center
    [1] => 555 Scruff McGruff, Suite 103, Chicago IL 60652
    [2] => (555) 555-5555
    [3] =>
    [4] =>
    [5] =>
    [6] => something else
)

edited Apr 11 '10 at 09:15

Peter Mortensen

30,738
21
105
131

answered Apr 10 '10 at 08:17

ghostdog74

327,991
56
259
343

i tried this, but it didnt seem to work. Still breaking up fields with comma's as two fields. – mrpatg Apr 10 '10 at 08:25

score 0 · Answer 2 · answered Apr 10 '10 at 08:32

0

You can search through each line whether it contains a comma and act accordingly:

 foreach($line AS $key => $value){
    if (strpos($value, ',') !== false)
    {
       // there is a comma in this value
    }
    else
    {
       // there is no comma
    }
}

answered Apr 10 '10 at 08:32

Sarfraz

377,238
77
533
578

would this only work after the fact? The fields are already split up by the comma, so none of them would contain one? Maybe im missing something, its quite early. – mrpatg Apr 10 '10 at 08:40
@Patrick: This is a check for any value containing the comma not values separated by comma. – Sarfraz Apr 10 '10 at 08:58
1

@Sarfraz i understand, but at the point where your code would be relavent, the commans have already been split into the array that this loops goes through. – mrpatg Apr 10 '10 at 09:01
@Patrick: You did not understand what I meant. You have values in quotes that also contain comma within them right? That's what this checks for. – Sarfraz Apr 10 '10 at 09:11
-1: The question is not asking whether commas are present or not on a given line. – eyelidlessness Apr 11 '10 at 09:20
@eyelidlessness: the second sentence of the questoin reads: `But some of the fields themselves, contain commas.` – Sarfraz Apr 11 '10 at 14:14
@Sarfraz, the fields aren't lines, they're separated by commas. By the time a string is split by commas, looping through each field value and checking for commas will always return false. Please reread the question and understand that what's being asked for is logic to not split on commas that fall between quotes; the question is *not* asking how to determine if commas are present there, just how to not split on them. – eyelidlessness Apr 11 '10 at 19:25

Working with a CSV file with odd encapsulation // PHP

2 Answers2

Linked