(MySQL) Excel匯入MySQL

https://xyz.cinc.biz/2007/10/excelmysql.html

Excel匯入MySQL

以下介紹兩種Excel資料放到MySQL的方式，但不管那一種方式，最重要的都是要注意Excel資料的編碼，與MySQL資料表的編碼，避免轉到資料庫後，變成亂碼。

方法1：將Excel轉存成CSV檔，再用phpmyadmin匯入。

1. 以下是用來測試的excel資料，裡面故意輸入一些特殊字元，也故意留了一個欄位沒有輸入值。
   
2. 首先，將excel檔另存成CSV檔。
   
3. 將剛剛另存的CSV檔轉成UTF8的編碼。轉換的方法有很多種，這邊介紹用記事本來轉換。
   用記事本，開啟CSV檔，然後選擇「另存新檔」 ，此時可發現編碼為「ANSI」，將編碼改成「UTF-8」後，再按  「存檔」即可。
   
4. 打開phpMyAdmin，到要匯入的資料表，確認資料表編碼也是UTF8，再按「輸入(Import)」。
   
5. 選擇剛剛的CSV檔。
   檔案的字集選「utf-8」，格式選「CSV」，
   欄位分隔符號「,」，內容分隔符號「"」，內容跳脫符號「"」。
   再按 「執行」。
   
6. 下圖為匯入的結果，一些特殊符號都能匯入，也無亂碼。
   

方法2：用程式去讀取excel的資料，再用程式將資料寫到資料庫。

1. 這邊最難的應該就是讀取excel檔的資料。
   這邊介紹用Spreadsheet_Excel_Reader去讀取excel資料，Spreadsheet_Excel_Reader之前看是在PEAR的套件庫裡面，後來在PEAR裡面就看不到了。
   不過在 http://sourceforge.net/projects/phpexcelreader/ 依然可以下載。
   (Spreadsheet_Excel_Reader目前只能讀取xls的檔，若是xlsx的檔，可用另一套PHPExcel處理。)
2. 該套件解壓縮後，有example.php檔的說明可以看套件用法。下面的程式碼是利用 Spreadsheet_Excel_Reader讀取xls檔資料後，再用PDO寫入資料庫。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

   require_once 'Excel/reader.php';   $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('UTF-8');//設定從excel的xls讀取出來的資料，用UTF8輸出 $data->setUTFEncoder('mb');//設定用mb_convert_encoding取代iconv，用來進行文字編碼的轉換 $data->read('Book1.xls');     //資料庫設定 $dsn = "mysql:dbname=test;host=127.0.0.1"; $user = "root"; $password = "test"; $driver_options = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8");//設定資料庫連線為UTF8   try {     $dbh = new PDO($dsn, $user, $password, $driver_options);    } catch (PDOException $e) {     echo 'Connection failed: ' . $e->getMessage(); }   $aa=''; $bb=''; $cc='';   $sth = $dbh->prepare('INSERT INTO test SET aa = :aa, bb=:bb, cc=:cc'); $sth->bindParam(':aa', $aa); $sth->bindParam(':bb', $bb); $sth->bindParam(':cc', $cc);   $numRows = $data->sheets[0]['numRows']; $numCols = $data->sheets[0]['numCols'];   for ($i = 1; $i <= $numRows; $i++) {  for ($j = 1; $j <= $numCols; $j++) {   $v = $data->sheets[0]['cells'][$i][$j];//第一列的第三個欄位為NULL，此時這邊會出現notice，可自行加判斷處理   if(NULL===$v) $v = "";//因為我的資料表結構不能儲存NULL，所以將NULL改為空字串   switch($j)   {    case 1:     $aa = $v;    break;    case 2:     $bb = $v;    break;    case 3:     $cc = $v;    break;   }  }  $sth->execute();//寫入資料庫 }

備註：
"require_once 'Excel/reader.php';"這一行若出現找不到"Spreadsheet/Excel/Reader/OLERead.php"的錯誤訊息(我下載時的版本有出現這個錯誤)，
請修改 Excel/reader.php 這個檔，將裡面的


1	require_once 'Spreadsheet/Excel/Reader/OLERead.php';

改為


1	require_once 'oleread.inc';