 |
excel to sql มีปัญหา ที่ id ซ้ำกัน อยากให้เซล ในexcel ไฟล์เดิม ไม่ซ้ำ id ในdatabase เข้ามาใน sql โดยยึด id เป็นหลักแก้ยังไงค่ะ ถ้า id ซ้ำกันข้อมูล colum นั้นจะไม่เข้า |
| |
 |
|
|
 |
 |
|
Code (PHP)
<head>
<title>ThaiCreate.Com PHP(COM) Excel.Application Tutorial</title>
</head>
<body>
<?
//*** Get Document Path ***//
$strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp
$OpenFile = "MyXls/MyCustomer.xls";
//*** Create Exce.Application ***//
$xlApp = new COM("Excel.Application");
$xlBook = $xlApp->Workbooks->Open($strPath."/".$OpenFile);
$xlSheet1 = $xlBook->Worksheets(1);
//*** Insert to MySQL Database ***//
$objConnect = mysql_connect("localhost","root","root") or die("Error Connect to Database");
$objDB = mysql_select_db("mydatabase");
for($i=2;$i<=5;$i++){
If(trim($xlSheet1->Cells->Item($i,1)) != "")
{
$strSQL = "";
$strSQL .= "INSERT INTO customer2 ";
$strSQL .= "(CustomerID,Name,Email,CountryCode,Budget,Used) ";
$strSQL .= "VALUES ";
$strSQL .= "('".$xlSheet1->Cells->Item($i,1)."','".$xlSheet1->Cells->Item($i,2)."' ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,3)."','".$xlSheet1->Cells->Item($i,4)."' ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,5)."','".$xlSheet1->Cells->Item($i,6)."') ";
mysql_query($strSQL);
}
}
//*** Close MySQL ***//
@mysql_close($objConnect);
//*** Close & Quit ***//
$xlApp->Application->Quit();
$xlApp = null;
$xlBook = null;
$xlSheet1 = null;
?>
Data Import/Inserted.
</body>
</html>
excel to sql มีปัญหา ที่ id ซ้ำกัน อยากให้เซล ในexcel ไฟล์เดิม ไม่ซ้ำ id ในdatabase เข้ามาใน sql โดยยึด id เป็นหลักแก้ยังไงค่ะ ถ้า id ซ้ำกันข้อมูล colum นั้นจะไม่เข้า
Tag : PHP
|
|
|
 |
 |
 |
 |
| Date :
2011-08-08 12:23:40 |
By :
ayumi |
View :
1996 |
Reply :
9 |
|
 |
 |
 |
 |
|
|
|
|
 |
 |
|
 |
 |
