bululu Posted November 5, 2013 Share Posted November 5, 2013 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> Link to comment https://forums.phpfreaks.com/topic/283614-escaping-comas-in-a-csv-file-php-and-mysql/ 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)) Link to comment https://forums.phpfreaks.com/topic/283614-escaping-comas-in-a-csv-file-php-and-mysql/#findComment-1457038 Share on other sites More sharing options...
bululu Posted November 5, 2013 Author 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)) @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! Link to comment https://forums.phpfreaks.com/topic/283614-escaping-comas-in-a-csv-file-php-and-mysql/#findComment-1457039 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,",","'")) Link to comment https://forums.phpfreaks.com/topic/283614-escaping-comas-in-a-csv-file-php-and-mysql/#findComment-1457105 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.