SQLServerで可変調CSVを取り込む必要が出た場合

業務ソフトのデータ移行などが特にそうなのですが、困った事に可変長CSVを加工して所定のテーブルに取り込む必要に迫られる事があります。
こういったデータ調の場合、COBOLからの移管データなど何等かの特殊ケースが多いです。

これはSQL Serverに限った事でも無いです。
しかしSQL Serverは何かと、かゆい所に手が届く命令を持っていないものがあったりするので悩む事が多くあります。
バージョンが上がる事によって良くなっていますが、過去にはSQL Server2000より前の時代はRowNumに相当するものが存在しないなどがありました。
この記事はわりかし自分用メモ。

とりあえずはCSVのデータ構造次第

通常なら、CSVをそのままバルクインサートかけてしまい、そのまま取り込んでOKなものが大半だと思います。
所が1行目、2行目、3行目でそれぞれ意味を持っていて、規則的にヘッダー、データ、終端情報といった構成になっていたりするものなどがあります。
こんなものを安直に取り込もうとしても無理があります。

バルクインサートは使いますが、仕方ないので一旦はCSVの形式を丸ごとワークテーブルにぶち込むしかないです。

CREATE TABLE dbo.workdata
(dataindex int IDENTITY(1,1)
,inputdata varchar(20000)
);

こんな感じで自動連番と暫定的にこれくらい取ったら問題ないであろう文字数を定義しておきます。

BULK INSERT
workdata
FROM
'C:\work\hogehoge.csv'    -- CSVファイルのフルパスを指定
WITH
(
FIRSTROW = 1,           -- ヘッダーを除いた1行目から読み込む
ROWTERMINATOR = '\n'    -- 1レコードは改行区切り
)

可変調なのでFIELDTERMINATORは使いません。
※CSVのイメージ通りにぶち込んで、データ加工の行を特定するために自動連番を発行する。

ワークデータを加工して特定のテーブルにぶち込む

特定データの扱いがどういったものかは不明なので、ここで明確に答えを書く事は出来ません。

とりあえずは取り込んだワークテーブルを行単位で加工するためにフェッチする必要があります。

フェッチすると行数とCSVデータが取れるので、行数毎で意味合いの違うものであれば、行数のステップ毎で処理を作ります。

後は明細データの定義に従って所定のテーブルにInsertしていきます。
面倒なのはコメント項目などで「”」や「'」で括ってある中に特殊文字が含まれていたりする時でしょうか。
仕方ないので特殊文字が入っていた場合は特定のカラム位置で分岐条件を作ってエスケープする様に加工してInsertします。

失敗したなぁ。
このケースは3年くらい前にぶち当たって色々悩んだものが多く、SQLServer固有の問題ですんなりとは問屋が卸さなかった筈なのに忘れてしまいました。処理都合でカンマを敢えて｜へ一時的に置換する必要が出たり、Splitで悩んだ記憶があります。
思い出したら追記します・・・
肝はFIELDTERMINATORを敢えて使わない事です。
IDENTITYを使うと再取り込み時に都合が悪くなるため、dropしておく必要があります。