HOWTO · MySQL
將 Excel 檔案匯入 MySQL 資料庫
你將瞭解將資料從 excel 檔案匯入 MySQL 資料庫的不同且最簡單的方法。
本頁內容
若要將 Excel 活頁簿匯入 MySQL,可以先將工作表另存為 CSV,再使用 LOAD DATA 或 phpMyAdmin 匯入;也可以使用 Sqlizer.io 將活頁簿轉換為 SQL。兩種 CSV 匯入方式都需要相容的目標資料表;Sqlizer.io 則直接處理活頁簿,但產生的 SQL 應先檢查再執行。以下步驟使用的學生資料表包含 id、firstname、lastname、gender 和 city 欄位。
使用 LOAD DATA 陳述式匯入資料
將工作表另存為 CSV(以逗號分隔)。確認 CSV 欄位的順序和意義都與目標資料表相符。此範例假設 CSV 有標題列、欄位以逗號分隔、文字以雙引號括住,並使用 Unix 換行符號。範例也假設目標資料表在首次匯入前沒有資料;再次執行匯入仍可能新增重複資料,除非先移除既有資料或設定重複資料處理方式。請依照實際儲存的檔案調整格式選項。
載入資料前,可以使用 Python 標準函式庫預覽一筆代表性的 CSV 資料列。這個簡短範例使用的欄位名稱與範例資料表相同:
import csv
from io import StringIO
sample = StringIO("id,firstname,lastname,gender,city\n1,Ada,Lovelace,F,London\n")
row = next(csv.DictReader(sample))
print(f"{row['firstname']} {row['lastname']} — {row['city']}")
Ada Lovelace — London
此預覽用來確認標題名稱和值是否依預期讀取;它不會連線至 MySQL,也不能取代下方的匯入陳述式。
MySQL 8.0.27 範例使用 Windows 伺服器的上傳目錄。LOAD DATA INFILE 會從 MySQL 伺服器的檔案系統讀取指定路徑,因此檔案必須能由伺服器存取,並符合 secure_file_priv 設定的限制。在 MySQL Workbench 中,請於連線至該伺服器的查詢分頁執行陳述式;Workbench 不會自動讓伺服器存取工作站上的路徑。透過命令列用戶端執行時,也適用相同的伺服器端路徑規則。若使用 LOAD DATA LOCAL INFILE,則由用戶端讀取檔案並傳送給伺服器,而且用戶端和伺服器設定都必須允許本機載入。若遇到存取遭拒,請參閱 MySQL 的 LOAD DATA 安全性指南。
範例檔案使用的陳述式為 LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/students.csv' INTO TABLE tb_students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;。請依照你的環境替換路徑、資料表名稱、分隔符號、文字括住符號和換行符號。若資料以定位字元分隔,請使用 FIELDS TERMINATED BY '\t';若以單一空格分隔,請使用 FIELDS TERMINATED BY ' '。Windows 的 CSV 檔案常使用 \r\n 換行符號;若資料列未如預期分開,請改用檔案實際採用的換行符號。IGNORE 1 ROWS 會略過標題列,但不會依欄位名稱對應資料。若檔案未包含 id,或欄位順序不同,請在資料表名稱後指定相符的目標欄位,並確認自動產生的值已妥善處理。
載入後,請在 Workbench 檢查資料表,或執行查詢 SELECT id, firstname, lastname, gender, city FROM tb_students LIMIT 10;。將資料列與 CSV 比對,並在使用資料前確認匯入的資料列數。
方法二:使用 Sqlizer.io 匯入資料
若想轉換活頁簿而不先另存為 CSV,請使用 Sqlizer.io。選取活頁簿和目標資料庫類型 MySQL,接著指定第一列是否包含標題。選擇目前的工作表,或輸入工作表名稱(例如 students),再選取整個工作表或要轉換的儲存格範圍。設定目標資料表名稱並產生 SQL。
執行前請檢查產生的陳述式:確認資料表定義、欄位名稱、值,以及建立資料表或檢查資料表是否存在的處理方式。請將 SQL 對預定使用的資料庫執行,之後檢查資料表,並將有代表性的資料列與活頁簿比對。活頁簿可能包含機密資料;上傳至外部轉換服務前,請先考量資料敏感性和服務條款。
方法三:使用 phpMyAdmin 匯入資料
此方法也使用 CSV 檔案。先將工作表另存為 CSV,再開啟目標資料庫,並選取欄位與 CSV 相符的資料表。範例資料表 tb_students_phpmyadmin 包含 id、firstname、lastname、gender 和 city 欄位。在 phpMyAdmin 中,開啟資料表的 匯入 分頁,選取 CSV 檔案並將格式設為 CSV。調整格式選項以符合檔案內容,包括欄位分隔符號、文字括住符號、換行符號,以及是否略過標題列。按一下 執行 開始匯入。
若 CSV 欄位與資料表不相符,請在匯入前修正資料表或檔案;選擇 CSV 格式本身不會依標題名稱對應欄位。匯入完成後,請瀏覽資料表或執行查詢,並將數筆值和資料列數與來源檔案比對。
結論
若能將工作表另存為 CSV,且 MySQL 伺服器可以存取該檔案,可選擇 LOAD DATA;若要透過瀏覽器匯入 CSV,則可使用 phpMyAdmin。Sqlizer.io 會將活頁簿轉換為 SQL,供你檢查後在預定使用的資料庫中執行。無論採用哪種方式,都要讓來源欄位和格式符合目標資料表,並確認匯入的資料列。