|
|
|
ต้องการ Import Data... เพื่อ Update data ด้วยไฟล์ excel เข้า SQL Server 2008 |
|
|
|
|
|
|
|
จาก Code เป็นการ Insert Excel file to MSSQL
ผมอยากได้ Code Update data ด้วย Excel file ครับ
ขอบคุณครับ
Code (PHP)
<?php
/** PHPExcel */
require_once 'Classes/PHPExcel.php';
/** PHPExcel_IOFactory - Reader */
include 'Classes/PHPExcel/IOFactory.php';
$inputFileName = "myData.xls";
$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 />';
//*** Connect to MySQL Database ***//
$objConnect = mssql_connect("localhost","sa","") or die("Error Connect to Database");
$objDB = mssql_select_db("mydatabase");
$i = 0;
foreach ($namedDataArray as $result) {
$i++;
$strSQL = "";
$strSQL .= "INSERT INTO customer ";
$strSQL .= "(CustomerID,Name,Email,CountryCode,Budget,Used) ";
$strSQL .= "VALUES ";
$strSQL .= "('".$result["CustomerID"]."','".$result["Name"]."' ";
$strSQL .= ",'".$result["Email"]."','".$result["CountryCode"]."' ";
$strSQL .= ",'".$result["Budget"]."','".$result["Used"]."') ";
mssql_query($strSQL) or die(mysql_error());
echo "Row $i Inserted...<br>";
}
mssql_close($objConnect);
?>
Tag : PHP, Ms SQL Server 2008
|
ประวัติการแก้ไข 2016-04-01 14:39:43
|
|
|
|
|
Date :
2016-04-01 14:38:24 |
By :
jakkit |
View :
2877 |
Reply :
4 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Code (PHP)
<?php
ini_set('display_errors', 1);
error_reporting(~0);
$serverName = "localhost";
$userName = "sa";
$userPassword = '';
$dbName = "mydatabase";
$connectionInfo = array("Database"=>$dbName, "UID"=>$userName, "PWD"=>$userPassword, "MultipleActiveResultSets"=>true);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
die( print_r( sqlsrv_errors(), true));
}
$sql = "INSERT INTO customer (CustomerID, Name, Email, CountryCode, Budget, Used) VALUES (?, ?, ?, ?, ?, ?)";
$params = array($_POST["txtCustomerID"], $_POST["txtName"], $_POST["txtEmail"], $_POST["txtCountryCode"], $_POST["txtBudget"], $_POST["txtUsed"]);
$stmt = sqlsrv_query( $conn, $sql, $params);
if( $stmt === false ) {
die( print_r( sqlsrv_errors(), true));
}
else
{
echo "Record add successfully";
}
sqlsrv_close($conn);
?>
|
|
|
|
|
Date :
2016-04-03 22:14:51 |
By :
mr.win |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Code
|
|
|
|
|
Date :
2016-04-03 23:01:56 |
By :
mr.win |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ตอบความคิดเห็นที่ : 3 เขียนโดย : mr.win เมื่อวันที่ 2016-04-03 23:01:56
รายละเอียดของการตอบ ::
จากโค็ดเป็นการ Insert data ด้วย excel file
ผมต้องการโค็ด Update data ด้วย excel file
เช่นเดียวกัน ครับ
ขอบคุณครับ
Code (PHP)
<?php
/** PHPExcel */
require_once 'Classes/PHPExcel.php';
/** PHPExcel_IOFactory - Reader */
include 'Classes/PHPExcel/IOFactory.php';
$inputFileName = "myData.xls";
$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 />';
//*** Connect to MySQL Database ***//
$objConnect = mssql_connect("localhost","sa","") or die("Error Connect to Database");
$objDB = mssql_select_db("mydatabase");
$i = 0;
foreach ($namedDataArray as $result) {
$i++;
$strSQL = "";
$strSQL .= "INSERT INTO customer ";
$strSQL .= "(CustomerID,Name,Email,CountryCode,Budget,Used) ";
$strSQL .= "VALUES ";
$strSQL .= "('".$result["CustomerID"]."','".$result["Name"]."' ";
$strSQL .= ",'".$result["Email"]."','".$result["CountryCode"]."' ";
$strSQL .= ",'".$result["Budget"]."','".$result["Used"]."') ";
mssql_query($strSQL) or die(mysql_error());
echo "Row $i Inserted...<br>";
}
mssql_close($objConnect);
?>
|
|
|
|
|
Date :
2016-04-05 10:56:08 |
By :
jakkit |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Load balance : Server 04
|