| |
|
|
|
| |
|
Code (PHP)
<head>
<title>ThaiCreate.Com PHP(COM) Excel.Application Tutorial</title>
</head>
<body>
<?
//*** Get Document Path ***//
$strPath = realpath(basename(getenv($_SERVER["SCRIPT_NAME"]))); // C:/AppServ/www/myphp
$OpenFile = "MyXls/MyCustomer.xls";
//*** Create Exce.Application ***//
$xlApp = new COM("Excel.Application");
$xlBook = $xlApp->Workbooks->Open($strPath."/".$OpenFile);
$xlSheet1 = $xlBook->Worksheets(1);
//*** Insert to MySQL Database ***//
$objConnect = mysql_connect("localhost","root","root") or die("Error Connect to Database");
$objDB = mysql_select_db("mydatabase");
for($i=2;$i<=5;$i++){
If(trim($xlSheet1->Cells->Item($i,1)) != "")
{
$strSQL = "SELECT * FROM customer2 WHERE CustomerID = '".$xlSheet1->Cells->Item($i,1)."' ";
$objQeury = mysql_query($strSQL);
$objResult = mysql_fetch_array($objQeury)
if(!$objResult)
{
$strSQL = "";
$strSQL .= "INSERT INTO customer2 ";
$strSQL .= "(CustomerID,Name,Email,CountryCode,Budget,Used) ";
$strSQL .= "VALUES ";
$strSQL .= "('".$xlSheet1->Cells->Item($i,1)."','".$xlSheet1->Cells->Item($i,2)."' ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,3)."','".$xlSheet1->Cells->Item($i,4)."' ";
$strSQL .= ",'".$xlSheet1->Cells->Item($i,5)."','".$xlSheet1->Cells->Item($i,6)."') ";
mysql_query($strSQL);
}
}
}
//*** Close MySQL ***//
@mysql_close($objConnect);
//*** Close & Quit ***//
$xlApp->Application->Quit();
$xlApp = null;
$xlBook = null;
$xlSheet1 = null;
?>
Data Import/Inserted.
</body>
</html>
ตามนี้ครับ เช็ค record ว่ามีอยู่หรือไม่
|
| |
|
|
| |
| Date :
2011-08-08 13:22:34 |
By :
webmaster |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ก็น่าจะถูกน่ะครับ ลืมปิด ; ด้วย
$objResult = mysql_fetch_array($objQeury);
|
| |
|
|
| |
| Date :
2011-08-08 14:22:55 |
By :
webmaster |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ขอบคุณค่ะ ลืม if(!$objResult) โทดค่ะ
|
| |
|
|
| |
| Date :
2011-08-08 14:50:24 |
By :
ayumi |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
|
| |
|
|
| |
| Date :
2011-08-08 15:03:57 |
By :
webmaster |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
excel to sql ถ้าอยากให้เซล ในexcel ไฟล์เดิม สามรถทับ id ในdatabase อันเก่า เข้ามาใน sql โดยยึด id เป็นหลัก
จะทำได้ไหมค่ะ
|
| |
|
|
| |
| Date :
2011-10-18 16:48:22 |
By :
ayumi |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
|
| |
|
|
| |
| Date :
2011-10-23 18:28:55 |
By :
ayumi |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ไม่ได้ ครับ
Fatal error: Call to a member function Item() on null in C:\AppServ\www\PHPExcel\PHPExcel\PHPExcelReadToMySQL.php on line 41
|
| |
|
|
| |
| Date :
2017-08-03 16:41:47 |
By :
pdeeya |
|
|
| |
|
|
|
|
|
| |
|
|
|
| |
|
|
|
| |
|
ยังซ้ำอยู่เลยครับ
<?php
/** PHPExcel */
require_once 'Classes/PHPExcel.php';
/** PHPExcel_IOFactory - Reader */
include 'Classes/PHPExcel/IOFactory.php';
$inputFileName = "pcnew.xlsx";
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
++$r;
foreach($headingsArray as $columnKey => $columnHeading) {
$namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
}
}
}
//echo '<pre>';
//var_dump($namedDataArray);
//echo '</pre><hr />';
$objConnect = mysql_connect("localhost","root","12345678") or die("Error Connect to Database");
$objDB = mysql_select_db("it");
for($i=2;$i<=5;$i++){
If(trim($xlSheet1->Cells->Item($i,1)) != "")
{
$strSQL = "SELECT * FROM ordercode WHERE A2 = '".$xlSheet1->Cells->Item($i,1)."' ";
$objQeury = mysql_query($strSQL);
$objResult = mysql_fetch_array($objQeury);
if(!$objResult);
{
$strSQL = "";
$strSQL .= "INSERT INTO ordercode ";
$strSQL .= "(A1,A2,A3,A4,A5,A6) ";
$strSQL .= "VALUES ";
$strSQL .= "('".$result["A1"]."','".$result["A2"]."' ";
$strSQL .= ",'".$result["A3"]."','".$result["A4"]."' ";
$strSQL .= ",'".$result["A5"]."','".$result["A6"]."') ";
mysql_query($strSQL);
}
}
}
//*** Close MySQL ***//
@mysql_close($objConnect);
//*** Close & Quit ***//
$xlApp->Application->Quit();
$xlApp = null;
$xlBook = null;
$xlSheet1 = null;
?>
Data Import/Inserted.
</body>
</html>
|
| |
|
|
| |
| Date :
2017-08-03 16:43:41 |
By :
pdeeya |
|
|
| |
|
|
|
|
|
| |
|
|
|
|
|
