CSVファイルのインポートについて

質問

SQL Server 2005 Standard, Windows XP(SP3)を使用しています。
あるツールから生成されるCSVファイルのデータベースへのインポートしたいと考えています。

しかし、CSVファイル以下のようなフォーマットになっています。
(・・・番号）は無視してください

date,2009/05/27 （・・・１）
name,test　（・・・２）

時刻,A,B,C,D,E,F　（・・・３）
11:13:05,1,2,3,4,5,6　　　（・・・４）
11:13:06,7,8,9,10,11,12　 （・・・５）
（以下４，５のようなデータが続く…）

となっています。
1は日付情報、２は特に意味無し、３はカラムの情報としたい物
4（以降）はデータとなっています。

データベースへは　"|"　をカラムの区切りとすると

時刻 |A|B|C|D |E |F |
2009/05/27 11:13:05|1|2|3|4 |5 |6 |
2009/05/27 11:13:06|7|8|9|10|11|12|
(・・・以降データが続く)

上記のように（３）をカラム情報にして、不必要な情報を省いた状態でデータを取り込みたいと考えております。

同じく上記の最初のフィールドのように、元データのdate情報を時刻情報と合わせてdatetime型として一つので取り込みたいと考えています。

この為には、文字列の操作が必要となってくると思いますが、SQL Server上ではこのような事はできるのでしょうか？

回答No.3

jamshid6 · ベストアンサー

そういえば、前にサーバはSQL Server 2000で、クライアントPC側にはSSMSだけインストールされていると書かれていませんでしたか？

・ファイルはクライアントPC側にあって、サーバにあるテーブルにデータを入れたい
　（処理の「実行」は、クライアントPC側で行う）
・ファイルはサーバ側にはコピーできない
・VBなどで多少の加工処理は書いてもよい（これもサーバでは実行できない）

上記のような理解ですが、それならばもっとも適した方法は、
1)VBなどで、ファイルを読み込んで１行目の日付を抜き出しつつデータ行の先頭に追加して別ファイルに書き出す処理だけを書く
2)BCPでファイルをサーバ側のテーブルにアップロードする
　（BCPユーティリティは本来コマンドラインから実行するものです）
です。

xp_cmdshellについては、exeも実行できますが、セキュリティ上の問題があるので、サーバで実行するのは嫌がられることがあります。
クライアントPCにもデータベースエンジンがインストールされているならば、実行させられるでしょうが、
結局サーバ側のテーブルに入れるにはBCPかOPENROWSETでリンクサーバ経由INSERTのいずれかが必要です。

お礼

ご回答ありがとうございます。
的確なアドバイスで本当に参考になります。

>そういえば、前にサーバはSQL Server 2000で、クライアントPC側にはSSMSだけインストールされていると書かれていませんでしたか？

その通りです。そこまで覚えていただいていて申し訳ありません。
（ツール面の機能統一をしたいので、2005に変更したいのですが当分先になりそうです。）

>・ファイルはクライアントPC側にあって、サーバにあるテーブルにデータを入れたい
　（処理の「実行」は、クライアントPC側で行う）
>・ファイルはサーバ側にはコピーできない
>・VBなどで多少の加工処理は書いてもよい（これもサーバでは実行できない）

これも把握されている通りです。
csvを生成するソフトを入れる許可が下りればサーバー側でファイルを持てるのですが…。

>それならばもっとも適した方法は、
>1)VBなどで、ファイルを読み込んで１行目の日付を抜き出しつつデータ行の先頭に追加して別ファイルに書き出す処理だけを書く
>2)BCPでファイルをサーバ側のテーブルにアップロードする

ご提案いただいた上記の方法でトライしてみようと思います。

ちなみに調べてみたのですがxp_cmdshellは2005からの機能みたいで、2000上では使えないようですね。

回答No.4

jamshid6

補足です。

>ちなみに調べてみたのですがxp_cmdshellは2005からの機能みたいで、2000上では使えないようですね。

そんなことはありません。SQL Server 2000でも使えます。
http://msdn.microsoft.com/ja-jp/library/aa260689(SQL.80).aspx
むしろ、このストアドはテクノロジとしては古いです。
（xpはSQL Server 2000時代の拡張ストアドプロシージャのプレフィックスです。SQL Server 2005ではCLRが推奨されています）

回答No.2

jamshid6

1) ストアドプロシージャを１本書く
　以下のいずれかの方法でワークテーブルに読み込む。
　・BULK INSERT（カンマ区切り）
　・OPENROWSET（BULK フォーマットファイル使用）
　・xp_xmdshellによるBCP（カンマ区切り）
　１行目の日付を変数に取り込む。
　型変換して実テーブルにセットするときに時刻に日付をつないでINSERTする。

2) ストアドプロシージャを１本書く
　以下のいずれかの方法でワークテーブルに読み込む。
　・BULK INSERT（デリミタなし）
　・OPENROWSET（BULK）
　・xp_xmdshellによるBCP（デリミタなし）
　１行目の日付を変数に取り込む。
　各行はコンマで切り出し、型変換して実テーブルにセットするときに時刻に日付をつないでINSERTする

3)SSISまたはDTSパッケージを作成する
　１からつくらなくても、データのインポートユーティリティからパッケージは生成できます。
　これにファイル先頭の日付を取り出す処理と、先頭列に日付を加える処理を追加します。

1)の方式は、先頭数行がデータ列ではなく、カンマの数が不足しているため、場合によってはうまく読み込めない
ケースがあります。
（行スキップオプションはありますが、カンマが不足していると行カウントが正しくされない）
2)の方式は、カンマで文字列を切り出す処理に少し工夫が必要です。
（CHARINDEXを使うか、XMLを使うか、スカラー関数を作るか）
3)は行スキップが正確に働くので、データ取り込み部分は設定がわかりやすいですが、追加する処理は多少勉強しないと設定できないでしょう。

なお、1)2)の方法はサーバ側に対象ファイルが存在する必要があります。
それぞれをとても説明しきれないので、キーワードでMSDNをあたってみてください。

補足

色々な方法を紹介していただいてありがとうございます。
１），２）はサーバーにデータがないとできないようですので、難しそうですが3)の方法を考えなくてはいけないかもしれません。

ただ、xp_cmdshellですがこれは外部で作成したexeを実行することが可能なようですが実際のところどうでしょうか？

このexeに文字列を整形する機能を持たせることもできるのかなと考えています。

回答No.1

jamshid6

できるかできないかといえば、できます。

インポート先のテーブルが存在せず、CSVの内容に基づいて新規作成したいということであれば、SSIS(SQL Server Integration Service)を使って、パッケージを作成すれば可能です。

ストアドプロシージャを書く方法でもできなくはないですが、一旦ワークテーブルに取り込んで動的クエリでテーブルを作成する必要があるため、ちょっと厄介です。
（インポート先のテーブルが既に存在する前提ならば、もっと簡単です）

補足

ご回答ありがとうございます。
なるほどやはり何種類か実現手段があるのですね。

>（インポート先のテーブルが既に存在する前提ならば、もっと簡単です）

考えてみると確かにカラムの内容は固定になりますので、テーブルは存在していても問題ありませんね。
その場合どのような方法になるでしょうか？