SQL Tips

 

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

[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データだと「1、2、3,4、5」のデータが入ります。
2 col2 "" : CSVデータだと「商品1、商品2。。。商品5」のデータが入ります。
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


実行結果


このエントリーをはてなブックマークに追加
2014-07-02 20:11:13   5187

コメント

[SQL Server] 日付の曜日を取得方法 (datepart, datename)

-.日付:2014-08-02   カテゴリ : -   閲覧数:9514

[SQL Server] tempdb データベースの移動方法

-.日付:2014-07-30   カテゴリ : -   閲覧数:2095

[SQL Server] IDENTITY に任意の値を設定はSET IDENTITY_INSERT

-.日付:2014-07-27   カテゴリ : -   閲覧数:3829

[SQL Server] 日時(DateTime)を文字列に変換する方法

-.日付:2014-07-17   カテゴリ : -   閲覧数:3246

[SQL Server] 文字列をバイナリに変換 / fn_hexstrtovarbin / varchar to varbinary

-.日付:2014-07-11   カテゴリ : -   閲覧数:5312

[SQL Server] バイナリを文字列に変換 / fn_varbintohexstr / varbinary to varchar

-.日付:2014-07-11   カテゴリ : -   閲覧数:7610

[SQL Server] テーブル名を変更する方法 - sp_rename

-.日付:2014-07-10   カテゴリ : -   閲覧数:5398

[SQL Server] 文字列に全角データが含まれているか確認する方法

-.日付:2014-07-10   カテゴリ : -   閲覧数:1746

[SQL Server] 文字列から全角データが含まれているかチェック  (1)

-.日付:2014-07-10   カテゴリ : -   閲覧数:7026

Copyright © 2015 INNOYA.COM All rights reserved. RSS