カテゴリー
コンピューター

MySQL から Excel で使える CSV を作成します！できるだけ簡単に♪

MySQL には CSV 出力する機能が備わっていることに、初めて気がつきました。

喜び勇んで試してみたものの、Excel ではどうにもうまく読み込めません。特に、セル内で改行があると、次の行という扱いになってしまうのですよね。。。

そこで、何とかしてみたメモを残しておきます。

ポイント

  • 今時なので MySQL は UTF-8 です。Excel で読むために Shift-JIS に変更する必要があります。
  • セル内での改行は LF、行末の改行は CRLF にする必要があります。
  • セルを「”」で囲む場合があるため、「”」をエスケープする必要があります。

やってみた

まずは CSV 出力する MySQL です。2種類の方法がありますが、今回は SELECT を使う方法です。

mysql> SELECT * FROM wp_posts INTO OUTFILE "/tmp/wp_posts.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n";
Query OK, 784 rows affected (0.06 sec)

続いて、上のポイントを解決していきます。

# \\　→　\
sed -i -e 's|\\\\|\\|g' /tmp/wp_posts.csv

# \"　→　""
sed -i -e 's|\\"|""|g' /tmp/wp_posts.csv

# \CRLF　→　LF ^Mはこのままコピペしても意味ないです。「^M」は「Ctrl」+「V」→「Ctrl」+「M」と続けて入力します。
sed -i -e 's|\\^M||g' /tmp/wp_posts.csv

# Shift-JIS に変換
nkf -s --overwrite /tmp/wp_posts.csv

実は回りくどいことをしています。が、最重要ポイントを書きます。

SELECT のときに指定しなかった、「ESCAPED BY」というオプションが鍵です。

このオプションで、エスケープ文字を指定できるのですが、ポイントに書いた「”」をわざと指定せず、デフォルトの「\」としました。

エスケープ文字として「”」を指定しなかった理由はセル内の改行を発見するためです。

「”」のままだと、セル内改行時は「”CRLF」、行末時は「”CRLF」と同じとなってしまうため見分けがつかないのです。そこで、あえて「\」を指定することでセル内改行時と行末時の見分けをつけています。

ただ、完全ではありません。行末が「\CRLF」となる可能性もありますよね。この場合、、、どうしたらよいでしょうか？わかりません。

テーブルに入るデータの最後のカラムが最終更新日時で NOT NULL だから行末が「\CRLF」となることはありえない、などのテーブルでしたら気にしなくてもよいのですけれども。

sed コマンドの解説です。

エスケープ文字を「\」としましたのでデータ中の \ は \\ に、” は \” にエスケープされています。これを、\\ は \ に、\” は “” に変換しているのが1つ目と2つ目の sed コマンドです。

sed を用いたファイル内の置換で、わたくしが調べながら躓いたのが次の3点です。

  • たとえば ‘s|\\\\|\\|g’ の「g」をつけないと各行で最初に一致した箇所しか置換されません。つまり、最初の「\\」しか変換されません。
  • 「\」はエスケープする必要があります。「\\」とします。
  • 改行文字の CR は「^M」と表現されますが、指定するには「^」と「M」を打ち込んではだめです。「^M」はキーボードの「Ctrl」+「V」→「Ctrl」+「M」と続けて入力します。

あと、最後の sed コマンドを少しだけ補足しておくと、「\CRLF」→「LF」は引き算です。「\CR」をを抜けば、改行文字の残った部分「LF」がUnix の改行として働いてくれます。

課題

わたくしの今回の方法ですと、わかっているだけで2点問題があります。

  • 行末が「\CRLF」となった場合、次の行が行末のセルの続きとみなされてしまう。
  • MySQL でNULL のデータが \N と表現されてしまう。

2点目は、「ESCAPED BY」オプションを何も指定しないようにすれば回避できますが、エスケープができなくなる問題が発生します。

うーん、わかりません。

おわりに

参考にさせていただいたページです。大変ありがとうございます♪

コメントを残す