喵喵笨兔 の 喵喵的家~遊戲天地 - 首頁(上版面)
喵喵笨兔 の 喵喵的家~遊戲天地 - 首頁(左版面)
首  頁 最新消息 遊戲攻略 遊戲影片 遊戲圖片 下載區 學習分享 留言板 FB粉絲團
 喵喵笨兔 の 喵喵的家~遊戲天地 - Excel 檔直接匯入 MySQL 資料庫:不用轉檔、不會中文和日韓文亂碼...

Excel 檔直接匯入 MySQL 資料庫:不用轉檔、不會中文和日韓文亂碼...

Excel 檔直接匯入 MySQL 資料庫 

  • 一般 Excel 轉 CSV 檔再匯入MySQL 若發生中文亂碼,可用筆記本 (notepad) 開啟 CSV 另存時,編碼選 UTF-8 即可解決。但若有日文、韓文....等其它國語言,則一樣是亂碼。笨兔編修的此轉換程式,只要是 Excel 內可看到的語言(日文、韓文....),全都可完整的匯入資料庫。
  • 此是配合 PHPExcel 來修改編寫的。
    phpexcel 官網:https://phpexcel.codeplex.com/
    phpexcel 下載:https://github.com/PHPOffice/PHPExcel
    本小站下載 PHPExcel

  • 本站下載內含笨兔寫好的二個轉換程式 test.php (自動建立資料表及欄位)、test1.php (須先自行建立資料表及欄位) 和 phpexcel 目錄,把它放在您本機架站目錄內 (若要放在雲端伺服器使用,那就自行去修改程式碼囉!)。 如笨兔放在本機 MySQL 架站的 C:\AppServ\www\MyCollections\ 內。

  • 輸入 http://localhost/mycollectionphp/test.php 和http://localhost/mycollectionphp/test1.php 即可開啟使用。
test.php 範例程式碼:(自動建立資料表及欄位)
  • Excel 檔名即為 MySQL 資料庫內的資料表名稱。請勿用中文檔名。
  • Excel 內第一行各欄位名稱,即為資料表的欄位名稱,預設皆為文字 (text) 型態。若須更改再自行進 MySQL 更改。
  • 高手所寫的 PHPExcel 主要是以 office 2007 為主,所以儘量用 .xls 來匯入。其實用.xlsx 也可以完整的成功匯入,只是會出現幾行錯誤警告訊息。
  • 資料表若存在,會出現警告訊息。但只要欄位名稱都相符,也可成功匯入。
  • 同檔案重覆匯入,或不同檔案但欄位名稱皆相同匯入,則會在最後一筆之後匯入,筆數因此會累增。
  • 詳情看程式碼內說明,須修改或注意的為紅色字部份,只要更改您的資料庫資料即可。範例設定的 MySQL 相關名稱如下。MySQL 連線部份,可參考笨兔另一篇隨手筆記【db_002】PHP 連接 MySQL 資料庫
    • 伺服器名稱: localhost
    • 使用者帳號: root
    • 使用者密碼: 12345678
    • 資料庫名稱: my_collection
