Jamyy's Weblog

將 MySQL 資料表內容匯出為 CSV 檔案

by Jamyy on 九月.07, 2015, under Linux

  1. 直接將 mysql 查詢結果儲存為 TXT 檔案, 再由 Libre Office Calc 或 MS Office Excel 讀入後轉存為 CSV 檔案
  2. 以 select into outfile 語法匯出資料表內容, 直接在 Linux CLI 產生 CSV 檔案

操作環境: CentOS, bash, mysql / mariadb

直接將 mysql 查詢結果儲存為 TXT 檔案, 再由 Libre Office Calc 或 MS Office Excel 讀入後轉存為 CSV 檔案

Step 1. echo "select * from ps_customer" | mysql -u root -p -A prestashop > result.txt
Step 2. 由 Libre Office Calc / MS Office Excel 讀入 result.txt (Tab 分隔)
Step 3. 於 Libre Office Calc / MS Office Excel 另存檔案為 CSV 格式

註: 若 SQL Query 語法較複雜, 可先將 SQL 內容寫入檔案再由 mysql 讀入. 例如:
mysql -u root -p -A prestashop < complex-query.sql > result.txt

以 select into outfile 語法匯出資料表內容, 直接在 Linux CLI 產生 CSV 檔案

建立工作路徑
# mkdir /tmp/csv
# chown mysql /tmp/csv

範例一: 匯出所有欄位

匯出 column_name
mysql> use mysql
mysql> select group_concat(concat(column_name)) into outfile '/tmp/csv/header.txt' from information_schema.columns where table_name='ps_customer' and table_schema='prestashop';

匯出 table 內容
mysql> use prestashop
mysql> select * into outfile '/tmp/csv/content.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n' from ps_customer;

範例二: 匯出指定欄位

匯出 column_name 與 table 內容
mysql> use prestashop
mysql> select 'id_customer,firstname,lastname,email,birthday' into outfile '/tmp/csv/header.txt';
mysql> select id_customer, firstname, lastname, email, birthday into outfile '/tmp/csv/content.txt' fields terminated by ',' enclosed by '"' lines terminated by '\n' from ps_customer;

彙整 header 與 content
# cd /tmp/csv/
# (sed 's/^/"/;s/$/"/;s/,/","/g' header.txt; cat content.txt) > result.csv
# unix2dos result.csv



Ref:



:,