LOAD DATA INFILE構文を使った、csvをMySQLのテーブルにインポートする方法を解説します。
ここではセールスフォースのレポート機能で出力するcsvを例に進めます。
セールスフォースでデータをエクスポートする際の形式
文字コードはutf8を選択して、csv形式での出力を設定します。
MySQLにcsvをLOAD DATA INFILE構文でインポートする
MySqlWorkbenchや、Sequel ProなどのGUIツールを使って、インポートしても良いですが、
LOAD DATA INFILE構文での実行が高速で、持ち回りもできてオススメです。
以下コードになります。
LOAD DATA
LOCAL INFILE '{your_local_file_path}/your_file_name.csv'
INTO TABLE salesforce_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
SET
int_field = nullif(state_code, '')
;
コードの解説
区切り文字
カンマ区切りは、
FIELDS TERMINATED BY ','
とします。
フィールドの括り
""で区切られていますので、
ENCLOSED BY '"'
を記述します。
改行コード
セールスフォースでの出力では改行コードはLFがセットされるようですので、
LINES TERMINATED BY '\n'
としています。
出力後に加工などして、改行コードが変わってしまった場合は以下のように変更してみてください。
CRでは、
LINES TERMINATED BY '\r'
CRLFでは、
LINES TERMINATED BY '\r\n'
となります。
無視する行を設定する
フィールド名がcsvの１行目に含まれている場合、
IGNORE 1 LINES
を記述しましょう。
int型などで空欄をnullにしたい時
int型で、nullを許容しているような時は、以下のようにします。
SET int_field = nullif(state_code, '')
nullを許容していない場合は、0をセットするようにすれば良いかと思います。
LOAD DATA LOCAL INFILEが実行できない人へ
こちらの記事が参考になるかと思います。
MySQL8系を例にしているようですが、おそらくMySQL5系でも同様かと思います。
--local-infileは、デフォルトで1がセットされていることも多いようですので、
私の場合は特に問題なく実行することができました。
ファイルパスや改行コードについてはこちらの記事も参考にさせていただきました。
まとめ
MySQLのテーブルへcsvをインポートするときは、GUIツールのimport機能に頼るより、
自身でLOAD DATA INFILE構文を使って投入すると効率的かつ確実かと思います。
（GUIツールの謎のエラーに悩まされたりしない。自分の場合はSequel Proでインポート実行中に決まった箇所で落ちましたが、データは問題なさそうなので原因わからず、、）
Herokuなどの本番環境でも同様に使いやすい方法かと思いますので、MySqlでcsv形式の大量データを投入する際の参考になれば幸いです。