SQLServerで項目数が異なるCSVファイルを取り込む方法

質問

(先ほど古いIDで登録してしまったため、削除して新しいIDで登録しなおしました)

お世話になります。
SQLServer2005 を使っています。

行によってデータ型も項目数も異なる CSV ファイルがあり、それを
テーブルに読み込みたいと思っています。
(POSのデータです)

サイズが比較的大きいので、BULK INSERT を使いたいと思い、
以下のように記述してみました。

BULK INSERT T_hoge FROM 'c:\hoge.csv'
WITH (
　DATAFILETYPE = 'char',
　FIELDTERMINATOR = ',',
　ROWTERMINATOR = '\n'
)

CSV項目数が可変なので、取り込む側のテーブルの項目数に
余裕を持たせ、1行1レコードで取り込みたいのですが、
改行を無視して取り込まれてしまうようです。

項目数可変のCSVを取り込む方法があれば教えてください。
宜しくお願い致します。

回答No.1

jamshid6 · ベストアンサー

カラム数が不定のものは、BULK INSERT一発で取り込むことはできないと思います。
（そもそも「ちゃんとしていないファイルをいきなり取り込もうとするのはおかしい」という考えだと思います）

したがって、考え方は２つで、
・まずファイルをちゃんとしてから取り込む
・一旦ワークに取込んでから、正しくセットするような処理を組む

前者のアプローチとしては、
1)VBScriptなどでファイルのカンマを補って、シンプルに取り込めるファイルにし、BULK INSERTする
　（SQL Server側からの処理で完結させたければ、xp_cmdshellでVBSを叩くとかでも）
2)SSISを使って取込む（スクリプトタスクでファイルを補正し、データフローで取り込む）

後者のアプローチとしては、
3)一旦ワークテーブルに区切らずにBULK INSERTし、カンマでフィールドを切り出してから、本来のテーブルにINSERTする
4)SSISを使って取込む（データフロー内でスクリプトタスクを使って補正しつつ取込む）
5)CLRストアドプロシージャを作成する（ファイルを読み出し、SPLITしてINSERTするストアドプロシージャをBIDSで作成する）

などが考えられます。
個人的には前者で考えた方がいいと思うのですが、一応3)のアプローチだとどうなるかサンプルを示します。

※最大カラム数が7とするケース
DECLARE @NUMOFCOL int
SET @NUMOFCOL=7
CREATE TABLE #hoge (FLD varchar(max))

BULK INSERT #hoge
FROM 'c:\hoge.csv'
WITH (ROWTERMINATOR='\n')

ALTER TABLE #hoge ADD [F1][varchar](10)
ALTER TABLE #hoge ADD [F2][varchar](10)
ALTER TABLE #hoge ADD [F3][varchar](10)
ALTER TABLE #hoge ADD [F4][varchar](10)
ALTER TABLE #hoge ADD [F5][varchar](10)
ALTER TABLE #hoge ADD [F6][varchar](10)
ALTER TABLE #hoge ADD [F7][varchar](10)

UPDATE #hoge SET FLD=FLD+REPLICATE(',',@NUMOFCOL-LEN(FLD)+LEN(REPLACE(FLD,',','')))

DECLARE @CNT int
DECLARE @sql varchar(max)
SET @CNT=1
WHILE (@CNT<=7)
BEGIN
SET @sql='UPDATE #hoge SET F'+CONVERT(varchar,@CNT)+'=LEFT(FLD,CHARINDEX('','',FLD)-1)'
EXEC (@sql)
UPDATE #hoge SET FLD=RIGHT(FLD,LEN(FLD)-CHARINDEX(',',FLD))
SET @CNT=@CNT+1
END

INSERT INTO t_hoge SELECT F1,F2,F3,F4,F5,F6,F7 FROM #hoge

DROP TABLE #hoge

お礼

早速ありがとうございます。
やっぱりすんなり取り込むのは無理なんですね。

教えていただいたサンプルを試して見ようと思います。

回答No.2

nda23

>行によってデータ型も項目数も異なる CSV ファイルがあり、それを
>テーブルに読み込みたいと思っています。
つまり、定義の違うレコードが混ざったCSVということですね。
無理です。受け入れ側（テーブル）は列数、列のデータ型が決まって
いるのですから。仮に元ネタは一緒で、NULLの所が省略されたと
言いたいかも知れませんが、それは人間だけに分かる話です。

お礼

ありがとうございます。

ですよね。
某大手有名会社のPOSレジのデータなのですが、
仕様的にむちゃくちゃな気がします。