【SQL Server】BulkInsert がうまくいかない場合のメモ

SQL Server 2017からは

FORMAT = ‘CSV’

と書けば勝手に上手くやってくれるらしいっすね
2018/10/16 追記

---

ずばりこれ

・CSVの改行コードは 「CR+LF(\r\n)」にして、
Bulk Insertのオプションに、

ROWTERMINATOR = '\n'

を指定する。「\r\n」ではない。
・各データは「"」で囲む
でないと、カンマを含むデータが存在した場合、列がずれる。
・日本語を使う場合の文字コードは shift-jis

エクセルでデータを編集した場合

・普通にエクスポートすると「”」で囲んでくれないので注意。
これをどうにかするには、
1. 全体の書式設定を「!"@!"」にしてCSV出力（すると「"""」で囲まれる）
2. CSV出力後、「”””」を「"」に一括変換

実例

失敗例1 改行コードが\r\nなので「ROWTERMINATOR = '\r\n'」を指定する

import_test.csv

ID,VALUE1,VALUE2
AA,aa,11

SQL

BULK INSERT [test]
FROM 'import_test.csv'
WITH
(
   FIRSTROW = 2,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\r\n'
)

結果

(0 行処理されました)

改行が認識されず、全て１行として処理されてしまう。
FIRSTROW = 2
を指定しているので、２行目から見ようとするが、２行目がないことになっている。

ので、ROWTERMINATOR = '\n' にしましょう。
SQL

BULK INSERT [test]
FROM 'import_test.csv'
WITH
(
   FIRSTROW = 2,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

失敗例2 カンマが含まれる値

impot_test.csv

ID,VALUE1,VALUE2
AA,ブレスケア(3,000円),匂いが取れる
BB,舞茸1kg(4,000円),おいしい

SQL

BULK INSERT [test]
FROM 'import_test.csv'
WITH
(
   FIRSTROW = 2,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

結果
select * from test

ID	VALUE1	VALUE2
AA	ブレスケア(3	000円),匂いが取れる
BB	舞茸1kg(4	000円),おいしい

カンマが含まれてるとそこで区切られてしまうのでこうしましょう。
impot_test.csv

"ID","VALUE1","VALUE2"
"AA","ブレスケア(3,000円)","匂いが取れる"
"BB","舞茸1kg(4,000円)","おいしい"

SQL

BULK INSERT [test]
FROM 'import_test.csv'
WITH
(
   FIRSTROW = 2,
   FIELDTERMINATOR = '\",\"', -- 区切り文字に注意
   ROWTERMINATOR = '"\n'
)
update test
set ID = STUFF(ID,1, 1,'')
,VALUE2 = STUFF(VALUE2,len(VALUE2), 1,'')

※行頭の「"」（ダブルクォート）は消えないので、UPDATEで消す。

結果
select * from test

ID	VALUE1	VALUE2
AA	ブレスケア(3,000円)	匂いが取れる
BB	舞茸1kg(4,000円)	おいしい

CSVデータは、ダブルクォートで囲むのは基本ですね。
ちなみに、STUFF関数はtext型だと使えないので注意してください。
varchar型を使いましょう。

create table test (ID varchar(50),VALUE1 varchar(50), VALUE2 varchar(50))

最後に

罠が多いですよね。
実際の改行コードは「\r\n」なのになんで「\n」を指定しなくちゃならないんでしょうね。
もし理由を知っている人がいたら是非教えてください。
コメント待ってます！