Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Aug 2002
    Posts
    66
    Thanks
    2
    Thanked 0 Times in 0 Posts

    fgetcsv OR SplFileObject HELP!!

    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($handle1000",")) !== 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

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    What output are you getting? Copy and paste of the code you have in use properly parses that line.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    709
    Thanks
    20
    Thanked 84 Times in 84 Posts
    PHP Code:
    while (($data fgetcsv($handle1000",")) !== FALSE) { 
    is looking for just a , not ","

    you could try something like
    PHP Code:
    while (($data fgetcsv($handle1000'","')) !== FALSE) { 
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    709
    Thanks
    20
    Thanked 84 Times in 84 Posts
    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); 
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #6
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    709
    Thanks
    20
    Thanked 84 Times in 84 Posts
    sorry, not all the fields have " i missed that part,

    where is your data from?

    this might help
    http://txt2re.com/
    Last edited by Arcticwarrio; 02-21-2013 at 05:03 PM.
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #7
    New Coder
    Join Date
    Aug 2002
    Posts
    66
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have echoed the output. This is what it shows as.

    Code:
    "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...

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    The closest I can get to yours is this:
    Code:
    ŝ˙"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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #9
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    709
    Thanks
    20
    Thanked 84 Times in 84 Posts
    im not sure what ZAP is but are there any export options?

    i.e. can you get it to buffer every field with " ?
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month

  • #10
    Regular Coder Arcticwarrio's Avatar
    Join Date
    May 2012
    Location
    UK
    Posts
    709
    Thanks
    20
    Thanked 84 Times in 84 Posts
    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>";

    }





    ?>
    There are 10 types of people on CodingForums,
    Those who understand Binary and those who dont.
    Get Cloud Hosting now from only£59 / month


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •