[SQL Server] ","(ダブルコーディション)区切りのCSVをBULK INSERTする方法(XMLフォーマット)

inno
2014-07-02 20:12 10067 0
MS-SQL / SQL Server
","(ダブルコーディション)区切りのCSVをBULK INSERTする方法(XMLフォーマット)


下記のように","(ダブルコーディション、カンマ、ダブルコーディション)区切りの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する方法


今回はXMLフォーマットファイルの使用して登録する方法を説明します。

では、テストの為に上記のCSVファイルを「C:\inno」フォルダーを作成して「Data.csv」ファイルで保存してください。
次はテーブルを作成しますので、下記のSQLを実行して「dbo.TempData」テーブルを作成してください。


CREATE TABLE dbo.TempData
(
Sn int
,CodeName nvarchar(20)
,Amount int
)

これで準備が整いました。
次はテキストエディターで下記の内容を登録して「C:\inno」フォルダに「Data.xml」で保存してください。

<?xml version="1.0"?>
<BCPFORMAT
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&quot;"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="&quot;\r\n" />
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="Col1" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col2" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Col3" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>


上記の内容を簡単に説明すると。。。。
<RECORD>と<ROW>で区別します。


<RECORD>ノードには<FIELD>が存在します。
FIELDのID値が「1」の識別子「"」の前に来るデータが、「COLUMN1」のデータになります。
つまり、「FIELD1」前の「COLUMN1」には何にもないので空白!
FIELDのID値が「2」の識別子「","」の前に来るデータが、「COLUMN2」のデータになります。
つまり、「FIELD2」前の「COLUMN2」には「1」が入っています。
FIELDのID値が「3」の識別子「","」の前に来るデータが、「COLUMN3」のデータになります。
つまり、「FIELD3」前の「COLUMN3」には「商品1」が入っています。

こんな感じでXMLフォーマットファイルを作成して行きます。
上記のCSVファイルの内容だと
<FIELD>が4個、<COLUMN>が3個になります。
また<COLUMN>のID値は「2」から始まります。
<COLUMN>のID値「1」はゴミデータなので無視して作成します。

では、下記のようなCSVファイルだとどうやって作成すると思いますか。

CSVファイルのデータ
1,0,"ああああ","おおおお",1
2,0,"かかかか","ここここ",2
3,0,"ささささ","そそそそ",3

この場合は、<FIELD>が5個、<COLUMN>が5個になります。
実際に作成すると下記のようになります。

<?xml version="1.0"?>
<BCPFORMAT
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR=","/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=",&quot;"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="&quot;,"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" />
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLINT"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>

<FIELD>のID値が「1」の識別子「,」の前には<COLUMN>のSOURCE値「1」のデータが存在するため
「1」番から記述しています。

これは実際にいろいろテストしながら覚えた方がいいです。

ここに全部書きながら説明するのは難しいので、実際に実行して動作を確認しながら理解しましょう。
もしくはQ&A掲示板に質問を書いてください。

ここまで準備が終わりましたら下記のSQLを実行して実際にデータを登録してみましょう。


BULK INSERT dbo.TempData
FROM
'C:\inno\Data.csv'
WITH 
(
FORMATFILE = 'C:\inno\Data.xml'
)

   
正常に登録できましたか?
もし下記のようにエラーが発生する方はCSVファイルの文字コードを「ANSI」に変更後再度実行してみてください。
ここでは「UNICODE」でCSVファイルが保存されているとエラーが発生しますね。

エラーメッセージー

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

必ず、CSVファイルの文字コードを「ANSI」で保存してください。
では、問題なく正常に登録されたらデータを確認してみましょう。

SELECT TOP 20 * FROM dbo.TempData


実行結果


コメント