insert xls error...

techker · 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 ...

Christian F. · 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.

techker · September 4, 2012

ok..but you lost me.i will look at the page.

Christian F. · 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.

techker · 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

Christian F. · 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.

techker · September 4, 2012

but is it cause ogf the xl format?

Christian F. · 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.

techker · 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...

jazzman1 · 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");

Christian F. · 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.

techker · September 4, 2012

ok tx guys!

Sign In

insert xls error...

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information