Jump to content

fgetcsv escaping commas in title


Big_Pat
Go to solution Solved by mac_gyver,

Recommended Posts

The following code is supposed to take a CSV file and insert to the database. First, I prepare the CSV file by replacing with \" and , with \, then run a PHP function to prepare for insertion. It's not working the way I want it to. I've tried reading about delimiters and escapes but I've not found a solution. Can you help, please?

 

The excerpted code for the insertion is: 

if ($_FILES[csv][size] > 0) {
 
    $file   = $_FILES[csv][tmp_name];
    $handle = fopen($file, "r");
 
    do {
        if ($data[0]) {
            mysql_query("INSERT INTO songlist (track_number,title,
				etc, etc) VALUES 
			( 
			    '" . mysql_prep($data[0]) . "', 
			    '" . mysql_prep($data[1]) . "',
			    etc, etc
			) 
		    ");
        }
    } while ($data = fgetcsv($handle, 1000, ",", "'"));
    

and mysql_prep's code is: 

function mysql_prep($value)
{
    $magic_quotes_active = get_magic_quotes_gpc();
    $new_enough_php      = function_exists("mysql_real_escape_string"); // i.e. PHP >= v4.3.0
    if ($new_enough_php) { // PHP v4.3.0 or higher
        // undo any magic quote effects so mysql_real_escape_string can do the work
        if ($magic_quotes_active) {
            $value = stripslashes($value);
        }
        $value = mysql_real_escape_string($value);
    } else { // before PHP v4.3.0
        // if magic quotes aren't already on then add slashes manually
        if (!$magic_quotes_active) {
            $value = addslashes($value);
        }
        // if magic quotes are active, then the slashes already exist
    }
    return $value;
}

Is there a better function available? At the moment, commas in titles are being read as end-of-entries, despite being escaped.

Link to comment
Share on other sites

I might've solved this myself, actually. When loading the csv file back into Excel, the title entry after the escaped comma has moved to the next column which, of course, I don't want. Seems I need to prepare my CSV file better.

Link to comment
Share on other sites

Well, I tried your suggestion but to no avail. One line of the CSV file reads like this, when opened in a text editor:

1,Ever After,Iron Tongue,"The Dogs Have Barked, the Birds Have Flown",Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013

and the result is that the title has split to two columns with no quotes and no comma.

 

I then tried 

1,Ever After,Iron Tongue,"The Dogs Have Barked\, the Birds Have Flown",Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013

but the result was " "The Dogs Have Barked" in one column and " the Birds Have Flown" " in the next, with no comma or escape \.

Link to comment
Share on other sites

Ah, that's it. Excel added the double quotes around fields it had escaped. This works:

 

Thanks!

7,Said N Done,Iron Tongue,'The Dogs Have Barked\, the Birds Have Flown',Iron Tongue,04:57,2013,Southern Rock,-1,Neurot,US,,3219,2013
Edited by Big_Pat
Link to comment
Share on other sites

Maybe it's just the vagaries of Excel, then, but if I simply enter the CSV as prepared by Excel it reads the comma in the album's title as a separator. In other words, 

1,Ever After,Iron Tongue,The Dogs Have Barked, the Birds Have Flown,Iron Tongue,08:14,2013,Southern Rock,-1,Neurot,US,,3219,2013

it sees the comma, understandably enough - the album's title is actually 'The Dogs Have Barked, the Birds Have Flown'.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.