- PR -

SQLServerのテーブルをVBAでエクスポート。でもクォートがつかない

投稿者投稿内容
take
大ベテラン
会議室デビュー日: 2004/08/13
投稿数: 177
お住まい・勤務地: 沖縄県北部
投稿日時: 2006-04-05 16:07
いつもお世話になります。SQLServer2000です。

EXCEL2000のVBAでSQLServer2000データベースに接続し、
テーブルのデータをテキストファイルにエクスポート
したいと思っています。
SQLDMOオブジェクトを使用してできたことはできたのですが、
データをダブルクォートで囲って出力させたいのにどうも指定
の仕方が見つかりません。
現状は、
コード:
Dim voSQLSrv As SQLDMO.SQLServer
Dim voBulkCpy As SQLDMO.BulkCopy

Set voSQLSrv = New SQLDMO.SQLServer
With voSQLSrv
.Connect ServerName:=csSrvName, _
Login:=csLogInName, _
Password:=csPwd
End With

Set voBulkCpy = New SQLDMO.BulkCopy

With voBulkCpy
.DataFileType = SQLDMODataFile_SpecialDelimitedChar
.FirstRow = 1
.LastRow = 1
.SuspendIndexing = True
.ColumnDelimiter = ""","""
.RowDelimiter = """" & vbCrLf
.DataFilePath = argsTargetFile
End With

With voSQLSrv
.Databases(csDbName).Tables(argsViewName).ExportData voBulkCpy
.Close
End With


のようにしてデリミタを指定しているのですが、行の先頭にダブルクォート
がつかず未完成の状態です。そもそもこんなことをしなくてもできそうな
気がしてならないのですが、どなたかご存知の方がいらっしゃいましたら
ご教授のほどよろしくお願いします。
じゃんぬねっと
ぬし
会議室デビュー日: 2004/12/22
投稿数: 7811
お住まい・勤務地: 愛知県名古屋市
投稿日時: 2006-04-05 16:32
引用:

takeさんの書き込み (2006-04-05 16:07) より:

デリミタを指定しているのですが、行の先頭にダブルクォートがつかず未完成の状態です。


今の実装では、先頭にはつかないのは当たり前だと思います。
そもそも、デリミタは CSV で言うカンマにすぎません。

行の末尾に付くのだって、行のデリミタに引用符をつけているからであって、
本来そうするためのものではないです > デリミタ

CSV などでは「引用符」は数値以外のものを表す時に明示的に使うものですが、
BulkCopy の場合、つかない方が都合が良かったりします。(確か)

というのも、今度は「インポート」する時に、引用符がついていると、
データに引用符が付いたままでインポートされていたような...

_________________
C# と VB.NET の入門サイト
じゃんぬねっと日誌
take
大ベテラン
会議室デビュー日: 2004/08/13
投稿数: 177
お住まい・勤務地: 沖縄県北部
投稿日時: 2006-04-05 16:40
じゃんぬさん、ご返答ありがとうございます。

引用:

今の実装では、先頭にはつかないのは当たり前だと思います。
そもそも、デリミタは CSV で言うカンマにすぎません。

行の末尾に付くのだって、行のデリミタに引用符をつけているからであって、
本来そうするためのものではないです > デリミタ

CSV などでは「引用符」は数値以外のものを表す時に明示的に使うものですが、
BulkCopy の場合、つかない方が都合が良かったりします。(確か)

というのも、今度は「インポート」する時に、引用符がついていると、
データに引用符が付いたままでインポートされていたような...




なるほど。まず自分の「デリミタ」の認識がおかしかったというのと、クォート
にこだわる理由を書いてなかったですね。
顧客側からダブルクォートつきのフォーマットにしてくださいとの
ことなので(意図は分からず)どうしてもダブルクォートつきにしたいのです。
出力しなければならないテーブルが100近くあって、bcpのバッチを作ろうかと
試みるもフォーマットファイルなるものが必要ということであきらめました。

1.csvでカンマ区切り、テキストはダブルクォートで囲む。
2.全テーブルをループで処理。ワンタッチエクスポート。

を実装できるほかの方法を探しています。
bulkcopy以外にexportする方法があるのでしょうか?
もしございましたら引き続きよろしくお願いします。
じゃんぬねっと
ぬし
会議室デビュー日: 2004/12/22
投稿数: 7811
お住まい・勤務地: 愛知県名古屋市
投稿日時: 2006-04-05 17:08
引用:

takeさんの書き込み (2006-04-05 16:40) より:

bulkcopy以外にexportする方法があるのでしょうか?
もしございましたら引き続きよろしくお願いします。


すっごく遅くなっちゃいますけど、手動でできますよね。
SQL Server に接続して全件取得して書き出す。

_________________
C# と VB.NET の入門サイト
じゃんぬねっと日誌
take
大ベテラン
会議室デビュー日: 2004/08/13
投稿数: 177
お住まい・勤務地: 沖縄県北部
投稿日時: 2006-04-05 17:17
引用:

すっごく遅くなっちゃいますけど、手動でできますよね。
SQL Server に接続して全件取得して書き出す。


うーん、やっぱそれしかないのでしょうか・・・。
とりあえず手動で、効率で上司が何か言って来たらデリミタ
でいんちきして行頭に何か工夫してやってみます。
ありがとうございました。
じゃんぬねっと
ぬし
会議室デビュー日: 2004/12/22
投稿数: 7811
お住まい・勤務地: 愛知県名古屋市
投稿日時: 2006-04-05 17:20
引用:

takeさんの書き込み (2006-04-05 17:17) より:

デリミタでいんちきして行頭に何か工夫してやってみます。


これで良いかもw
でも、読み出して書き出さなくてはいけませんから、これも結構コスト高かもしれません。

_________________
C# と VB.NET の入門サイト
じゃんぬねっと日誌
take
大ベテラン
会議室デビュー日: 2004/08/13
投稿数: 177
お住まい・勤務地: 沖縄県北部
投稿日時: 2006-04-05 17:24
引用:

じゃんぬねっとさんの書き込み (2006-04-05 17:20) より:
引用:

takeさんの書き込み (2006-04-05 17:17) より:

デリミタでいんちきして行頭に何か工夫してやってみます。


これで良いかもw
でも、読み出して書き出さなくてはいけませんから、これも結構コスト高かもしれません。





そうですね。まあでも手動でやるよりはいろんな意味でコストがいいのでw
とりあえずやってみます。他の方法をご存知の方がいらっしゃいましたら
引き続きよろしくお願いします。
matu_tak
会議室デビュー日: 2003/02/06
投稿数: 13
投稿日時: 2006-04-07 02:59
松本です。こんにちは
# ¥ を 1個にする方法が分からない... orz

> Dim voBulkCpy As SQLDMO.BulkCopy
> 1.csvでカンマ区切り、テキストはダブルクォートで囲む。
>
bcp (SQLDMO.BulkCopy) だと、
テキストをダブルクォート(二重引用符)で囲むには
フォーマット ファイルを使わないといけないです。
列数が多い場合はかなり面倒です ^^;

-------------
Northwind の Products テーブルが例だとこんな感じです (う〜む長い...)。

■ 手順1.
まず、コマンドプロンプトを開いて bcp コマンドを使って
単純に CSV 形式で吐き出したテキストファイルを作成

コード:
bcp Northwind..Products out c:\\prod.csv -T -c -t "," -F 1 -L 1


注: 2個の ¥ マークを 1 個に変更して実行ください

生成された c:\\prod.csv の中身
1,Chai,1,1,10 boxes x 20 bags,18.0000,39,0,10,0


■ 手順2.
DTS パッケージ デザイナを使ってフォーマット ファイルを自動生成させる
その手順は
 1) DTS デザイナで新規パッケージを作成
  SQL Server Enterprise Manager で「データ変換サービス」を展開して
  「ローカル パッケージ」を右クリックして「新規パッケージ」
 2)「接続」→「Microsoft OLEDB Provider for SQL Server」を追加。
  インポート先のテーブルが含まれているデータベースを選択しておく。
  今回は Northwind を選択
 3)「タスク」→「一括挿入タスク」を追加。
 4)「変換先テーブル」に、インポート先のテーブルを選択。
  今回は Products を選択
 5)「変換元データファイル」に、インポート元のテキストファイルのパスを入力。
  今回は c:\\prod.csv と入力(手順1で作成したもの。¥マークは 1個です)
 6)「フォーマット ファイルを使用」をチェックして「生成」ボタンをクリック。
 7)「フォーマット ファイルの選択」に任意のファイル名を入力。適当な名前で OK。
  ここで入力した名前のフォーマット ファイルが生成されることになる。
  今回は c:\\fff.fmt と入力

あとは、ファイルのフォーマットにしたがってウィザードを進めると
フォーマット ファイルができあがる(CSV 形式にするには「区切り記号」で
「カンマ」を選択しておく)。

できあがったフォーマット ファイルの例

コード:
7.0
10
1 SQLCHAR 0 4 "," 1 ProductID
2 SQLCHAR 0 40 "," 2 ProductName
3 SQLCHAR 0 4 "," 3 SupplierID
4 SQLCHAR 0 4 "," 4 CategoryID
5 SQLCHAR 0 20 "," 5 QuantityPerUnit
6 SQLCHAR 0 8 "," 6 UnitPrice
7 SQLCHAR 0 2 "," 7 UnitsInStock
8 SQLCHAR 0 2 "," 8 UnitsOnOrder
9 SQLCHAR 0 2 "," 9 ReorderLevel
10 SQLCHAR 0 2 "\\r\\n" 10 Discontinued


注: 2個の ¥ マークは本当は 1 個です

■ 手順3.
手順2で作成したフォーマット ファイル(c:\\fff.fmt)を
テキスト エディタで開いて、編集する。

例えば、ProductName 列のデータをダブルクォートで囲みたい場合は、

コード:
1 SQLCHAR 0 4 ",\\"" 1 ProductID
2 SQLCHAR 0 40 "\\"," 2 ProductName


のように編集して、上書き保存する。
注: 2個の ¥ マークを 1 個に変更する必要があります

フォーマット ファイルの作り方はこちらが参考になると思います。
http://www.sqlpassj.org/bbs/bbs_disp.aspx?forum_id=1¤t_page=1&disp_mode=2&detail_mode=1&message_id=2258


■ 手順4.
プログラムの DataFileType プロパティと FormatFilePath プロパティを変更する

コード:
Set voBulkCpy = New SQLDMO.BulkCopy
With voBulkCpy
 .DataFileType = SQLDMODataFile_UseFormatFile
 .FirstRow = 1
 .LastRow = 1
 .SuspendIndexing = True
 .FormatFilePath = "c:\\fff.fmt"   '手順2・3 で作成・編集したフォーマット ファイル名
 .DataFilePath = "c:\\out.txt"
End With



結果 (c:\\out.txt の中身。ProductName 列のデータがダブルクォートで囲まれる)

1,"Chai",1,1,10 boxes x 20 bags,18.0000,39,0,10,0
-------------

p.s
ちなみに、SQL Server 2005 の bcp にはフォーマット ファイルを生成する機能がつきました。

米田さんのブログより
bcpでフォーマットファイル生成 (SQL Server 2000 で便利な SQL Server 2005 のツール)
http://blogs.sqlpassj.org/yoneda/archive/2005/12/27/15204.aspx

SQL Server 2005 Books Online フォーマット ファイルの生成
http://msdn2.microsoft.com/ja-jp/library/ms191516(SQL.90).aspx

あと、bcp ではなくて DTS を使うという方法もありますね....
DTS ならテキストをダブルクォートで囲むのワン クリックで
GUI ベースで簡単に作れたりします...

DTS に関しては NEC 鈴木さんの連載が分かりやすくてお勧めです。
http://www.microsoft.com/japan/msdn/sqlserver/columns/dts/dts1.asp

あと 自習書もお勧めです(管理編 No.3 と No.4)。
http://www.microsoft.com/japan/technet/prodtechnol/sql/2000/evaluate/exercises.mspx

スキルアップ/キャリアアップ(JOB@IT)