2007年10月11日星期四

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寫入資料庫。
    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();//寫入資料庫
    }
    
  3. 備註:
    "require_once 'Excel/reader.php';"這一行若出現找不到"Spreadsheet/Excel/Reader/OLERead.php"的錯誤訊息(我下載時的版本有出現這個錯誤),
    請修改 Excel/reader.php 這個檔,將裡面的
    require_once 'Spreadsheet/Excel/Reader/OLERead.php';
    
    改為
    require_once 'oleread.inc';
    

2 則留言:

  1. 請問您用csv_importer有出現phpmyadmin中文字變成亂碼的問題嗎?

    我phpmyadmin用的是UTF-8

    回覆刪除
  2. 感謝您,很棒的文章,很受用。

    回覆刪除