Jump to content

insert xls error...


Recommended Posts

Hey guys i go this going.


$db_username="2"; //database user name
$db_password="2";//database password
$db_database="2"; //database name

@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);

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






Link to comment
Share on other sites

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.

Link to comment
Share on other sites

i tried a fast test with this


$link = mysql_connect("localhost", "1", "2");
$db_selected = mysql_select_db("3", $link);
if (!$db_selected) { die ('Database access error : ' . mysql_error());}

but i still get the odd caracters..i will continue with more tests

Link to comment
Share on other sites

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 
    } while ($data = fgetcsv($handle,1000,",","'")); 

    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"> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> 
<title>Import a CSV File with PHP & MySQL</title> 


<?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" /> 



but i still don't get why csv works better the xls..it seems to that what i find in google...

Link to comment
Share on other sites

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


Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.