MySQLでCSVファイルをインポートする
はじめに
データベースへのデータ入力は、大変骨の折れる作業だ。少しでも手抜きをするために、ここではcsvファイルに書かれているデータをMySQLへインポートする方法を説明する。基本的にMS-DOSでの操作を示しているが、所々でLinux向けの説明もしている。ファイルの準備
まずは適当なデータをcsvで用意する。そしてWinodowsであれば以下のフォルダに、そのCSVファイルを置く。
'C:\ProgramData\MySQL\MySQL Server 5.5\Data\データベース名'ただしこれは、環境に依存するようなので、それぞれ確認して欲しい。
Linuxであれば/tmp/いかのディレクトリに入れておくのが適切だろう。
ただしここで気をつけなければいけないのは、ファイルとディレクトリのパーミッションだ。Linuxは、この辺りが厳密なので注意が必要だ。フルパスで指定するファイルの上位ディレクトリ全てのパーミッション設定を変更しておく必要がある。
例えば、パーミッション設定が適切でない場合、以下のようにファイルにアクセスできないと泣きついてくる。
mysql> LOAD DATA INFILE "/tmp/Book2.csv" INTO TABLE testdb.book1; ERROR 13 (HY000): Can't get stat of '/tmp/Book2.csv' (Errcode: 13)
問題が無ければ、全ユーザに対してアクセス権を開放してしまう。これで問題なくインポートができるようになる。
# chmod 777 /tmp
MySQLへインポート
まずは、インポートするデータにあわせてテーブルを作っていく。CREATE TABLE 2011MonacoGP( Result int, CarNumber int, Driver text, Team text );
テーブルの作成完了したら、DESCRIBEコマンドで確認する。
mysql> DESCRIBE 2011MonacoGP; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | Result | int(11) | YES | | NULL | | | CarNumber | int(11) | YES | | NULL | | | Driver | text | YES | | NULL | | | Team | text | YES | | NULL | | +-----------+---------+------+-----+---------+-------+ 4 rows in set (0.16 sec)
問題なく作成できているようだ。
では、肝心のcsvファイルのインポートをLOAD DATAを使って行う。ファイル名を指定している""の中は、フルパスを使ってファイルを指定してもいい。
例えばLinuxでtmpディレクトリの中にファイルを置いてあるのであれば、/tmp/2011Monaco.csvとなる。
mysql> LOAD DATA INFILE "2011MonacoGP.csv" -> INTO TABLE 2011MonacoGP FIELDS TERMINATED BY "," -> LINES TERMINATED BY "\r\n"; Query OK, 23 rows affected (0.01 sec) Records: 23 Deleted: 0 Skipped: 0 Warnings: 0
インポートの確認
では、ちゃんとインポートされたか確認してみる。mysql> select * from 2011MonacoGP; +--------+-----------+---------------------------------+-----------------------+ | Result | CarNumber | Driver | Team | +--------+-----------+---------------------------------+-----------------------+ | 1 | 1 | セバスチャン ベッテル | レッドブル | | 2 | 5 | フェルナンド アロン | フェラーリ | | 3 | 4 | ジェンソン バトン | マクラーレン | | 4 | 2 | マーク ウェバー | レッドブル | | 5 | 16 | 小林可夢偉 | ザウバー | | 6 | 3 | ルイス ハミルトン | マクラーレン | | 7 | 14 | エイドリアン スーティル | フォース インディア | | 8 | 9 | ニック ハイドフェルド | ロータス ルノーGP | | 9 | 24 | ルーベンス バリチェロ | ウィリアムズ | | 10 | 18 | セバスチャン ブエミ | トロ ロッ・ | | 11 | 8 | ニコ ロズベルグ | メルセデスGP | | 12 | 15 | ポール ディ レスタ | フォース インディア | | 13 | 21 | ヤルノ トゥルーリ | チーム ロータス | | 14 | 20 | ヘイキ コバライネン | チーム ロータス | | 15 | 25 | ジェローム ダンブロシオ | ヴァージン レーシング | | 16 | 23 | ヴィタントニオ リウッツィ | ヒスパニア レーシング | | 17 | 22 | ナレイン カーティケヤン | ヒスパニア レーシング | | 0 | 12 | パストール マルドナド | ウィリアムズ | | 0 | 10 | ヴィタリー ペトロフ | ロータス ルノーGP | | 0 | 19 | ハイメ アルグエルスアリ | トロ ロッソ | | 0 | 6 | フェリペ マッサ | フェラーリ | | 0 | 7 | ミハエル シューマッハ | メルセデスGP | | 0 | 11 | ティモ グロック | ヴァージン レーシング | +--------+-----------+---------------------------------+-----------------------+ 23 rows in set (0.00 sec) mysql>
問題なく出来ているようだ。
ここで、文字化けが起きているのならば、ファイルの文字コードとMySQLの文字コードを確認して同じものを指定する。csvファイルの文字コード変換は、テキストエディターのnotepad++を使用した。他のエディターでも問題なく変換できると思う。
ちなみにMySQLの文字コードの確認は以下のコマンドで確認出来る。
mysql> show variables like 'character_set%';</pre>
せっかくなのでSQLをたたいてみる。
mysql> SELECT * FROM 2011MonacoGP WHERE Team='レッドブル'; +--------+-----------+-----------------------+------------+ | Result | CarNumber | Driver | Team | +--------+-----------+-----------------------+------------+ | 1 | 1 | セバスチャン ベッテル | レッドブル | | 4 | 2 | マーク ウェバー | レッドブル | +--------+-----------+-----------------------+------------+ 2 rows in set (0.07 sec)
と言うことで、レットブルは1位と4位だったようだ。
まとめ
以上のように、MySQLにcsvを使ってデータをインポートする方法を説明した。最初は簡単と思っていたが、実際に作業してみると意外と時間がかかった。またインポートするcsvファイルについても、データの途中に"・"があったり全角スペースが入っていて、エラーを返された。ただ、何度か作業してだいぶMySQLの扱いに慣れてきた感覚がある。何事もへこたれずに取り組むことが大切だと感じた。
参考にしたWebページ
MySQL初心者入門講座 http://mysqlweb.net/category/4005373-1.html
