SQL Server 2017 BULK INSERTでエラーが発生する

こんにちは。
もしご存知の方がいらっしゃいましたらアドバイスをいただけたら幸いです。

対象データベースは、SQLServer 2017です。PCのOSはWindows10です。
"|"区切りのテキストデータを、BULK INSERTでテーブルに取り込みたいのですが、
BULK INSERT実行時に以下のエラーが発生します。

■エラーの内容：

メッセージ 4832、レベル 16、状態 1、行 1
一括読み込み: データファイルで予期しないファイルの終了が検出されました。
メッセージ 7399、レベル 16、状態 1、行 1
リンクサーバー "(null)" の OLE DB プロバイダー "BULK" により、エラーがレポートされました。プロバイダーからエラーに関する情報を取得できませんでした。
メッセージ 7330、レベル 16、状態 2、行 1
リンクサーバー "(null)" の OLE DB プロバイダー "BULK" から行をフェッチできません。

■実行したSQL：

BULK INSERT BulkInsertTest FROM 'C:\temp\bi\STORE_20190323.txt' WITH (DATAFILETYPE='widechar', FORMATFILE='C:\temp\bi\BulkInsertTest.fmt')

■取り込もうとしたデータ(STORE_20190323.txt UTF-8(BOM無) 改行LF)：

00001|あ|1|N|T
00002|a|1|N|This store is already closed.

■フォーマットファイル(BulkInsertTest.fmt)：

14.0
5
1 SQLCHAR 0 5 "|\0" 1 StoreCd Japanese_CI_AS
2 SQLNCHAR 0 200 "|\0" 2 StoreName Japanese_CI_AS
3 SQLINT 0 4 "|\0" 3 SeqNo ""
4 SQLCHAR 0 1 "|\0" 4 DlvStatCd Japanese_CI_AS
5 SQLNCHAR 0 400 "\n\0" 5 DlvNGRsn Japanese_CI_AS

■対象のテーブル(BulkInsertTest)：

些細な点でも構いませんので、ご意見等いただけたら幸いです。
よろしくお願いいたします。

■テキストの中身

行動規範の内容に同意します

回答1件

ベストアンサー

フォーマットファイルの指定の内、区切り文字の指定が合っていないようです。
|区切りということと、普通、行の区切りはcrlfですので、以下のような指定だと思います。
※\0ってlfを表現しようとした？←NULL ターミネータ (表示されないターミネータ)ですね。

項目順	データ型	prefix長	データ長	区切文字	格納先項目順	格納先項目名	項目文字コード
14.0
5
1	SQLCHAR	0	5	"｜"	1	StoreCd	Japanese_CI_AS
2	SQLNCHAR	0	200	"｜"	2	StoreName	Japanese_CI_AS
3	SQLINT	0	4	"｜"	3	SeqNo	""
4	SQLCHAR	0	1	"｜"	4	DlvStatCd	Japanese_CI_AS
5	SQLNCHAR	0	400	"\n"	5	DlvNGRsn	Japanese_CI_AS

追記

CSVがutf-8でしたね。
utf-8の場合、コードページに関しては、フォーマットファイルは指定できないようです。
※指定する場所が無いので。それ以外は問題なさそうです。
UTF-8 形式のテキストファイルを BULK INSERT で一括取り込みする( SQL Server )
【SQL】BULK INSERTのデータインポート（UTF-8）
上記を参考に以下で取り込めました。

SQL
1BULK INSERT BulkInsertTest FROM 'C:\temp\STORE_20190323.txt'
2WITH (
3  DATAFILETYPE='char'
4, CODEPAGE = '65001'
5, FIELDTERMINATOR = '|'
6, ROWTERMINATOR='0x0A'	-- 行ターミネータ LF (0x0A)
7)

投稿2019/03/24 02:19

編集2019/03/24 09:04

sazi

総合スコア25300

gysnet

2019/03/24 03:23

sazi様、ご回答ありがとうございます。設定を修正して確認してみます。 \0は、Webサイトを検索して見よう見真似でやっていたことで、意図はありません。今回取り込むテキストファイルは、とある外部システムが作成するのですが、サンプルのテキストを見たときに改行コードがLFでしたので、行ターミネータもLFでないといけないかなと思っていました。

gysnet

2019/03/24 03:27

修正して実施してみたところ、以下のエラーに変わりました。引き続き調べてみます。メッセージ 4863、レベル 16、状態 1、行 1 行 1、列 1 (StoreCd) の一括読み込みデータ変換エラー (切り捨て)。

gysnet

2019/03/24 03:30

先程は、ファイル形式がUTF-8(BOM付)だったのですが、 UTF-8(BOM無)にして行うと、さらに以下のエラーに変わりました。メッセージ 4863、レベル 16、状態 1、行 1 行 1、列 2 (StoreName) の一括読み込みデータ変換エラー (切り捨て)。

sazi

2019/03/24 03:44

質問にあるデータをコピーして確認しましたがエラーにはなりません。 Japanese_CI_ASの指定を、NCHARの属性である「StoreName」と「DlvNGRsn」のみにしてみてはどうでしょう。

sazi

2019/03/24 03:47

LFのみなら、\nで指定してみて下さい

gysnet

2019/03/24 04:31

Japanese_CI_ASの指定、\nの指定を修正して行ってみましたが、依然としてエラーとなります。 sazi様の環境では発生していないのでしたら、私の環境に原因がある可能性もありますね。すんなりBULK INSERTの対応が入れれそうと思っていたのですが、思わぬドはまりです…。アドバイス誠にありがとうございます。

sazi

2019/03/24 04:35

CSVの確認にはどういったエディタを使われていますか？私はサクラエディタです。 LFやCRも記号として識別されます。

gysnet

2019/03/24 04:35

度々すみません。以下の2つのエラーメッセージは最初のエラーに引きずられて発生しているものになりますでしょうか？・メッセージ 7399、レベル 16、状態 1、行 1 リンクサーバー "(null)" の OLE DB プロバイダー "BULK" により、エラーがレポートされました。プロバイダーからエラーに関する情報を取得できませんでした。・メッセージ 7330、レベル 16、状態 2、行 1 リンクサーバー "(null)" の OLE DB プロバイダー "BULK" から行をフェッチできません。

gysnet

2019/03/24 04:37

sazi様私もサクラエディタを使用しています。

sazi

2019/03/24 04:50 編集

LFだと、「←」で表現されているはずですがどうでしょう

sazi

2019/03/24 05:00

項目の区切りが正しくないと、 >一括読み込み: データファイルで予期しないファイルの終了が検出されました。行の区切が正しくないと、 >一括読み込みデータ変換エラー (切り捨て)。が発生しています。

gysnet

2019/03/24 05:01

質問のところにキャプチャを付けさせていただきました。私の設定が悪いのでしょうか、↓で表示されています。