<?php 
$dbhost = "localhost"; //改成您的資料庫資料 $dbuser = "root"; $dbpass = "12345678"; $dbname = "my_collection"; $conn=mysql_connect($dbhost,$dbuser,$dbpass) or die('Error with MySQL connect'); mysql_query("SET NAMES 'UTF8'"); mysql_select_db($dbname); require_once 'PHPExcel/Classes/PHPExcel.php'; //必導入的PHPExel if($_GET['excelfile']!=NULL){ $file=$_GET['excelfile']; //完整路徑檔名 $pos=mb_strrpos($file,"\\"); //路徑最後出現\的位置 $pos2=mb_strrpos($file,"."); //路徑最後出現.的位置 $len=mb_strlen($file); $fileName=mb_substr($file,$pos+1,($pos2-$pos-1)); //擷取檔案名(不含副檔名) $fileName1=mb_substr($file,$pos+1); //擷取檔案名(含副檔名) try { $objPHPExcel = PHPExcel_IOFactory::load($file); } catch(Exception $e) { die('Error loading file "'.pathinfo($file,PATHINFO_BASENAME).'": '.$e->getMessage()); } $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); foreach($sheetData as $key => $col) { if ($key != 0){ //0表示從Excel第一行開始讀取,1從第二行 $a = array(); foreach ($col as $colkey => $colvalue) { array_push($a,$colvalue); } //讀取第1行,把它拿來當資料表的欄位名,預設都為文字text型態 if($key=='1'){ $i=count($a); //計算有幾欄 for($j=0;$i>$j;$j++){ if($j==0){ $table_col="`".$a[$j]."` text"; //第1欄不加,分隔 $table_col1="`".$a[$j]."`"; }else{ $table_col=$table_col.",`".$a[$j]."` text"; //第2欄後都加,分隔 $table_col1=$table_col1.",`".$a[$j]."`"; //此是用來寫入資料用的欄位 } } //建資料表 $sql = 'CREATE TABLE `'.$fileName.'` ('.$table_col.')'; //若資料表已存在也沒關係,只是每個欄位名稱要和您設在EXCEL第一行的一樣,才能成功寫入資料 if (mysql_query($sql)){ echo '資料表 '.$fileName.' 建立成功'; }else { echo '<font color="red">資料表 '.$fileName.' 建立失敗 (可能已存在了!)</font>'; } }else{ $i=count($a); for($j=0;$i>$j;$j++){ //讀取每行各欄位資料 if($j==0){ $table_data="'".addslashes($a[$j])."'"; //第1欄不加,分隔 }else{ $table_data=$table_data.",'".addslashes($a[$j])."'"; //第2欄後都加,分隔 } } $sql ="INSERT INTO `".$fileName."` (".$table_col1.") VALUES (".$table_data.")"; $result = mysql_query($sql) or die(mysql_error()); } } } } ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>喵喵笨兔 - Excel 檔直接匯入 MySQL </title> </head> <body> <h3 align="center">喵喵笨兔 - Excel 檔直接匯入 MySQL (自動建立資料表及欄位)</h3> <div> <form method="get"> <label for="fileField">要轉換的 EXCEL 檔 (勿用中文檔名):</label> <input type="file" name="excelfile" id="excelfile"> <input type="submit" name="submit" id="submit" value="開始匯入"> </form> </div> </body> </html>

【範例結果】

  • 以有日韓文內容的 abc.xls 為例,第一行先輸入要成為資料表的欄位名。
  • 直接將路徑指向您電腦內要匯入 MySQL 的 Excel 檔,再按開始匯入即可完成。


test1.php 範例程式碼:(須先在 MySQL 內自行建立資料表及欄位)
  • 這須要自行先在 MySQL 內建立資料表及欄位,並且要進程式碼內修改 Excel 檔名,及更改對應的資料表名、欄位。直接看程式碼吧!笨兔有附加說明,有興趣者自行參研唄!須修改或注意的為紅色字部份
  • <?php $dbhost = "localhost";  //改成您的資料庫資料
    $dbuser = "root";
    $dbpass = "12345678";
    $dbname = "my_collection";
    $conn=mysql_connect($dbhost,$dbuser,$dbpass) or die('Error with MySQL connect');
    mysql_query("SET NAMES 'UTF8'");
    mysql_select_db($dbname);
    
    require_once 'PHPExcel/Classes/PHPExcel.php';  //必導入的PHPExel
    
    //須先在您的MySQL建立對應的資料表及欄位名
    //改您要匯入的完整路徑檔案名稱 (放在同目錄內)
    $file = "abc.xls";
    try {
            $objPHPExcel = PHPExcel_IOFactory::load($file);
        } catch(Exception $e) {
            die('Error loading file "'.pathinfo($file,PATHINFO_BASENAME).'": '.$e->getMessage());
        }
        $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
    
     foreach($sheetData as $key => $col)
        {
    		if ($key != 0){    //0表示從Excel第一行開始讀取,1從第二行
    		$a = array();
            foreach ($col as $colkey => $colvalue) {
    		array_push($a,$colvalue);
            }
    			//匯入每筆資料,此例是四個欄位(由0開始),若三欄位就留三行,
    			//若五行則數字漸增加入如 $a4=addslashes($a[4]); 以此類推
    			$a0=addslashes($a[0]);
    			$a1=addslashes($a[1]);
    			$a2=addslashes($a[2]);
    			$a3=addslashes($a[3]);
    
    			//INTO 後面改您的建立好的資料表名稱,() 內改您的資料表欄位名稱
    			//VALUES() 內加入上面您抓取幾個欄位代號
    			$sql =("INSERT INTO 資料表名稱 (`欄位名1`,`欄位名2`,`欄位名3`,`欄位名4`) VALUES ('$a0','$a1','$a2','$a3')");
    			$result = mysql_query($sql) or die(mysql_error());	
    		}
        }
    ?>
    笨兔隨手筆記於 2017/12/03

    喵喵笨兔 の 喵喵的家~遊戲天地
    http://vv0817.tk
    http://vvv.lionfree.net
    http://qo3op.asuscomm.com/vvv
    https://uoedgpkhyfwzmd7zua8myg-on.drv.tw/vvv