i am trying to upload an xlsx file. using php and mysql .. but i have got an extra symbol in the database after a value .. like value is ETI5465 after upload it becomes ETI5465Â
if($_POST['action']=="addFile"){
$fname = $_FILES['sel_file']['name'];
$path_info=pathinfo($fname);
$ext=$path_info['extension'];
$chk_ext = explode(".",$fname);
if(strtolower($chk_ext[1]) == "xlsx")
{
require 'simplexlsx.class.php';
$filename = $_FILES['sel_file']['tmp_name'];
// $handle = fopen($filename, "r");
$xlsx = new SimpleXLSX($filename);
list($cols, $rows) = $xlsx->dimension();
foreach($xlsx->rows() as $k => $data) { // LOOP THROUGH EXCEL WORKSHEET
if($k >'7'){
if($_POST['upload']=="excel1" && $cols<>'10')
{
$newdate=date('m/d/Y H:i:s A',$xlsx->unixstamp($data[4]));
$sql = "INSERT INTO vehicle_fuel_expenses (`company_code_nr`, `cartao`, `placa`, `modelo`, `responsavel`, `data_hora`, `transacao`, `tipo`, `liberacao_Restricao`, `motorista`, `matricula`, `estabelecimento`, `cidade`, `quilometragem`, `horas`, `servico`,`valor`,`km_rodados`,`horas_trabalhadas`,`litros`,`km_litro`,`litros_Hora`,`valor_Litro`,`ia_1`,`ia_2`,`ia_3`,`ia_4`,`ia_5`) VALUES ('".$_SESSION['company']."','$data[0]','$data[1]','$data[2]','$data[3]','$newdate','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]',
'$data[13]','$data[14]','$data[15]','$data[16]','$data[17]','$data[18]','$data[19]','$data[20]','$data[21]','$data[22]','$data[23]','$data[24]','$data[25]','$data[26]')";
$inser=@mysql_query($sql) or die(mysql_error());
}
if($_POST['upload']=="excel2" && $cols=='3')
{
//$newdate=date('m/Y',$xlsx->unixstamp($data[4]));
$sql = "INSERT INTO vehicle_fuel_expenses_mensalidade1 (`company_code_nr`, `veículo`, `item_faturado`, `valor`) VALUES ('".$_SESSION['company']."','".addslashes($data[0])."','".addslashes($data[1])."','".addslashes($data[2])."')";
$inser=@mysql_query($sql) or die(mysql_error());
}
if($_POST['upload']=="excel3" && $cols=='10')
{
$sql = "INSERT INTO vehicle_toll_consumption_cost (`company_code_nr`, `placa`, `tag`, `prefixo`, `marca`, `categ`, `data`, `hora`, `rodovia`, `praca`,
`valor`) VALUES ('".$_SESSION['company']."','$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]')";
$inser=@mysql_query($sql) or die(mysql_error());
}
}
}
if($inser=='1')
{
echo '<script>alert("Importado com sucesso");</script>';
}
else
{
echo '<script>alert("Please Select A Valid File");</script>';
}
The problem is due to the encoding of the file and the character set used by the DB tables. For example, if the character set used by the DB tables are utf8_general_ci and the file from which data is read is encoded in UTF-8 (as it probably will be your files) immediately after the connection to the DB you must execute the command SQL to set the correct character set, in this case:
SET CHARACTER SET utf8