Everybody! This is important. In a few days, these forums will be moving over to using the totally sweet Discourse platform. To ensure this migration happens smoothly with no loss of content, these forums are currently in a read-only mode. I do apologize for the inconvenience.

There is never a good time to turn the forums off for an extended period of time, but I promise the new forums will be a billion times better. I'm pretty sure of it.

See you all on the other side in a few days, and if you have any (non-technical) questions, please e-mail me at kirupa@kirupa.com. For technical questions, try to find a tutorial that corresponds to what you are looking for and post in the comments section of that page.

Cheers,
Kirupa

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