MySQLでcsvファイルをエクスポート・インポートする方法まとめ

MySQLを利用して開発を行っていると、csvからサンプルデータをimportしたり、DBの中身をエクスポートする場合があります。

Pentaho(Spoon)等のETLツールを使うと、スクリプト化したり、項目の加工を行ったり、エクセルからimportしたり、エクスポートしたりすることもできますので、ぜひその記事も読んで活かしてほしいですが、今回は、シンプルにMySQLの機能を使って実現することにしましょう。

エクスポートする方法

以下のmysqlコマンド構文でcsvファイルをエクスポートできます。

気をつけたいのは、「escaped by」で指定するエスケープ文字ですね。

デフォルトは「」になっていますので、カラムの囲み文字と同じ文字がレコードに含まれると、「」でエスケープされ、「”AD”,”アン”ドラ”」のように出力されます。

通常、csvでは「”」がエスケープに使われますので、それと同じにするには「escaped by ‘”‘」を指定した方が良いと思います。

mysql>
select * from table_name into outfile "D:\export_file_name.csv"
fields 
terminated by ','             /* カラムの区切り文字 */
enclosed by '"'               /* カラムの囲み文字 */
escaped by '"'                /* エスケープ文字(デフォルトは) */
lines terminated by 'rn';   /* 1レコードの改行コード */

出力カラムに加工を加えてエクスポートするには

上記のコマンドで「select * from table_name」の部分が、出力カラムを指定しているところで、基本的にSQLのSelectの結果をファイル出力しています。

そのため、この部分に「select concat(CODE, ‘-‘, COUNTRY), CODE, COUNTRY from table_name」のようなSQLの関数等を使用してカラムに対して加工を行うことも可能です。

ヘッダをつけてエクスポートするには

上記を応用すると、ヘッダをつけることもできます。

一行目に文字列を出力し、selectの結果とUNIONします。

mysql>
(select 'CODE', 'COUNTRY')
UNION
(select CODE, COUNTRY  from country into outfile "D:\export_file_name.csv"
fields 
terminated by ','             /* カラムの区切り文字 */
enclosed by '"'               /* カラムの囲み文字 */
escaped by '"'                /* エスケープ文字(デフォルトは) */
lines terminated by 'rn'   /* 1レコードの改行コード */
);

Nullを含むデータを出力するとおかしくなる問題に対処

上記のように出力すると、Nullを含むレコードは「”N」のように出力されておかしくなってしまいます。

そこで、IFNULL()関数を使用して、あらかじめNullだったらどのように置換するかを指定しておくと良いです。

mysql>
select CODE, IFNULL(COUNTRY, '') from country into outfile "D:\export_file_name.csv"
fields 
terminated by ','             /* カラムの区切り文字 */
enclosed by '"'               /* カラムの囲み文字 */
escaped by '"'                /* エスケープ文字(デフォルトは) */
lines terminated by 'rn'; 

タブ区切りでエクスポートするには

よく使われる形式として、タブ区切りの形式(tsv)もあります。

上記の原理が分かれば簡単ですね。以下のように指定します。

mysql>
select * from table_name into outfile "D:\export_file_name.csv"
fields 
terminated by 't'           /* カラムの区切り文字 */
lines terminated by 'rn'   /* 1レコードの改行コード */

インポートする方法

以下のmysqlコマンド構文でcsvファイルをインポートできます。

mysql>
load data local infile "D:\import_file_name.csv" into table table_name 
fields terminated BY ','          /* カラムの区切り文字 */
enclosed by '"'                   /* カラムの囲み文字 */
lines terminated by 'rn'        /* 1レコードの改行コード */
IGNORE 1 LINES;                   /* 1行を無視する */

一行目にヘッダのあるcsvファイルの場合には、上記のように「GNORE 1 LINES」を指定すると1行目を読み飛ばしてくれます。

インポートするときに、項目の値に加工を加える

通常はそのままcsvをインポートしますが、その際に、なんらかの加工を行いたい場合があります。

たとえば日付型のフォーマット加工や文字列置換などです。

その場合には、「ユーザ変数」を利用すると便利です。

上記では、例として、CODEとCOUNTRYというカラムを持つテーブルのCOUNTRYの項目に対して、インポート時に文字列置換(REPLACE)処理を行う例です。

mysql>
load data local infile "D:\export_file_name.csv" into table country
fields terminated BY ','      /* カラムの区切り文字 */
enclosed by '"'               /* カラムの囲み文字 */
lines terminated by 'rn'    /* 1レコードの改行コード */
(CODE, @var_country)
SET COUNTRY = REPLACE(@var_country, 'E', 'rr');

置換だけでなく、

STR_TO_DATE(@date_column, '%m/%d/%Y');

のように文字列を日付型に加工したりすることもできます。