Big_Pat Posted June 2, 2013 Share Posted June 2, 2013 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. Quote Link to comment Share on other sites More sharing options...
Big_Pat Posted June 2, 2013 Author Share Posted June 2, 2013 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. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 2, 2013 Share Posted June 2, 2013 there's no reason for you to modify the csv file. any piece of data that contains the comma delimiter will/should be enclosed in quotes and fgetcsv can correctly parse it. Quote Link to comment Share on other sites More sharing options...
Big_Pat Posted June 2, 2013 Author Share Posted June 2, 2013 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 \. Quote Link to comment Share on other sites More sharing options...
Solution mac_gyver Posted June 2, 2013 Solution Share Posted June 2, 2013 just use the default fgetcsv parameters. you have specifically used a single-quote as the field enclosure character. your data is using double-quotes (the default) as the field enclosure character. Quote Link to comment Share on other sites More sharing options...
Big_Pat Posted June 2, 2013 Author Share Posted June 2, 2013 (edited) 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 June 2, 2013 by Big_Pat Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted June 2, 2013 Share Posted June 2, 2013 you are still doing too much. there's no need to modify the csv data. fgetcsv will read the original csv data by default. Quote Link to comment Share on other sites More sharing options...
Big_Pat Posted June 2, 2013 Author Share Posted June 2, 2013 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'. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.