データの一覧を頼まれて、xamppで動かしているlocalにあるmysqlのデータをphpmyadminからExcelにエクスポートしようとしたのですが、「あ、、あれ？Microsoft Excel 2000 が…ない？」ということに気づきました。あれー確かあるはずなんだけどと思ったのですが、xamppのバージョン5.2のphpmyadminにはあるのですがバージョン5.3のphpmyadminにはない！ということに今日気づきました。
ということでmysqlのデータをExcelにする方法を検討して実行してみました。
ExcelがだめならCSVがあるじゃないの…(>_<)
ExcelがだめならCSVがあるじゃないの！と思ってCSVにしてみたのですが結論から行くとダメでした。色々やればできるかもしれないですが、コードを変換したりプログラム的なものを書いたりとやたら手間がかかりそうです。以前のphpmyadminでExcelにエクスポートしたときは保存したファイルをダブルクリックするだけで警告は出るものの、そのままExcelで開けて文字化けも起こっていませんでした。
ODBCがあった！
という時点でさらにググったところいくつかのページでODBC経由で、という記事がありました。そうか～ODBCがあった！と気づきました。何度かAccessやExcelやVbでmysqlやoracleのデータベースに接続してデータを利用したことがあったのでODBCでやろう！と思いました(^^)/
【最初の1回だけ！】mysql用のODBCドライバをダウンロード
http://dev.mysql.com/downloads/connector/odbc/
上記ページから自分の環境に合うODBCドライバをダウンロードします。私はwindows7なので、Windows (x86, 64-bit), MSI Installer というバージョンをダウンロードします。
ダウンロードする際にログインを求められます。Oracleのアカウントを作ってあればそのアカウントで、作っていなければここから登録してからログインします。
ログインすると、PCに指定したファイルを保存することができるようになります。
【最初の1回だけ！】ODBCのインストール
ダウンロードした mysql-connector-odbc-5.3.4-winx64.msi というファイルをダブルクリックします。
実行 をクリック
Next をクリック
I accept の方にチェックを付けて Next をクリック
Typical（デフォルト） のまま Next をクリック
Install をクリック
インストールが終了しました(^^)/
【データベースごとに1回だけ！】ODBCの設定
ODBCの設定です。ODBCは使うデータベースごとに1回だけ設定をすればそれ以降はその設定を使うことが出来るようになります。
スタートメニューをクリック
「
コントロールパネル をクリック
システムとセキュリティ をクリック
管理ツールを クリック
データソース(ODBC)をダブルクリック
追加 をクリック
MySQL ODBC 5.3 Unicode Driver をクリック
Data Source Name に後で見てこの接続が分かる名前を付けます。私はlocalhostにある a_testdummy というデータベースへの接続なので、 localhost_a_testdummy という名前にしました。
TCP/IP Server に localhost
User にデータベースのユーザー名、Passwordにデータベースのパスワードを入れます。この場合ユーザー名がrootでパスワードがなしでした。
ここまで入力すると Database のリストボックスでデータベースを選べるようになりますので使いたいデータベースを選びます。
Test をクリックして接続を確認します。
成功するとこの画面になります。失敗の時は設定を見直してください。
Details ボタンをクリックします
Character Set の欄で utf8 を選びます。この設定をしておかないと、文字化けが起こってしまいます(^_^;。
ExcelにODBC経由でMySQLのデータを取り込む
新しくExcelファイルを開きます。
リボンの データ タブをクリックします。その他のデータソース 三角をクリックして、Microsoft Queryをクリック
データソースの選択画面で先ほど作った接続、 localhost_a_testdummy を選択して OK をクリック
このデータベースにはテーブルが２つあるので参照したい方の nc20m_coworking をクリックしてから ＞ をクリック
次へ をクリック
次へ をクリック
次へ をクリック
この画面で、今の設定を次にも使えるようにします。
クエリの保存 をクリック
とりあえず表示された名前のまま保存しました。
この画面では何もせずに OK をクリック
じゃーん！なんとこんなきれいな形でMySQLのデータが表示されました。
通常のExcelファイルとしても保存できます。
先ほど保存したクエリを使ってみます。新しいExcelファイルを開いて、
リボンのデータタブから、既存の接続をクリックします
既存の接続が表示されるので、先ほど保存したクエリを選択して 開く をクリックします。
OKをクリック。
新しいExcelにも先ほどと同じようにMySQLからデータが貼り付けられました(^^)/
まとめ：インストールと設定は長いけども、それが終われば次回からはExcelから読み込むだけなので簡単
インストールと設定は割と長くてちょっと嫌になるかもしれませんが(^_^;、説明すると長くても実際にやると5分ぐらいで終わると思います。それから先はExcelのデータタブからMySQLに接続してデータを参照できるのでとても簡単で、便利だと思います(^^)/
コメント
コメント一覧 （11件）
逆にエクセルで編集して文字化けさせずにインポートさせる事もできるのでしょうか？
tinさん、おはようございます。インポートをCSVから行ったことがなかったのですがどうなるのかなーと思ってやってみました。
インポートさせるためにはまず、エクセルで編集したファイルを CSV の形式で保存します。
phpmyadminでインポートしたいテーブルを表示させてから インポート タブをクリック
インポートするファイル で保存したCSVを指定
そのすぐ下にある ファイルの文字セット で SHIFT_JIS を選択
一番下の 実行 をクリック
これで文字化けせずにインポートすることができました。ただ、IDが同じものが既にそのテーブルに存在していればエラーになりますので書き換えをしたければ該当するデータを削除しておく必要があります。データを削除してしまってインポートが失敗するとこわいのでバックアップを取って失敗してもいいようにしてから実行するといいかと思います。
なるほど！
詳しくありがとうございます。インポート時に自分はshiftJISなんだよ。と指定してあげれば、自動的に変換してmysqlの文字コード(utf-8等)にしてくれるという事ですね。
でも確かに、インポートだとファイル更新の際に一度削除する必要があるので、他の方法を使ったほうがいいかもです。
僕はmysql使いたての頃はエクセルをcsvインポート(日本語は読み込まれず空欄に)していて、
しばらくしてからは、練習がてら、phpmyadmin上でプログラムを打って更新する画面で上書き保存をするようになりました。できることはその２つだけです。
しかし、ある程度一気に更新したい時とか、新しいのも追加したい時とか、もっと効率よくする方法はないかなぁと模索中です…。
なんて。
ご教授ありがとうございました。
tinさん、こんにちは。私がよくやっているのはExcelの関数かなんかで update文 を作っちゃうことです。文章だけだとうまく説明できませんが、update文の決まり切ったところは１行目にかいちゃって、値が変わるところだけ a3 とか書いておいて式を引っ張ると、a4,a5,a6…と変わっていくようにしてupdateのSQLを作って、phpmyadminで実行させるようなやり方です。
ちょっとイメージしにくいですが、確かにエクセルでプログラム文を作っちゃうってのはいいかも知れませんね。
基本的な部分ができたら、データをダウンロードしてエクセルで更新して、更新した部分と追加した文を判別して自動的にプログラム文を作ってくれるようなエクセルを作れたらいいかも知れません。
まだ思いつきませんけど。
tinさん、おはようございます。プログラム文ではなくってupdate文とかをだーっと作っちゃう関数をよく使います。VBAですらないのでわりとかんたんです。コメントだけだと分からないですよね(^_^;うまくせつめいできなくて。今度blogにでも書いてみようかなと思ってます。
兎本さんおはようございます。
あ、そうか。僕はupdate文もプログラム文だと思って認識してました（笑）
おもしろそうですね。ブログ楽しみにしています！
お付き合いいただきありがとうございました。
はじめまして．
安西といいます．現在学生です．
記事を書いていただきありがとうございます．
この記事を参考にして進めていこうと思っているのですが，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):
と出ます．
これは何がダメなのでしょうか．
返信よろしくお願い致します．