bululu Posted November 5, 2013 Share Posted November 5, 2013 (edited) I am having trouble importing a CSV file into MyQL using PHP's fgetcsv, because of comas in one or more fields. I have successfully uploaded files where all fiels do not have comas. As expected, the presence of comas messes up everything as the item with comas is inserted in multiple columns instead of its allocated column. How do I escape any number of comas? I have tapped my head several times and I cannot come up with a way to do it. Please help! Viewed in a Text Editor, my CSV looks like: 2013-10-29,301,Mr Peter Jones,Father,"Leeds Industries Ltd, 17 Ice Se, LS7 3BD, Leeds",0796000056,01132345678,10,A 2013-10-29,302,Ms Adel Martins,Mother,"Cosmetics Industries Bradford, ",0746000087,01242545671,10,A 2013-10-29,303,Mrs Joyce Malaiti,Auntie,"The Hedrow, No 213, Leeds, LS9 3TT",0756000034,01133451321,10,A 2013-10-29,304,Mr Phiri,Father,"PO Box 123456, Leeds",0776000090,,10,A 2013-10-29,305,Mr Bismark,Uncle,"Plot 56789 Albion Street, Leeds, LS4 3EE",0776000032,01137123465,10,A As you can see, I am including the date because I could not use the MySQL NOW() function to handle the date. Is there a way to automate datae insertion when importing a csv file? The code is as follows: ?php //connect to the database $connect = mysql_connect("localhost","root","password"); mysql_select_db("some_db",$connect); //select the table // if ($_FILES['csv']['size'] > 0) { //get the csv file $file = $_FILES['csv']['tmp_name']; $handle = fopen($file,"r"); //loop through the csv file and insert into database do { if ($data[0]) { mysql_query("INSERT INTO youths (date,id,guardian,relationship,address,mobile,landline,grade,class) VALUES ( '".addslashes($data[0])."', '".addslashes($data[1])."', '".addslashes($data[2])."', '".addslashes($data[3])."', '".addslashes($data[4])."', '".addslashes($data[5])."', '".addslashes($data[6])."', '".addslashes($data[7])."', '".addslashes($data[8])."' ) "); } } while ($data = fgetcsv($handle,100000,",","'")); // //redirect header('Location:guardian_csv_mysql.php?success=1'); die; } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Import a CSV File with PHP & MySQL</title> </head> <body> <?php if (!empty($_GET['success'])) { echo "<b>Your file has been imported.</b><br><br>"; } //generic success notice ?> <form action="guardian_csv_mysql.php" method="post" enctype="multipart/form-data" name="form1" id="form1"> Choose your file: <br /> <input name="csv" type="file" id="csv" /> <input type="submit" name="Submit" value="Submit" /> </form> </body> </html> Edited November 5, 2013 by bululu Quote Link to comment Share on other sites More sharing options...
Barand Posted November 5, 2013 Share Posted November 5, 2013 The strings are enclosed in double quotes (") you specify single quote (') in while ($data = fgetcsv($handle,100000,",","'")) You could just use default values as in while ($data = fgetcsv($handle,100000)) Quote Link to comment Share on other sites More sharing options...
Solution bululu Posted November 5, 2013 Author Solution Share Posted November 5, 2013 (edited) The strings are enclosed in double quotes (") you specify single quote (') in while ($data = fgetcsv($handle,100000,",","'")) You could just use default values as in while ($data = fgetcsv($handle,100000)) @Barand, I removed the quotes as you advised, I used : while ($data = fgetcsv($handle,100000)) and it works flawlessly regardless of any comas or quotes! Would you, please care to explain because I really thought those quotes were needed in there so am wondering why they were even there in the first place! Thanks so much, you saved me from running crazy! Edited November 5, 2013 by bululu Quote Link to comment Share on other sites More sharing options...
Barand Posted November 5, 2013 Share Posted November 5, 2013 The strings are enclosed in double quotes (") you specify single quote (') in while ($data = fgetcsv($handle,100000,",","'")) 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.