csv のBULK INSERTがうまくいきません。

mizuki
2014-05-26 23:30 11831 1
こんばんは。
こちらの「SQL Tips」で「csvのBULK INSERT」について勉強させて頂き、早速実践していますが、
SQL Serverのテーブルにインポートしようとしているcsvの構造が複雑なせいか、苦戦しています。
長文で申し訳ございませんが、以下に詳細を纏めましたので、アドバイスを頂きたくお願い致します。

◆質問
ダブルクォーテーション("")で囲まれた列(テキスト)とそうでない列(数値、日付)が混在し、かつ、
テキストが未入力の場合はダブルクォーテーションがつかない、カンマ区切りのcsvであっても、
フォーマットファイル(.fmt)を用いたBULK INSERTは可能でしょうか?
「Yes(=可能)」の場合、フォーマットファイルの書き方は、どのようにすれば良いでしょうか?
「No(=不可能)」の場合、csvの元データが格納されているDBで設定変更を行ってもらうしかないでしょうか?
(例:csvを吐き出す際、テキスト型の列は全てダブルクォーテーションが付加されるようにする)

◆現状、困っていること
ダブルクォーテーション("")で囲まれた列とそうでない列が混在している、カンマ区切りのcsvを、
フォーマットファイル(.fmt)を使って「BULK INSERT」でSQL Serverのテーブルにインポートしたいのですが、
csvの構造が特殊で、テキストが未入力の場合はダブルクォーテーションで囲まれず、区切り文字が「,」となるため、
「数値等が入力されている」とSQL Serverが誤認識しているようで、型の不一致エラーが発生して失敗してしまいます。

◆発生するエラーメッセージの内容
メッセージ 4864、レベル 16、状態 1、行 1
行 3、列 3 (優先度) の一括読み込みデータ変換エラー (型の不一致または指定されたコードページでは無効な文字)。
メッセージ 4832、レベル 16、状態 1、行 1
一括読み込み: データ ファイルで予期しないファイルの終了が検出されました。
メッセージ 7399、レベル 16、状態 1、行 1
リンク サーバー "(null)" の OLE DB プロバイダー "BULK" により、エラーがレポートされました。プロバイダーからエラーに関する情報を取得できませんでした。
メッセージ 7330、レベル 16、状態 2、行 1
リンク サーバー "(null)" の OLE DB プロバイダー "BULK" から行をフェッチできません。

◆問題の背景
自社製品DB(SQL ServerやOracleではない)から吐き出されるcsvデータ(約60万件)を、毎日、
SQL Serverのテーブル(列数は200以上)に取り込んでいます。
以前はデータインポート用SSISパッケージを定義し、決まった時間にjobを実行していましたが、
ある時点から「文字列の切り捨て」エラーが多発するようになり、全件取り込めなくなってしまいました(※)。
※70%ぐらい取り込めたところで切り捨てエラーが発生し、インポートが途中で終了
そこで「BULK INSERT」を試してみることにした次第です。

csvの中身を調べたところ、ダブルクォーテーション("")で囲まれた列とそうでない列が混在しており、
テキスト型の列で未入力がある場合、ダブルクォーテーションは付かないことが分かりました。
<例>
・テキスト型(全て文字入力あり)
 "あああ","いいい","ううう", ・・・  ←ダブルクォーテーションで囲まれる
・テキスト型(未入力列がある場合)
 "あああ",,"ううう" ・・・  ←未入力列はダブルクォーテーションがつかない
・数値型や日付型
 1,2004-01-01,150.00  ←ダブルクォーテーションはつかない


◆BULK INSERT文 ※今回の質問にあたり作成したサンプル
-----------------------------------------
BULK INSERT dbo.test
FROM
'D:\test\testdata.csv'
WITH 
(
FORMATFILE = 'D:\test\test.fmt'
)
-----------------------------------------

◆csv(testdata.csv)の中身 ※今回の質問にあたり作成したサンプル
------------------------------------------------------------
"123",1,"阿部",250.00000,2004-01-01,"完了",2004-01-01 12:00:00
"789",2,"鈴木",200.15000,2012-05-29,"却下",2012-05-29 15:00:00
"012",3,,310.20000,2006-06-06,"完了",2006-06-06 09:00:00
"345",4,"藤田",1234.50000,2008-02-28,"完了",2008-02-28 07:00:00
"135",5,"真弓",123.50000,2018-12-28,,2018-12-28 07:00:00
------------------------------------------------------------

◆インポート先テーブル(dbo.test)のデザイン ※今回の質問にあたり作成したサンプル
--------------------------------
列名   データ型
事業所  varchar(50)
優先度  int
担当者  varchar(50)
金額    decimal(18, 5)
更新日  date
ステータス  char(10)
更新時刻  datetime2(0)
※全てNULLを許容
--------------------------------

◆フォーマットファイル(test.fmt)の内容 
※今回の質問にあたり作成したサンプル。
自宅のSQL Serverは2012なので「11.0」としていますが、会社ではSQL Server 2008を使っています。
--------------------------------
11.0 
8
1 SQLCHAR 0 10 "\"" 0 dummy ""
2 SQLCHAR 0 50 "\"," 1 col1 ""
3 SQLCHAR 0 50 ",\"" 2 col2 ""
4 SQLCHAR 0 50 "\"," 3 col3 ""
5 SQLCHAR 0 0 "," 4 col4 ""
6 SQLCHAR 0 0 ",\"" 5 col5 ""
7 SQLCHAR 0 0 "\"," 6 col6 ""
8 SQLCHAR 0 0 "\r\n" 7 col7 ""
--------------------------------


コメント

2014年 05月 27日 (12:03)
こんにちは。
イノ(inno)です。

分かりやすく細かく質問して頂きありがとうございます。
確かに質問に記述して頂いたfmtファイルの内容やテーブル構成には問題なく
csvの2行目までは正常に登録されることを確認しました。
csvの3行目にテキストが未入力の場合はダブルクォーテーションで囲まれないのが問題になりますね。
今回はfmtを使ってもできなさそうです。
データに登録されているテキスト自体にダブルクォーテーション("")が入っている場合はないでしょうか。
もし、ないようであればcsvを作成する時にテキストの場合、ダブルクォーテーション("")で囲まれないように設定を変更することは可能でしょうか。
つまりcsvの内容はダブルクォーテーション("")で囲まれなず下記のように保存します。

123,1,阿部,250.00000,2004-01-01,完了,2004-01-01 12:00:00
789,2,鈴木,200.15000,2012-05-29,却下,2012-05-29 15:00:00
012,3,,310.20000,2006-06-06,完了,2006-06-06 09:00:00
345,4,藤田,1234.50000,2008-02-28,完了,2008-02-28 07:00:00
135,5,真弓,123.50000,2018-12-28,,2018-12-28 07:00:00

そのあと下記のように作成してbulk insertをすると問題なくできます。

BULK INSERT dbo.test
FROM 'D:\test\testdata.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

ですが、SSISパッケージの「文字列の切り捨て」のエラーが発生するからBulk insertに変更すると言うなら
SSISパッケージを修正して対応するのが一番いいかと思います。
私もSSISパッケージで「文字列の切り捨て」のエラーが発生したりするのですが、
プロパティから列のサイズを調整したり、「データ変換」コントロールを追加し解決して使っています。

十分な回答ができたか分かりませんが、もっと説明が必要な部分がありましたらコメントに書いてください。
私が分かる部分なら説明します。