CSVを加工する機会は多いと思います。データのやり取りはなんだかんだでCSVが主流で、Salesforceのデータローダでもインポート、エクスポートはCSVを使います。
自分がよく使うCSVの加工するやり方について書きます。
ちなみに、一番よく使うのは6の「H2 Database」です。
- テキストエディタで加工する
- Excelで加工する
- MS-Accessに一度取り込んだ後加工する
- ODBCの「Microsoft Text Driver (*.txt; *.csv)」で加工する
- Javaライブラリ「HSQLDB」のTEXT Tableにセットした後加工する
- Java ライブラリ「H2 Database」で加工する
- MySQL、SQL Serverへ取り込んだ後加工する
1.テキストエディタで加工する
原始的にテキストエディタでテキストを開き、値の確認や修正を行います。値の中で改行が多い場合、どの行を編集しているかがわからなくなります。あと、CSVが大きくなると開けない時があります。もちろん他のデータと結合や集計などはできません。
2.Excelで加工する
ExcelでCSVを開き、値の確認や修正を行います。単純に開くと"000010"のような値はゼロサプレスの対象となり、保存すると日付のフォーマットが勝手に変わってしまうリスクがあります。Excelのデータタブからテキストファイルを取り込むことで、ファイルの細かな指定をして取り込むことができますが、項目が多いと非常に大変です。
他のデータと結合や集計などは、vlookup関数やピボットテーブルを使うことで近いことはできますが限界があります。そして、CSVが大きくなるとExcelで扱うのは現実的ではありません。
3.MS-Accessに一度取り込んだ後加工する
MS-Accessでテキストファイルをインポートして、テーブルとして扱います。テーブルの列数は255までです。CSVもある程度の件数まで扱えます。（サイズが大きいとMS-Accessのファイルが壊れるので注意が必要です）
データベースなので結合や集計は可能です。(SQLエンジンがJetなので多少癖は強いです。) 一度取り込んでしまえば、クエリを駆使したり、別のテーブルを作成
(select field1, ・・・・ into newtable from orgtable の書き方が便利です)
したりした後、エクスポートすることで加工後のCSVを作成します。インポートとエクスポートはコマンドではなくGUIになる以外はあまり違和感がないやり方ですが、インポートするファイルの数が多かったり、エクスポートするファイルの数が多いとGUIがネックになります。
4.ODBCの「Microsoft Text Driver (*.txt; *.csv)」で加工する
Windows限定のやり方ですが、MS-Accessにデータをいれることなく、CSV自体をテーブルとして扱い、selectや、新しいCSVを作成します。これはMS-Accessと同じSQLエンジンなので、MS-Accessと同様に項目は255までとなります。これを使う際には、ODBCを扱えるSQLクライアント(cseなど、もしくはJava用のSQLクライアントでodbc driverを使うかです)か、OleDBを扱えるSQLクライアントが必要です。また、ODBCを設定する際には、64bitのPCでは、「SysWOW64」の中のODBC定義(odbcad32.exe)から設定が必要です。
ドライバで定義したフォルダに「schema.ini」を定義することで、ファイルの文字コードや項目名、型を指定できますが、CSVファイルをShiftJISで、全項目をダブルクオートの囲み文字を指定することで、全て文字列で扱うのが簡単です。
単純なselect
select * from sample1.csv
先頭N件のselect (件数が多い場合にデータを読む場合便利)
select top 10000 * from sample1.csv
csvから新しいcsvを作成
select * into sample2.csv from sample1.csv
5.Javaライブラリ「HSQLDB」のTEXT Tableにセットした後加工する
JDBCの「HSQLDB」を使います。そして、JDBC用のSQLクライアントが必要です。(SQuirreL SQL ClientかExecute Queryが便利です)
各SQLクライアントの設定で、「HSQLDB」のJDBCドライバを指定し、スタンドアロンでHSQLDBを起動します。メモリモードか、ファイルモードかなどいくつか選べますが、ここではファイルモードにします。
JDBCの接続文字列 (Dドライブ直下に作成する)
"jdbc:hsqldb:d:\hsqldb"
TEXT Tableの作成
create text table tablex (f1 varchar(100), f2 varchar(100) );
TEXT TableへCSVをセット
set table tablex source "xxx.txt";
これでCSVをテーブルとして扱えます。 v2.4で試したところ、set table のCSVのファイルパスがフルパスが使えませんでした。ファイルパスは接続文字列で指定したドライブからの相対パスになるようです。もちろん、set tableでオプション指定することが区切り文字やファイルのエンコーディングの指定ができます。
事前にテーブルの作成が必要になるのが手間です。
6.Java ライブラリ「H2 Database」で加工する
JDBCの「H2 Database」を使います。5のhsqldbと同様に、JDBC用のSQLクライアントが必要です。そして、csvreadという関数を使うのですが、これが一番使いやすいと感じています。csvread関数は、全件メモリに展開するようで、ファイルサイズが大きいとエラーになります。(ヒープサイズを増やせばそれなりの件数は扱えますが)
JDBCの接続文字列 (Dドライブ直下に作成する)
jdbc:h2:mem:.
CSVにselectする
select * from csvread('D:\sample.csv')
CSVからテーブルデータを作成する
create table sampletable as select * from csvread('D:\sample.csv')
テーブルデータをCSVに出力する
call csvwrite('D:\sampleexport.csv','select * from sampletable')
csvread関数で引数を渡すことで、ファイルの区切り文字やエンコーディングを指定できます。
7.MySQL、SQL Serverへ取り込んだ後加工する
データ件数が多い場合は通常のデータベースを使う事になります。
MySQLの場合
Load data Local INFILE 'D:\\sampledata.csv' into table sampletable
character set cp932
fields terminated by ','
enclosed by '"'
lines
terminated by '\r\n'
ignore
1 lines
;
SQL Serverの場合
BULK INSERT sampletable
FROM 'D:\sample.csv'
WITH
(
FIRSTROW = 1,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
となり、ここまでくるとちょっとした開発みたくなってしまいます。（厳密にファイルの仕様を定義しないとデータもおかしくなります）
最後に
6のJava ライブラリ「H2 Database」で扱う の使い勝手がとても良いです。64bitマシンでメモリに余裕があれば、-Xmx10G などとすることでたいていの加工処理が済んでしまいます。是非お試しください。
