将excel文件导入数据库时出错 [英] error while importing excel file into database
本文介绍了将excel文件导入数据库时出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在将 excel 文件导入我的数据库.导入时出现错误您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 ' 999, , , 2009-02-03, 9, 133, 2, 0, 0, 0, 3, 1, 1, 1, 04/附近使用的正确语法02/2009，40816.0460995，' 第 1 行我做了一个回声查询，它是正确的.但是当我尝试将其插入数据库时，出现此错误.这是我的数据库结构
I am importing excel file to my database. While importing i get the error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 999, , , 2009-02-03, 9, 133, 2, 0, 0, 0, 3, 1, 1, 1, 04/02/2009, 40816.0460995,' at line 1 I did a echo for query and it is proper. But when i try to insert this into database, i get this error. Here is my database structure
CREATE TABLE `product` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`model` varchar(64) COLLATE utf8_bin NOT NULL,
`sku` varchar(64) COLLATE utf8_bin NOT NULL,
`upc` varchar(12) COLLATE utf8_bin NOT NULL,
`location` varchar(128) COLLATE utf8_bin NOT NULL,
`quantity` int(4) NOT NULL DEFAULT '0',
`stock_status_id` int(11) NOT NULL,
`image` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`manufacturer_id` int(11) NOT NULL,
`shipping` tinyint(1) NOT NULL DEFAULT '1',
`price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`points` int(8) NOT NULL DEFAULT '0',
`tax_class_id` int(11) NOT NULL,
`date_available` date NOT NULL,
`weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`weight_class_id` int(11) NOT NULL DEFAULT '0',
`length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
`length_class_id` int(11) NOT NULL DEFAULT '0',
`subtract` tinyint(1) NOT NULL DEFAULT '1',
`minimum` int(11) NOT NULL DEFAULT '1',
`sort_order` int(11) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '0',
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`viewed` int(5) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=50 ;
以及导入和插入数据库的代码
and code to import and insert into database
<?php
require_once 'reader.php';
$Host = "localhost";
$User = "user";
$Password = "password";
$DBname = "test";
$Link = mysql_connect ($Host, $User, $Password) or die (mysql_error());
mysql_select_db($DBname);
if(isset($_POST['submit']))
{
$filename = $_FILES['uploadedfile']['name'];
$TARGET_PATH = "uploads/";
//$filename = mysql_real_escape_string($filename);
$TARGET_PATH .= $filename;
if (move_uploaded_file($_FILES['uploadedfile']['tmp_name'],$TARGET_PATH))
chmod($TARGET_PATH, 0777);
{
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read($TARGET_PATH);
$count=0;
for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
if($j==1){
$product_id=$data->sheets[0]['cells'][$i][$j];
}
if($j==2){
$model=$data->sheets[0]['cells'][$i][$j];
}
if($j==3){
$sku=$data->sheets[0]['cells'][$i][$j];
}
if($j==4){
$upc=$data->sheets[0]['cells'][$i][$j];
}
if ($j==5){
$location=$data->sheets[0]['cells'][$i][$j];
}
if ($j==6){
$quantity=$data->sheets[0]['cells'][$i][$j];
}
if ($j==7){
$stock_status_id=$data->sheets[0]['cells'][$i][$j];
}
if ($j==8){
$image=$data->sheets[0]['cells'][$i][$j];
}
if ($j==9){
$manufacturer_id=$data->sheets[0]['cells'][$i][$j];
}
if ($j==10){
$shipping=$data->sheets[0]['cells'][$i][$j];
}
if ($j==11){
$shippingamount=$data->sheets[0]['cells'][$i][$j];
}
if ($j==12){
$price=$data->sheets[0]['cells'][$i][$j];
}
if ($j==13){
$points=$data->sheets[0]['cells'][$i][$j];
}
if ($j==14){
$tax_class_id=$data->sheets[0]['cells'][$i][$j];
}
if ($j==15){
$date_available=$data->sheets[0]['cells'][$i][$j];
}
if ($j==16){
$weight=$data->sheets[0]['cells'][$i][$j];
}
if ($j==17){
$weight_class_id=$data->sheets[0]['cells'][$i][$j];
}
if ($j==18){
$length=$data->sheets[0]['cells'][$i][$j];
}
if ($j==19){
$width=$data->sheets[0]['cells'][$i][$j];
}
if ($j==20){
$height=$data->sheets[0]['cells'][$i][$j];
}
if ($j==21){
$length_class_id=$data->sheets[0]['cells'][$i][$j];
}
if ($j==22){
$subtract=$data->sheets[0]['cells'][$i][$j];
}
if ($j==23){
$minimum=$data->sheets[0]['cells'][$i][$j];
}
if ($j==24){
$sort_order=$data->sheets[0]['cells'][$i][$j];
}
if ($j==25){
$status=$data->sheets[0]['cells'][$i][$j];
}
if ($j==26){
$date_added=$data->sheets[0]['cells'][$i][$j];
}
if ($j==27){
$date_modified=$data->sheets[0]['cells'][$i][$j];
}
if ($j==28){
$viewed=$data->sheets[0]['cells'][$i][$j];
}
}
$count++;
$Query72 ="insert into product (product_id,model,sku,upc,location,quantity,stock_status_id,image, manufacturer_id, shipping, shippingamount, price, points, tax_class_id, date_available, weight, weight_class_id, length, width, height, length_class_id, subtract, minimum, sort_order, status, date_added, date_modified,viewed) values('', '$model', '$sku', '$upc', '$location', $quantity, $stock_status_id, '$image', $manufacturer_id, $shipping, $shippingamount, $price, $points, $tax_class_id, $date_available, $weight, $weight_class_id, $length, $width, $height, $length_class_id, $subtract, $minimum, $sort_order, $status, $date_added, $date_modified, $viewed)";
echo $Query72;
mysql_query ($Query72) or die (mysql_error());
if (mysql_affected_rows() >= 1) {
print("ROW $count - update successful!");} else {print("ROW $count - update failed!");
}
}
}
echo "Updated Successfully";
}
?>
请帮助我为什么会出现此错误以及如何解决
Please help me why am getting this error and how can i resolve it
推荐答案
使用这个
$Query72 ="insert into product (product_id,model,sku,upc,location,quantity,stock_status_id,image, manufacturer_id, shipping, shippingamount, price, points, tax_class_id, date_available, weight, weight_class_id, length, width, height, length_class_id, subtract, minimum, sort_order, status, date_added, date_modified,viewed)
values('', '".$model."', '".$sku."', '".$upc."', '".$location."', '".$quantity."','".$stock_status_id."', '".$image."', '".$manufacturer_id."', '".$shipping."', '".$shippingamount."', '".$price."', '".$points."', '".$tax_class_id."', '".$date_available."', '".$weight."', '".$weight_class_id."', '".$length."', '".$width."', '".$height."','".$length_class_id."','".$subtract."','".$minimum."','".$sort_order."', '".$status."','".$date_added."','".$date_modified."','".$viewed."')";
希望对你有帮助.
发生此错误是因为您没有正确使用引号.在您的查询日期值必须在单引号中.
This error occurred because you are not using quotes properly. In your query date value must be in a single quote.
这篇关于将excel文件导入数据库时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
