techker Posted September 4, 2012 Share Posted September 4, 2012 Hey guys i go this going. $db_username="2"; //database user name $db_password="2";//database password $db_database="2"; //database name $db_host="localhost"; mysql_connect($db_host,$db_username,$db_password); @mysql_select_db($db_database) or die( "Unable to connect to database."); $handle = fopen("http://www.ecoleljp.ca/Upload_/test.xls", "r"); //test.xls excel file name if ($handle) { $array = explode("\n", fread($handle, filesize("test.xls"))); } $total_array = count($array); $i = 0; while($i < $total_array) { $data = explode(",", $array[$i]); $sql = "insert into test (`nom` ,`prenom` ) values ('$data[0]','$data[1]')"; // i have two fields only , in oyour case depends on your table structure $result = mysql_query($sql); $i++; } echo "completed"; it inserts but it looks like this lol id nom prenom Edit Inline Edit Copy Delete 1 ???? Edit Inline Edit Copy Delete 2 DROID TECH 8?rCal Edit Inline Edit Copy Delete 3 ?rCalib ##0_);("$"# Edit Inline Edit Copy Delete 4 ?? ?? ? Edit Inline Edit Copy Delete 5 ?} Edit Inline Edit Copy Delete 6 ????} Edit Inline Edit Copy Delete 7 ?} Edit Inline Edit Copy Delete 8 ????} how did i get to this? my xls file looks like this 1 2441343 techker techker 2 .... ... ... 3 ... Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/ Share on other sites More sharing options...
Christian F. Posted September 4, 2012 Share Posted September 4, 2012 This is a typical encoding issue, it looks as if you're trying to treat a input source encoded in something other than UTF-8 as UTF-8. You'll need to detect the correct charset of the input source, transcode it to UTF-8 proper, and then save it to the database. Look at the Multibyte String Functions for more information on this. Note that you'll also need to make sure that the fields/table in the database are set to use UTF-8, and that you've selected it as the charset for the active PHP-MySQL database connection. Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375015 Share on other sites More sharing options...
techker Posted September 4, 2012 Author Share Posted September 4, 2012 ok..but you lost me.i will look at the page. Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375017 Share on other sites More sharing options...
Christian F. Posted September 4, 2012 Share Posted September 4, 2012 Hmmm... Sounds like you need to read the "Absolute Minimum article on Unicode as well, to give you a solid foundation of knowledge to solve your problem with. Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375021 Share on other sites More sharing options...
techker Posted September 4, 2012 Author Share Posted September 4, 2012 i tried a fast test with this $link = mysql_connect("localhost", "1", "2"); mysql_set_charset('utf8',$link); $db_selected = mysql_select_db("3", $link); if (!$db_selected) { die ('Database access error : ' . mysql_error());} [/php but i still get the odd caracters..i will continue with more tests Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375022 Share on other sites More sharing options...
Christian F. Posted September 4, 2012 Share Posted September 4, 2012 That's one part down, the PHP-MySQL connection. Now it only remains to ensure the actual database is UTF-8 compliant, the charset detection of the source data, and the transcoding to UTF-8. Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375023 Share on other sites More sharing options...
techker Posted September 4, 2012 Author Share Posted September 4, 2012 but is it cause ogf the xl format? Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375024 Share on other sites More sharing options...
Christian F. Posted September 4, 2012 Share Posted September 4, 2012 Not directly, no. The text inside the XLS document, however, is encoded in a particular character set which is not compatible with UTF-8. Which is why you're having the issues as noted above. Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375028 Share on other sites More sharing options...
techker Posted September 4, 2012 Author Share Posted September 4, 2012 i tried it with csv instead and my new script works. //connect to the database $connect = mysql_connect("localhost","2","2"); mysql_select_db("2",$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 test (nom, prenom, fiche,groupe) VALUES ( '".addslashes($data[1])."', '".addslashes($data[2])."', '".addslashes($data[0])."', '".addslashes($data[6])."' ) "); } } while ($data = fgetcsv($handle,1000,",","'")); // //redirect header('Location: import.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="" 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> but i still don't get why csv works better the xls..it seems to that what i find in google... Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375030 Share on other sites More sharing options...
jazzman1 Posted September 4, 2012 Share Posted September 4, 2012 First of all, never ever run a query string in a loop. Take a look at this -> http://forums.phpfreaks.com/index.php?topic=363815.msg1722467#msg1722467 Secondly, if you're using mysql_set_charset('utf8',$link);, you have to be sure the database collation is utf8_general_ci (I prefer this). In your script you can put after $db_selected this piece of code: $db_selected = mysql_select_db("3", $link); mysql_query("SET NAMES UTF8"); Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375031 Share on other sites More sharing options...
Christian F. Posted September 4, 2012 Share Posted September 4, 2012 Jazzman1: SET NAMES and mysql_set_charset () does the same thing. It does not alter the table structure, but only sets the charset used for the communication between PHP and MySQL. Also, the latter is the preferred one of the two, as explained in the PHP manual. Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375035 Share on other sites More sharing options...
techker Posted September 4, 2012 Author Share Posted September 4, 2012 ok tx guys! Quote Link to comment https://forums.phpfreaks.com/topic/267973-insert-xls-error/#findComment-1375093 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.