fgetcsv OR SplFileObject HELP!!

[Tidus]

Hi guys.

I have a CSV file that I am trying to import to a mysql database.

The issue i am having is that the system we export the CSV files from seems to enclose SOME 'columns' with a " character. I am assuming it does this when the column contains a , character (for example in a name like DOE, JANE) that I dont want to be split into a separate column.

I can't seem to work out the correct code to make this happen. Here is an example of a line.

"ABEL, TAMMY 454454","End of: ABEL, TAMMY 454454",QP544454,28/10/2012 11:41,"0811 unlawfully use, possess","STEPHENS, JEREMY 54544454",LINK OPERATIONS,Located details incorrect,Entity: FORD FALCON Reg #: Colour: White

So it only seems to include the " when a , is necessary in the column.

My code looks like this;

PHP Code:

$row = 1;
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        echo "<p> $num fields in line $row: <br /></p>\n";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo $data[$c] . "<br />\n";
        }
    }
    fclose($handle);
} 


It doesn't seem to be parsing correctly...

I've also tried it with SplFileObject with similar results - i'm happy to use either.

HELP!?!

Thanks heaps

[Fou-Lu]

What output are you getting? Copy and paste of the code you have in use properly parses that line.

[Arcticwarrio]

PHP Code:

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { 


is looking for just a , not ","

you could try something like

PHP Code:

while (($data = fgetcsv($handle, 1000, '","')) !== FALSE) { 


[Fou-Lu]

No, you don't want to do that. The " are designed as enclosures (although the third parameter isn't necessary at all) and exist only when wrapping data that includes the ,. If you do that it will only seek to separate on "," instead of ignoring , which is surrounded by ".
Unless the user is looking for 13 results based completely on the ,? If that's the case, than an explode of the whole line would be easier.

Edit:
Actually, according to the doc you cannot do that anyways. The delimiter is only a char, not a string.

[Arcticwarrio]

Might be better to do a replace first with a vertical bar --> |

PHP Code:

$file = file_get_contents('test.csv', true);
$data = str_replace('","','|',$file); 


[Arcticwarrio]

sorry, not all the fields have " i missed that part,

where is your data from?

this might help
http://txt2re.com/

[Tidus]

I have echoed the output. This is what it shows as.

"ABEL
TAMMY 454454"
"End of: ABEL
TAMMY 454454"
QP455454
28/10/2012 11:41
"0811 unlawfully use
possess"
POLICELINK BRANCH
"STEPHENS
JEREMY 54544454"
LINK OPERATIONS
Located details incorrect
Entity: FORD FALCON Reg #: Colour: White

It seems to have split at every comma anyway and not removed the ".

This is a report extracted from ZAP if this is any help...

[Fou-Lu]

The closest I can get to yours is this:

þÿ"ABEL
TAMMY 454454"
"End of: ABEL
TAMMY 454454"
QP544454
28/10/2012 11:41
"0811 unlawfully use
possess"
"STEPHENS
JEREMY 54544454"
LINK OPERATIONS
Located details incorrect
Entity: FORD FALCON Reg #: Colour: White

Which I got by using unicode. So looks like the output software is saving in non-BOM unicode and not in ansi.
You should be able to issue a setlocale(LC_ALL, 'en_US.utf8');, which should use a utf8 charset. fgetcsv lists that it is locale aware.

[Arcticwarrio]

im not sure what ZAP is but are there any export options?

i.e. can you get it to buffer every field with " ?

[Arcticwarrio]

im sure Fou-Lu has an easier more compact way

but this works

PHP Code:

<?php


  $txt='"ABEL, TAMMY 454454","End of: ABEL, TAMMY 454454",QP544454,28/10/2012 11:41,"0811 unlawfully use, possess","STEPHENS, JEREMY 54544454",LINK OPERATIONS,Located details incorrect,Entity: FORD FALCON Reg #: Colour: White' ;


$pieces = explode('"', $txt);
$r = 1;
foreach ($pieces as $k => $v) {
    if ($v == ',') {
        unset($pieces[$k]);
    }
    if ($v == '') {
        unset($pieces[$k]);
    }
    if (substr_count($v, ',') > 1) {
        $Result[$r] = explode(',', $v);
        
        unset($pieces[$k]);
        $r++;
        
    }
    
}
$results = array_merge($pieces, $Result[1], $Result[2]);
foreach ($results as $k => $v) {
    if ($v == ',') {
        unset($results[$k]);
    }
    if ($v == '') {
        unset($results[$k]);
    }

}
foreach ($results as $k => $v) {
echo $k." => ".$v."<BR>";

}





?>