こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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を取り込む方法があれば教えてください。
宜しくお願い致します。

投稿日時 - 2009-07-02 20:11:28

QNo.5093128

困ってます

質問者が選んだベストアンサー

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

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

前者のアプローチとしては、
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

投稿日時 - 2009-07-02 20:17:16

お礼

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

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

投稿日時 - 2009-07-03 09:30:42

このQ&Aは役に立ちましたか?

6人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

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

投稿日時 - 2009-07-02 20:51:14

お礼

ありがとうございます。

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

投稿日時 - 2009-07-03 09:33:27

あなたにオススメの質問