localにあるmysqlのデータをきれいなExcelデータにする方法

Image

データの一覧を頼まれて、xamppで動かしているlocalにあるmysqlのデータをphpmyadminからExcelにエクスポートしようとしたのですが、「あ、、あれ?Microsoft Excel 2000 が…ない?」ということに気づきました。あれー確かあるはずなんだけどと思ったのですが、xamppのバージョン5.2のphpmyadminにはあるのですがバージョン5.3のphpmyadminにはない!ということに今日気づきました。

ということでmysqlのデータをExcelにする方法を検討して実行してみました。

ExcelがだめならCSVがあるじゃないの…(>_<)

Image

ExcelがだめならCSVがあるじゃないの!と思ってCSVにしてみたのですが結論から行くとダメでした。色々やればできるかもしれないですが、コードを変換したりプログラム的なものを書いたりとやたら手間がかかりそうです。以前のphpmyadminでExcelにエクスポートしたときは保存したファイルをダブルクリックするだけで警告は出るものの、そのままExcelで開けて文字化けも起こっていませんでした。

ODBCがあった!

という時点でさらにググったところいくつかのページでODBC経由で、という記事がありました。そうか~ODBCがあった!と気づきました。何度かAccessExcelVbmysqloracleのデータベースに接続してデータを利用したことがあったのでODBCでやろう!と思いました(^^)/

【最初の1回だけ!】mysql用のODBCドライバをダウンロード

http://dev.mysql.com/downloads/connector/odbc/

Image
上記ページから自分の環境に合うODBCドライバをダウンロードします。私はwindows7なので、Windows (x86, 64-bit), MSI Installer というバージョンをダウンロードします。

Image

ダウンロードする際にログインを求められます。Oracleのアカウントを作ってあればそのアカウントで、作っていなければここから登録してからログインします。

Image

ログインすると、PCに指定したファイルを保存することができるようになります。

【最初の1回だけ!】ODBCのインストール

Image

ダウンロードした mysql-connector-odbc-5.3.4-winx64.msi というファイルをダブルクリックします。

Image

実行 をクリック

Image

Next をクリック

Image

I accept の方にチェックを付けて Next をクリック

Image

Typical(デフォルト) のまま Next をクリック

Image

Install をクリック

Image

インストールが終了しました(^^)/

【データベースごとに1回だけ!】ODBCの設定

ODBCの設定です。ODBCは使うデータベースごとに1回だけ設定をすればそれ以降はその設定を使うことが出来るようになります。

Image

スタートメニューをクリック

Image

コントロールパネル をクリック

Image

システムとセキュリティ をクリック

Image

管理ツールを クリック

Image

データソース(ODBC)をダブルクリック

Image

追加 をクリック

Image

MySQL ODBC 5.3 Unicode Driver をクリック

Image

Data Source Name後で見てこの接続が分かる名前を付けます。私はlocalhostにある a_testdummy というデータベースへの接続なので、 localhost_a_testdummy という名前にしました。

TCP/IP Server に localhost

User にデータベースのユーザー名、Passwordにデータベースのパスワードを入れます。この場合ユーザー名がrootパスワードがなしでした。

ここまで入力すると Database のリストボックスでデータベースを選べるようになりますので使いたいデータベースを選びます。

Image

Test をクリックして接続を確認します。

Image

成功するとこの画面になります。失敗の時は設定を見直してください。

Image

Details ボタンをクリックします

Image

