[SQL Server] ","(ダブルコーディション)区切りのCSVをBULK INSERTする方法(FMTフォーマット)
下記のように","（ダブルコーディション、カンマ、ダブルコーディション）区切りのCSVファイルを
BULK INSERTしてデータを登録する方法について説明したいと思います。
CSVファイルのデータ
"1","商品1","100"
"2","商品2","200"
"3","商品3","300"
"4","商品4","400"
"5","商品5","500"
この場合はフォーマットファイルの使用して登録することができます。
フォーマットファイルは2つの方式があり、fmtもしくはxmlを使います。
","(ダブルコーディション)区切りのCSVをBULK INSERTする方法
今回はFMTフォーマットファイルの使用して登録する方法を説明します。
では、テストの為に上記のCSVファイルを「C:\innoya」フォルダーを作成して「Data.csv」ファイルで保存してください。
次はテーブルを作成しますので、下記のSQLを実行して「dbo.TempData」テーブルを作成してください。
CREATE TABLE dbo.TempData
(
Sn int
,CodeName nvarchar(20)
,Amount nvarchar(20)
)
これで準備が整いました。
次はテキストエディターで下記の内容を登録して「C:\innoya」フォルダに「Data.fmt」で保存してください。
11.0
4
1 SQLCHAR 0 15 "\"" 0 dummy ""
2 SQLCHAR 0 20 "\",\"" 1 col1 ""
3 SQLCHAR 0 20 "\",\"" 2 col2 ""
4 SQLCHAR 0 20 "\"\r\n" 3 col3 ""
上記の内容を簡単に説明すると。。。。
11.0 : SQL Serverのバージョンです。 9.0 (SQL Server 2005), 10.0 (SQL Server 2008), 11.0 (SQL Server 2012)
4 : 列の数です。csvファイルには列が3つしかありませんが、"（ダブルコーディション）影響で1つが増えます。
1 SQLCHAR 0 15 "\"" 0 dummy "" ： これは列の情報記述したものです。
これをまた簡単に説明すると。。
1 : データ ファイル内の各フィールドの位置を示す番号
SQLCHAR ： データ ファイルの特定のフィールドに格納されているデータ型
0 ： フィールドのプレフィックス長文字の数
15 ： データ ファイルの特定フィールドに格納されるデータ型の最大バイト数。
"\"" ： データ ファイルのフィールドを分割する区切り文字
0 ： SQL Server テーブルで列が表示される順序
dummy ： SQL Server テーブルからコピーされる列の名前
"" ： データ ファイル内の文字データや Unicode データを格納するときに使用される照合順序。
こんな感じです。
難しいですか？？
各行の最後の部分に下記のような部分があります。
0 dummy "" ： "（ダブルコーディション）影響でゴミデータを作成する必要があります。
1 col1 "" ： ここからDBの連携するカラムです。CSVデータだと「１、２、３，４、５」のデータが入ります。
2 col2 "" ： CSVデータだと「商品１、商品２。。。商品５」のデータが入ります。
3 col3 "" ： CSVデータだと「100、200。。。500」のデータが入ります。
またcol1,col2,col3というカラム名を設定する部分がありますが、なんでもいいので、あまり気にしないでください。
私は分かりやすくあういうカラム名を使用しています。
またCSVファイルのデータのデータが最初から「","(ダブルコーディション)」を使用しない場合は
FMTフォーマットファイルを書き方が分かりますので、下記の例文をご参考ください。
CSVファイルのデータ
0001,000,"ああああ","おおおお",1
0002,000,"かかかか","ここここ",2
0003,000,"ささささ","そそそそ",3
FMTフォーマットファイルの書き方
11.0
5
1 SQLCHAR 0 50 "," 1 col1 ""
2 SQLCHAR 0 50 ",\"" 2 col2 ""
3 SQLCHAR 0 50 "\",\"" 3 col3 ""
4 SQLCHAR 0 50 "\"," 4 col4 ""
5 SQLCHAR 0 50 "\r\n" 5 col5 ""
ここに全部書きながら説明するのは難しいので、実際に実行して動作を確認しながら理解しましょう。
もしくはQ&A掲示板に質問を書いてください。
ここまで準備が終わりましたら下記のSQLを実行して実際にデータを登録してみましょう。
BULK INSERT dbo.TempData
FROM
'C:\innoya\Data.csv'
WITH
(
FORMATFILE = 'C:\innoya\Data.fmt'
)
正常に登録できましたか？
もし下記のようにエラーが発生する方はCSVファイルの文字コードを「ANSI」に変更後再度実行してみてください。
ここでは「UNICODE」でCSVファイルが保存されているとエラーが発生しますね。
エラーメッセージー
メッセージ 4832、レベル 16、状態 1、行 1
一括読み込み: データ ファイルで予期しないファイルの終了が検出されました。
メッセージ 7399、レベル 16、状態 1、行 1
リンク サーバー "(null)" の OLE DB プロバイダー "BULK" により、エラーがレポートされました。プロバイダーからエラーに関する情報を取得できませんでした。
メッセージ 7330、レベル 16、状態 2、行 1
リンク サーバー "(null)" の OLE DB プロバイダー "BULK" から行をフェッチできません。
では、問題なく正常に登録されたらデータを確認してみましょう。
SELECT TOP 20 * FROM dbo.TempData
実行結果