現代標準流程：CSV + LOAD DATA INFILE (最高效)

原文「逐欄另存 txt 再 PHP 迴圈 INSERT」極度低效且易出錯。標準作法：

步驟 1：Excel 另存為 CSV (UTF-8)

• Excel → 「另存新檔」→ 選「CSV UTF-8 (逗號分隔) (*.csv)」。
• 若有逗號、換行、雙引號，Excel 會自動加雙引號包裹並轉義。
• 確認編碼為 UTF-8 (無 BOM 最佳)。

步驟 2：建立對應資料表

CREATE TABLE `excel_import` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `col_a` VARCHAR(100) NOT NULL DEFAULT '',
  `col_b` VARCHAR(100) NOT NULL DEFAULT '',
  `col_c` VARCHAR(100) NOT NULL DEFAULT '',
  `col_d` VARCHAR(100) NOT NULL DEFAULT '',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步驟 3：一條 SQL 匯入 (秒級百萬行)

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE `excel_import`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'  -- Windows CSV 通常 CRLF
IGNORE 1 LINES              -- 跳過標題行
(`col_a`, `col_b`, `col_c`, `col_d`);

必要條件：MySQL 伺服器與客戶端皆啟用 local_infile=1；PHP PDO 連線需加 PDO::MYSQL_ATTR_LOCAL_INFILE => true。

PHP 程式化匯入 (需資料清洗/轉換時)

<?php
function importCsvToMysql(string $csvPath, PDO $pdo, string $table, array $columns, int $batchSize = 1000): int
{
    $handle = fopen($csvPath, 'r');
    if (!$handle) throw new RuntimeException("無法開啟 $csvPath");
    
    // 讀取標題行對應欄位 (可選)
    $header = fgetcsv($handle);
    $colMap = array_flip($header); // 欄位名 => 索引
    
    $placeholders = implode(',', array_fill(0, count($columns), '?'));
    $sql = "INSERT INTO `$table` (`" . implode('`,`', $columns) . "`) VALUES ($placeholders)";
    $stmt = $pdo->prepare($sql);
    
    $imported = 0;
    $batch = [];
    
    while (($row = fgetcsv($handle)) !== false) {
        // 資料對應與清洗
        $data = [];
        foreach ($columns as $col) {
            $val = $row[$colMap[$col] ?? 0] ?? '';
            // 例：去除首尾空白、轉換編碼、日期格式化等
            $data[] = trim($val);
        }
        $batch[] = $data;
        
        if (count($batch) >= $batchSize) {
            $pdo->beginTransaction();
            foreach ($batch as $record) {
                $stmt->execute($record);
            }
            $pdo->commit();
            $imported += count($batch);
            $batch = [];
        }
    }
    
    // 餘數
    if ($batch) {
        $pdo->beginTransaction();
        foreach ($batch as $record) $stmt->execute($record);
        $pdo->commit();
        $imported += count($batch);
    }
    
    fclose($handle);
    return $imported;
}

// 使用
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
    PDO::MYSQL_ATTR_LOCAL_INFILE => true, // 關鍵！
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$count = importCsvToMysql('data.csv', $pdo, 'excel_import', ['col_a','col_b','col_c','col_d']);
echo "成功匯入 $count 筆";
?>

進階工具推薦 (免寫代碼)

工具	特點	適用場景
MySQL Workbench	GUI 匯入精靈、欄位對應、預覽	一次性、中小檔案
phpMyAdmin	Web 介面、支援 CSV/CSV.gz	無 CLI 權限環境
csvkit (csvsql)	命令列、自動建表、型別推斷	`csvsql --db mysql://user:pass@host/db --insert data.csv`
Laravel Excel (Maatwebsite)	PHP 匯入/匯出神器、Chunk 讀取、佇列、驗證	Laravel 專案長期維護
Python pandas + SQLAlchemy	資料科學級清洗、型別推斷、大塊寫入	複雜 ETL、資料分析流程

常見坑點避雷

1. 編碼不匹配：Excel 另存 CSV 預設可能是系統編碼 (Big5/GBK)，請明確選「CSV UTF-8」。
2. 換行符差異：Windows CRLF (\r\n)、Linux LF (\n)、Mac 舊 CR (\r)，LOAD DATA 需正確指定 LINES TERMINATED BY。
3. 欄位包含分隔符/引號：CSV 標準要求雙引號包裹並雙引號轉義 (\"\" 代表 \")，fgetcsv 自動處理。
4. 大數值科學記號：Excel 長數字 (身分證、手機) 會變科學記號，匯入前在 Excel 設定欄位格式為「文字」。
5. 日期格式：Excel 日期為序列數，匯出 CSV 可能變「2023/10/18」或「44213」，需統一轉換。

結語

Excel → MySQL 匯入，**核心是「CSV 為中介格式」+「LOAD DATA INFILE 批次載入」**。拋棄逐行 PHP INSERT 迴圈，擁抱資料庫原生批次載入能力，效能提升 100-1000 倍。配合成熟工具 (Laravel Excel, csvkit, pandas) 處理清洗轉換，才是專業資料工程態度。