このトピックでは。文字列中に改行を含むCSVデータを Oracle Database へ正しくロードする方法について説明します。１億件の大量CSVデータをわずか95秒で処理できる実用的なデータ変換プログラムのソースコードもダウンロードすることが出来ます。
Oracle Database へCSVデータをロードするにはSQL*Loaderというツールがよく使われます。
SQL*Loader は大量のテキストデータを素早くOracle Databaseへ取り込むのに大変都合良いのですが、意外なことに文字列型フィールド中に改行を含むCSVを正しく取り込むことが出来ません。例えダブル・クォーテーション（二重引用符）で囲っても不可です。
そうとは知らず、データをロードする段になってBADファイルやエラー・メッセージの山に遭遇し、困り果てた末にネット検索でこの記事に辿り着いた読者もいらっしゃる事でしょう。
このトピックは、データ変換プログラムを通すことで、アンロードを再び行うこと無しにCSVファイルを救済し、読者が直面している課題の解決に役立てることを目標としています。
どんな状況で障害が発生するのかを検証してみましょう。
たとえば次のようなCSVデータがあったとします。名前をLOAD_SAMPLE.csvとします。
"2012/10/21","改行無し",85 "2012/11/21","２文
字目で改行",86 "2012/12/21","４文字目
で改行",87
これには日付型、文字列型、数値型それぞれ１つずつのフィールドが含まれる、３つのレコードが存在したとします。
２行目と３行目のそれぞれ２番目の文字列型フィールドに改行（正確にはCRとLF）が含まれています。
このCSVデータを格納するためのテーブルをSCOTTスキーマへ用意することにしました。次のSQL文を使います：
CREATE TABLE load_sample ( c1 DATE, c2 VARCHAR2(30), c3 NUMBER(5));
次の挿入文を実行するとLOAD_SAMPLE.csvと同じ内容を load_sample 表へ導入することも出来ます。
insert into load_sample values ('12-10-21','改行無し',85);
insert into load_sample values ('12-11-21','２文'||chr(13)||chr(10)||'字目で改行',86);
insert into load_sample values ('12-11-21','４文字目'||chr(13)||chr(10)||'で改行',87);
commit;
CSV データを格納するための制御ファイルを次のように作成します。名前はLOAD_SAMPLE.ctlとします。
LOAD INFILE "LOAD_SAMPLE" INTO TABLE "LOAD_SAMPLE" TRUNCATE FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( "C1" DATE "YYYY/MM/DD" ENCLOSED BY '"' , "C2" CHAR ENCLOSED BY '"' , "C3" DECIMAL EXTERNAL )
SQL*Loader を次のように起動してCSVデータをLOAD_SAMPLE表へ取り込んでみます。
C:\output>sqlldr SCOTT/TIGER control=LOAD_SAMPLE SQL*Loader: Release 11.2.0.1.0 - Production on 木 10月 25 14:28:32 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. コミット・ポイントに達しました。 - 論理レコード件数5
次のようなメッセージが戻され、一見うまくいっているように見えます。でも何かがおかしい事に気づきませんか？
そうですね。CSV は３件しかなかったのに、画面では「件数5」となっているのが変ですね。では、ログ・ファイルがどうなっているか見てみましょう。
C:\output>type LOAD_SAMPLE.log SQL*Loader: Release 11.2.0.1.0 - Production on 木 10月 25 14:33:47 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. #### 途中略 #### Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- "C1" FIRST * , " DATE YYYY/MM/DD "C2" NEXT * , " CHARACTER "C3" NEXT * , CHARACTER レコード2: 拒否されました。- 表"LOAD_SAMPLE",列"C2"でエラーが発生しました。 2番目の囲み文字列がありません レコード3: 拒否されました。- 表"LOAD_SAMPLE",列"C1"でエラーが発生しました。 先頭の囲み文字が見つかりません レコード4: 拒否されました。- 表"LOAD_SAMPLE",列"C2"でエラーが発生しました。 2番目の囲み文字列がありません レコード5: 拒否されました。- 表"LOAD_SAMPLE",列"C1"でエラーが発生しました。 先頭の囲み文字が見つかりません 表"LOAD_SAMPLE": 1行のロードに成功しました。 4行はデータ・エラーのためロードされませんでした。 0行はWHEN句のエラーのためロードされませんでした。 0行はすべてのフィールドがNULLのためロードされませんでした。 #### 途中略 #### 実行時間: 00: 00: 00.16 CPU時間 : 00: 00: 00.05
やはり、問題が発生していたようです。
レコードxx: 拒否されました。- 表"LOAD_SAMPLE",列"C2"でエラーが発生しました。
というメッセージが４回表示され、ロードが正しく行われなかったことが分かりました。
最初のエラー・メッセージでは
2番目の囲み文字列がありません
となっていますから、２番目のレコードの文字列フィールド読み取りが開始されその後、フィールドの終わりを示す囲み文字列（二重引用符）が見つからなかったようです。
つまり、この文字列フィールド中の改行はデータの一部ではなく、レコードを区切るデリミタとして解釈されてしまったのです。
では、この CSV をSQL*Loader を使ってロードすることは無理なのでしょうか。
いいえ、そのようなことはありません。次のセクションにあるプログラムを作って、CSV の書式を変換し、制御ファイルへ若干の手直しを加えることにより、ロードすることが可能になります。
実際にプログラムを作る前に、解決策のコンセプトを確認します。
オラクル社の製品マニュアル「Oracle Databaseユーティリティ」にはSQL*Loaderの使用方法に関する説明があります。
SQL*Loaderの概念 という章でSQL*Loaderが認識するレコードの形式について説明があります。扱えるレコード形式には次の３つがあります。
| レコード形式名 | 制御ファイル上の指定 |
|---|---|
| 固定レコード形式 | INFILE datafile_name “fix n” |
| 可変レコード形式 | INFILE “datafile_name” “var n” |
| ストリーム・レコード形式 | INFILE datafile_name [“str terminator_string”] |
datafile_name の次のシンボルが省略された場合、つまり今回のエラーが発生したLOAD_SAMPLE.ctlという制御ファイルのようなケースではストリーム・レコード形式が選択され、Windows プラットフォームでは terminator_string に”\r\n” または “\r” が選択されます。
従って文字列に改行を含むCSVをロードする場合、デフォルトのストリーム形式では無理だということが分かります。
CSVを出力しなおすことなく変換のみでロードするには、可変レコード形式を使うことになります。この形式はレコードの終わりをCR+LFではなく、レコード自身に含まれるレコードの長さに基づいて識別するので、途中にCR+LFが存在していてもそれをレコードの区切りとして扱うことはありません。
下図に可変レコード形式を扱う為の制御ファイルと、それとマッチするレコードの見本を示します。
可変レコード形式を使用するための制御ファイルには VAR というキーワードに続けて 10 進数で表現した桁数を置きます。最大のレコード長を考えて桁数を定めます。図のように 10 を選ぶと 10 の 10 乗バイトまでのレコード長に対応できます。
可変レコード形式で実際のレコードを表現するには、レコード本体の前に VAR キーワードの引数で指定した桁数のスペースを設けて、10 進数でレコード長をゼロ詰めで置きます。この時注意して欲しいのは、レコード本体には行末のCR+LFのバイト数も含めるということです。
更にCR+LFの前にカンマが無い場合はカンマを１文字分追加します。
ここまで説明した変更を制御ファイルとCSVファイルへ加えるとデータを正しくロードすることが出来るようになります。制御ファイルは簡単に手作業で修正できますが、CSVファイルはレコード長を数えて10桁の数値文字列データを先頭へ追加するという作業を繰り返さなければなりません。そこで変換プログラムの出番となります。