周辺知識

MySQLコマンドについての備忘録(CSVファイルのインポート)

MySQL コマンドについての備忘録その2です。

その1はこちら。

Laravel ではCSVファイルを読み込むライブラリがあるみたいですね。

しかし、今回は直接SQLコマンドを使ってCSVファイルをインポートするやり方をエントリーします。

 

ユースケース

CSVファイルを作成してDBにインポートしたい。

インポートするデータはデモで「FAIFAランキング」(※2018年3月)

 

手順

1)テーブルの作成
2)CSVファイルの作成
3)設置
4)MySQLにログイン
5)CSVインポート作業
6)動作確認

 

1)テーブルの作成

MySQLにログインしてデータベース・テーブルを作成します。

テーブルの構成は以下のようにします。

table name : fifaranking
column : ranking(int11) country(varchar255) point(int11)
CREATE TABLE fifaranking(
ranking int,
country varchar(255),
point int
);

こんな感じです。

 

2)CSVファイル作成

Excel を開いてデータを入力します。

1.「名前を付けて保存」
2.ファイルの種類に「CSV(カンマ区切り)(*.csv)」を選択

Excel(2010、2013) では文字コードを 「UTF-8」 にして CSV 形式で保存することができません。

テキストエディタでもう一度開いて文字コードを「UTF8」にして保存します。

 

3)設置

作成したCSVファイルをCドライブ直下に置きます。

【 解説 】
サーバー(Linux)環境であれば /tmp/ に入れて、パーミッション設定します。

chmod 777 /tmp

 

4)MySQLにログイン

【 構文 】
mysql -u { ユーザー名 } -p { データベース名 }

 

5)CSVインポート作業

【 構文 】
LOAD DATA INFILE 'CSVファイルへのフルパス' INTO TABLE `テーブル名` FIELDS TERMINATED BY ',' LINES TERMINATED BY "\r\n";
  • LOAD DATA LOCAL INFILE → LOAD DATA INFILE
  • TERMINATED BY ' 区切り文字 '
  • LINES TERMINATED ' 改行コード '
  • LOAD DATA INFILEはデフォルトでは改行コードを LF として扱う
  • Excelで作成すると改行コードは CR+LF(\r\n)
  • パスに日本語が入っているとNG
  • インポートするcsvファイルについても、データの途中に"・"があったり全角スペースが入っているとエラーを返す
【 CSVフルパスがおかしい? 】
NG LOAD DATA INFILE 'C:\test.csv' …
OK LOAD DATA INFILE 'C:\\test.csv' …
※これはなぜか僕の環境だけみたいです。。なんで?

 

6)動作確認

とりあえず上位5位まで表示してみます。

select * from fifaranking limit 5;

以上です。

本庄マサノリ

仕事でLaravelを使っています。気づいたことや新しい発見など情報を発信していきます。メールはこちら

 

-周辺知識