こんばんは。
こちらの「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 ""
--------------------------------