Character Set の欄で utf8 を選びます。この設定をしておかないと、文字化けが起こってしまいます(^_^;。

ExcelにODBC経由でMySQLのデータを取り込む

新しくExcelファイルを開きます。

Image

リボンの データ タブをクリックします。その他のデータソース 三角をクリックして、Microsoft Queryをクリック

Image

データソースの選択画面で先ほど作った接続、 localhost_a_testdummy を選択して OK をクリック

Image

このデータベースにはテーブルが2つあるので参照したい方の nc20m_coworking をクリックしてから をクリック

Image

次へ をクリック

Image

次へ をクリック

Image

次へ をクリック

Image

この画面で、今の設定を次にも使えるようにします。

Image

クエリの保存 をクリック
Image

とりあえず表示された名前のまま保存しました。

Image

この画面では何もせずに OK をクリック
Image

じゃーん!なんとこんなきれいな形でMySQLのデータが表示されました。

Image

通常のExcelファイルとしても保存できます。

先ほど保存したクエリを使ってみます。新しいExcelファイルを開いて、

Image

リボンのデータタブから、既存の接続をクリックします

Image

既存の接続が表示されるので、先ほど保存したクエリを選択して 開く をクリックします。

Image

OKをクリック。

Image

新しいExcelにも先ほどと同じようにMySQLからデータが貼り付けられました(^^)/

まとめ:インストールと設定は長いけども、それが終われば次回からはExcelから読み込むだけなので簡単

インストールと設定は割と長くてちょっと嫌になるかもしれませんが(^_^;、説明すると長くても実際にやると5分ぐらいで終わると思います。それから先はExcelのデータタブからMySQLに接続してデータを参照できるのでとても簡単で、便利だと思います(^^)/

兎本美佳

ブログを見た人がそのままできたらいいなと思って、できるだけ丁寧に書いています。blogに書いたようなネタの有償対応のご相談は「ゆうそうと」へいただければと思います(^^)/
無償での対応をご希望の場合は、コメントをいただけましたら可能な場合はコメントを返させていただきます。

シェアする

  • このエントリーをはてなブックマークに追加

フォローする

スポンサーリンク
スポンサー広告
ブログをメールで購読

メールアドレスを記入して購読すれば、更新をメールで受信できます。

261人の購読者に加わりましょう

コメント

  1. tin より:

    逆にエクセルで編集して文字化けさせずにインポートさせる事もできるのでしょうか?

    • 兎本美佳 兎本美佳 より:

      tinさん、おはようございます。インポートをCSVから行ったことがなかったのですがどうなるのかなーと思ってやってみました。

      インポートさせるためにはまず、エクセルで編集したファイルを CSV の形式で保存します。
      phpmyadminでインポートしたいテーブルを表示させてから インポート タブをクリック
      インポートするファイル で保存したCSVを指定
      そのすぐ下にある ファイルの文字セット で SHIFT_JIS を選択
      一番下の 実行 をクリック

      これで文字化けせずにインポートすることができました。ただ、IDが同じものが既にそのテーブルに存在していればエラーになりますので書き換えをしたければ該当するデータを削除しておく必要があります。データを削除してしまってインポートが失敗するとこわいのでバックアップを取って失敗してもいいようにしてから実行するといいかと思います。

      • tin より:

        なるほど!
        詳しくありがとうございます。インポート時に自分はshiftJISなんだよ。と指定してあげれば、自動的に変換してmysqlの文字コード(utf-8等)にしてくれるという事ですね。

        でも確かに、インポートだとファイル更新の際に一度削除する必要があるので、他の方法を使ったほうがいいかもです。

        僕はmysql使いたての頃はエクセルをcsvインポート(日本語は読み込まれず空欄に)していて、
        しばらくしてからは、練習がてら、phpmyadmin上でプログラムを打って更新する画面で上書き保存をするようになりました。できることはその2つだけです。

        しかし、ある程度一気に更新したい時とか、新しいのも追加したい時とか、もっと効率よくする方法はないかなぁと模索中です…。

        なんて。

        ご教授ありがとうございました。

        • 兎本美佳 兎本美佳 より:

          tinさん、こんにちは。私がよくやっているのはExcelの関数かなんかで update文 を作っちゃうことです。文章だけだとうまく説明できませんが、update文の決まり切ったところは1行目にかいちゃって、値が変わるところだけ a3 とか書いておいて式を引っ張ると、a4,a5,a6…と変わっていくようにしてupdateのSQLを作って、phpmyadminで実行させるようなやり方です。

          • tin より:

            ちょっとイメージしにくいですが、確かにエクセルでプログラム文を作っちゃうってのはいいかも知れませんね。
            基本的な部分ができたら、データをダウンロードしてエクセルで更新して、更新した部分と追加した文を判別して自動的にプログラム文を作ってくれるようなエクセルを作れたらいいかも知れません。
            まだ思いつきませんけど。

          • 兎本美佳 兎本美佳 より:

            tinさん、おはようございます。プログラム文ではなくってupdate文とかをだーっと作っちゃう関数をよく使います。VBAですらないのでわりとかんたんです。コメントだけだと分からないですよね(^_^;うまくせつめいできなくて。今度blogにでも書いてみようかなと思ってます。

          • tin より:

            兎本さんおはようございます。
            あ、そうか。僕はupdate文もプログラム文だと思って認識してました(笑)
            おもしろそうですね。ブログ楽しみにしています!
            お付き合いいただきありがとうございました。

  2. 安西聡 より:

    はじめまして.
    安西といいます.現在学生です.
    記事を書いていただきありがとうございます.
    この記事を参考にして進めていこうと思っているのですが,mysql-connector-odbcは無料で使えるのでしょうか.
    初歩的な質問で申し訳ありませんが,返信よろしくお願い致します.

    • 安西聡 より:

      この質問はなしにさせてください汗.
      申し訳ありません.
      途中まで記事のとおりに進めさせていただいてるのですが,data source configuration のところでうまくいきません.どのような問題が考えられますでしょうか.
      教えていただければ幸いです.よろしくお願い致します.

      • 兎本美佳 兎本美佳 より:

        安西聡さん、こんにちは!

        data source configuration のところでうまくいきません ということですが状況がよくわからないため、なんともアドバイスできません。エラーが出ているとしたらどんなメッセージがでているか、とかあるいは期待していた動きはこうだけど、実際にはこうなっているとかなどの状況をお知らせいただけますか?

        • 安西聡 より:

          よくわからない質問をしてしまい,申し訳ありません.
          connection failed
          [MySQL][ODBC 5.3(w Driver]Can’t connect to MySQL server on ‘localhost’ (10061):
          と出ます.
          これは何がダメなのでしょうか.
          返信よろしくお願い致します.

トップへ戻る