情報科学屋さんを目指す人のメモ

方法・手順・解説を書き残すブログ。私と同じことを繰り返さずに済むように。

【MySQL】SELECTの結果をCSVファイルに出力してExcelで編集したい

Excel (41) Linux (29) MySQL (3)

日ごとの投稿数を、WordPressのデータベースからCSVファイルに出力しようとしたときのメモ。今回の用途的に、DBが巨大な場合や、TSV→CSV変換のエスケープはあまり考えていないので注意。

INTO OUTFILEを使う方法

最初に試したのは、直接SQLでファイル出力をする方法でした(SELECT ... INTO 構文)。

ひとまず、次のコマンドで、MySQLにログインします。

mysql -u DB_USER -p -D DB_NAME -h DB_HOST
Password: (DB_PASSWORD)

※ログインに必要なパスワードなどの情報は、wp-config.phpに書いてあります。

ログインしたら、MySQLに対して、次のようなクエリを投げました。

mysql> SELECT DATE(post_date) AS dt, COUNT(*) FROM posts WHERE '2016-01-01' <= post_date AND post_date < '2017-01-01' AND post_status = 'publish' GROUP BY DATE(post_date) ORDER BY dt ASC LIMIT 366 INTO OUTFILE '~/posts-2016.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

ポイントは、終盤部分です。「INTO OUTFILE '(ファイル指定)' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'」を加えて、出力しようとしています。

ただ、これは次のエラーになってしまいました。

ERROR 1045 (28000): Access denied for user 'username'@'localhost' (using password: YES)

このエラーは、このテーブルについてFILE権限がないことが原因のようでした。

ELECT の SELECT ... INTO OUTFILE 'file_name' 形式は、選択された行をファイルに書き込みます。このファイルはサーバーホスト上で作成されるため、この構文を使用するには FILE 権限が必要です。 引用元

GRANTしてね、と。

mysql -e オプションを使う

ただ、DB設定には変更を加えたくなかったのと、あくまでMySQLのあるホストのローカルにしか出力できないのが面倒だったので、mysqlコマンドの「-e」オプションを使ってみることにしました。

ただ、そのままだと、タブ区切りで出力されてしまうので、次の通り、sedで簡単にタブ文字をカンマに置き換える処理を挟んでみました。

mysql -u DB_USER -p -D DB_NAME -h DB_HOST -e "SELECT DATE(post_date) AS post_date, COUNT(*) FROM posts WHERE '2016-01-01' <= post_date AND post_date < '2017-01-01' AND post_status = 'publish' GROUP BY DATE(post_date) ORDER BY post_date ASC LIMIT 366" | sed 's/\t/,/g' > ~/posts-2016.csv

あとはそのCSVをWindows環境に持ってきてExcelで開いてみると、こうなりました(Excelで開くだけならTSVでもよかったかも)。

文字化け対策をする

この後、記事タイトルを出力しようとしたところ、Excel上で思いっきり文字化けをしてしまいました。

BOM付きUTF-8のCSVにすればいいのだろう、ということで、nkfコマンドを使って、「nkf --overwrite --oc=UTF-8-BOM ~/posts-2016.csv」を実行してあげました。とりあえず文字化け解消。

最終的なコマンド

最終的に、CSVのエスケープ関連処理も少しして、以下のようなSQLとなりました。

mysql -u DB_USER -p -D DB_NAME -h DB_HOST -e "(SELECT文)" | sed -r -e 's/"/""/g' -e 's/\t/","/g' -e 's/(^|$)/"/g' | nkf --oc=UTF-8-BOM > ~/result.csv

-eオプションで、指定のSQLを実行して、その結果のTSVをCSVに変換しつつ、BOM付きUTF-8に変換して、result.csvとして保存(出力)しています。

おまけ

いろいろ試してみたところ、CSVファイルが途中で切れてしまっていました。何がおかしいのかと思ったのですが、どうやら、GROUP_CONCATの結果の文字数制限に引っ掛かっていたようで、その切れ方が悪いのか、それ以降の結果がまるごと切れてしまっていたようでした。group_concat_max_lenの設定を書き換えてあげることで回避しました。

mysql -u DB_USER -p -D DB_NAME -h DB_HOST -e "set group_concat_max_len = 100000; SELECT DATE(post_date) AS dt, GROUP_CONCAT(post_title SEPARATOR ',') FROM posts WHERE '2016-01-01' <= post_date AND post_date < '2017-01-01' AND post_status = 'publish' GROUP BY DATE(post_date) ORDER BY dt ASC LIMIT 366" | sed -r -e 's/"/""/g' -e 's/\t/","/g' -e 's/(^|$)/"/g' | nkf --overwrite --oc=UTF-8-BOM > ~/posts-2016.csv

MySQLには、array_aggがないんですね。

参考

コメント(0)

新しいコメントを投稿




  • カテゴリ ナビ
  • 著者紹介

    ブログが趣味で、スマホアプリの利用中に発生するトラブルや不具合の対策手順や障害情報、 設定の変更方法などについて、解説記事をよく書いています。

    自分が困ったことをブログに書けば、次に困る人の参考になって、みんながみんな同じ苦労をせずに済む、というのが原点です。

    最近の関心は、スマホやパソコンが苦手な人の行動や思考、そしてそんな人を手助けする方法です。

    Amazonのアソシエイトとして、did2は適格販売により収入を得ています。

    RSS | Facebook | Twitter | About