The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.


Results 1 to 9 of 9

Thread: PHP csv import - with commas in datafields

  1. #1

    PHP csv import - with commas in datafields

    Hi Guys,

    I'm importing a CSV file to mysql using PHP using the usual methods.

    Obviously im splitting strings at commas - but the problem is that some of the data also contains commas!

    How do i differentiate between the commas that are data, and the commas that separate my fields??

    Really appreciate any input!

  2. #2
    use TSV

    your content should actually be enclosed in quotes or semi-quotes so it shouldn't conflict... AND if you are using
    PHP Code:
    mysql_real_escape_string() 
    it wouldn't be a problem either
    Let us live so that when we come to die even the undertaker will be sorry. - Mark Twain
    Don't PM me your CSS, xHTML, JS or PHP questions. I will not reply to ANY IE6 questions.

  3. #3
    I'd love to use TSV - but the file is exported from my clients internal system and they wont modify it!

    None of the content is encased unless its specifically a string

    Any other suggestions? Appreciate your time!

  4. #4
    Oh that sucks ...

    But I always thought that if you get an exported CSV that has commas in it it'd show something like:
    Code:
    "This, has, commas", This doesn't have commas, foo, bar
    It's not doing this for you?
    Let us live so that when we come to die even the undertaker will be sorry. - Mark Twain
    Don't PM me your CSS, xHTML, JS or PHP questions. I will not reply to ANY IE6 questions.

  5. #5
    Would appear not - I've tried exporting my own version to check.

    Regardless - I'm splitting the string by commas - so even in your example the data fields would split in the wrong place :/

  6. #6
    Well if it was adding the quotes ( like it really should be ) you could modify and use this piece of code:
    http://www.php.net/manual/en/function.split.php#73286

    I modified to:
    PHP Code:
    <?php
        
    function getCSVValues($string$separator=",")
        {
            
    $elements explode($separator$string);
            
            for (
    $i 0$i count($elements); $i++) 
            {
                
    $nquotes substr_count($elements[$i], '"');
                
                if (
    $nquotes %== 1)
                {
                    for (
    $j $i+1$j count($elements); $j++) 
                    {
                        if (
    substr_count($elements[$j], '"') > 0
                        {
                            
    // Put the quoted string's pieces back together again
                            
    array_splice($elements$i$j-$i+1,
                            
    implode($separatorarray_slice($elements$i$j-$i+1)));
                            break;
                        }
                    }
                }
                
                if (
    $nquotes 0
                {
                    
    // Remove first and last quotes, then merge pairs of quotes
                    
    $qstr =& $elements[$i];
                    
    $qstr substr_replace($qstr''strpos($qstr'"'), 1);
                    
    $qstr substr_replace($qstr''strrpos($qstr'"'), 1);
                    
    $qstr str_replace('""''"'$qstr);
                }
            }
            
            return 
    $elements;
        }
        
        
    $file file_get_contents('quotes.csv');
        
    $dataStrings explode("\r"$file);
        
        
    $i 0;
        foreach ( 
    $dataStrings as $data ) ++$i

        for ( 
    $j 0$j $i; ++$j )
        {
            
    $strings getCSVValues$dataStrings[$j] );
            
    print_r($strings);
            print(
    "<br/>\n\r");
        }
    ?>
    your CSV would look like:
    Code:
    Pedro Calderon de la Barca,"What law, what reason can deny that gift so sweet, so natural that God has given a stream, a fish, a beast, a bird?",Love Quotes
    Ansel Adams,"You don't take a photograph, you make it. ",Art Quotes
    and the final output would look like
    Code:
    Array ( [0] => Pedro Calderon de la Barca [1] => What law, what reason can deny that gift so sweet, so natural that God has given a stream, a fish, a beast, a bird? [2] => Love Quotes )
    Array ( [0] => Ansel Adams [1] => You don't take a photograph, you make it. [2] => Art Quotes )
    Let us live so that when we come to die even the undertaker will be sorry. - Mark Twain
    Don't PM me your CSS, xHTML, JS or PHP questions. I will not reply to ANY IE6 questions.

  7. #7
    Thanks Simp - mighty frustrating, I'll play with your script and get back to you.

  8. #8
    Simp - the script was great, it ran through one of my CSV files with no issues at all!

    There's a second CSV though, and it's tripping up, and cannot figure out why! Could I add you to msn if you wouldn't mind taking a quick look? - If it's a huge job I'd be willing to pay for your time.

    Thanks again man!

    Zaid

  9. #9

    csv import which contains the data field other than comma

    Can u please help me in uploading this type of line into database.
    "The doctor\\'s callback time is every day at 4 o\\'clock"
    That is the data field containing special characters in it.
    When i used the code given in this site it works good with comma in data field. but i want to insert the field as which i mentioned above. Please help me. thanks in advance

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012