|
[三流君] Top ken3.orgへ [ASP解説] ASPの解説TOP [ASP記事 バックナンバー] 番号順のバックナンバー [SOHO/在宅プログラマー/派遣] 派遣のお話ほか [...サイトマップ(総合案内へ)] 分類別ガイド ADOで[ADO Mdb接続] ,[ADO Excel接続] ,[ADO CSV接続] [ASP Form データのやりとりPOSTとGET] [ASPでTextFile操作] [ASPでVBScriptを使う] [その他サンプル] |
| No.100 | 2004/10/31 SQL文で構造が同じ複数テーブルを集計してみた |
[ページTOPへ戻る] |
＜SQL文で構造が同じ複数テーブルを集計してみた＞
こんにちは、三流プログラマーのＫｅｎ３です。 今回は、 構造が同一の複数のテーブルから、 UNIONでデータをつなげ、 集計をしてみました。 いつもの三流的なアプローチなので、実際はアレンジして使ってください。/* * １．今回のキッカケ */
>（質問） 同じ構造（フィルード名）のテーブルがいくつかあります。 >この同じ条件でそれぞれ抽出してひとつに集計をさせたいのですが...。 >一番簡単なＳＱＬ文を教えて下さい。 >(例） テーブル T_2002年売上 T_2003年売上 T_2004年売上 > フィールド名 商品CD 売上金額 >（結果） 商品ＣＤ別に2002年から2004年の売上金額の合計を知りたい と、質問をもらった。 一番簡単なＳＱＬ文かぁ・・・なんだろうね。 よくあるパターンでテーブル構造が一緒ってことですよね。 いろいろなアプローチ方法ありそうだなぁ/* * ２．Select * From T_2002年売上, T_2003年売上, T_2004年売上 */
さてと、まずは、複数テーブルからSelectしてみますか。 テーブルは T_2002年売上 T_2003年売上 T_2004年売上 の３つで、 フィールドは 商品CD 文字型 売上金額 数値型 で、テーブル作って、テストデータを放り込み、 T_2002年売上 商品CD 売上金額 A001 100 B002 50 A001 20 CCCC 1200 T_2003年売上 商品CD 売上金額 A001 100 CCCC 500 T_2004年売上 商品CD 売上金額 B002 250 DDDDD 5000 単純に複数テーブルをFromにセットしてみました Select * From T_2002年売上, T_2003年売上, T_2004年売上 なんてSQLを作り、走らせると、 http://www.ken3.org/cgi-bin/test/test100-1.asp ↑実行テスト 結果 T_2002年売上.商品CD T_2002年売上.売上金額 T_2003年売上.商品CD T_2003年売上.売上金額 T_2004年売上.商品CD T_2004年売上.売上金額 A001 100 A001 100 B002 250 A001 100 A001 100 DDDDD 5000 A001 100 CCCC 500 B002 250 A001 100 CCCC 500 DDDDD 5000 B002 50 A001 100 B002 250 B002 50 A001 100 DDDDD 5000 B002 50 CCCC 500 B002 250 B002 50 CCCC 500 DDDDD 5000 A001 20 A001 100 B002 250 A001 20 A001 100 DDDDD 5000 A001 20 CCCC 500 B002 250 A001 20 CCCC 500 DDDDD 5000 CCCC 1200 A001 100 B002 250 CCCC 1200 A001 100 DDDDD 5000 CCCC 1200 CCCC 500 B002 250 CCCC 1200 CCCC 500 DDDDD 5000 うわ・・ボロボロだね。これでは使えないや・・・/* * ３．UNIONで構造が同じテーブルをつなげる */
オイオイ、テーブルつなげるのってUNIONじゃないの？ まぁ、チョットやってみただけなので許してね。 あらためて、 Select * From T_2002年売上 UNION Select * From T_2003年売上 UNION Select * From T_2004年売上 とSQL文を作り、発行してみた。 http://www.ken3.org/cgi-bin/test/test100-2.asp ↑実行テスト 結果 商品CD 売上金額 A001 20 A001 100 B002 50 B002 250 CCCC 500 CCCC 1200 DDDDD 5000 よし、OKと思ったが、よく見ると、 A001 金額100って2002年と2003年にあるのに１つしかレコードが発生してない。 これは？なぜだ・・・ UNION だけだと 同じデータを親切に消してくれるんだぁ。 これはこれで、使えるかもしれないけど、集計したいんだよね今は。/* * ４．UNION ALL でつなげる */
少し困っていたけど、UNION ALLなんて感じでつなげるとOKです。 ※ALL をつけると 同じデータは削除されません。 UNION ALL 演算子でつなげ、複数テーブルをSelectしてみました Select * From T_2002年売上 UNION ALL Select * From T_2003年売上 UNION ALL Select * From T_2004年売上 やっと縦に並んで取り出すことができたのかな？ http://www.ken3.org/cgi-bin/test/test100-3.asp ↑テスト実行 実行結果 商品CD 売上金額 A001 100 B002 50 A001 20 CCCC 1200 A001 100 CCCC 500 B002 250 DDDDD 5000 やっと、 A001 100も出てきて、複数テーブルの明細をつなげて取り出せました。/* * ５．SUM Group By したテーブルをUNIONでつなげる */
さてと、取り出したら次は集計ですよね。 集計は？SUMとGroup Byだよね、 Group By したテーブルをUNIONでつなげるかな。 単純に書くと、 SUM Group By したテーブルを UNION ALL 演算子でつなげ、複数テーブルをSelectしてみるんだから、 Select 商品CD, SUM(売上金額) AS 合計金額 From T_2002年売上 Group By 商品CD UNION ALL Select 商品CD, SUM(売上金額) AS 合計金額 From T_2003年売上 Group By 商品CD UNION ALL Select 商品CD, SUM(売上金額) AS 合計金額 From T_2004年売上 Group By 商品CD これを実行すると？どうなるのかな？ http://www.ken3.org/cgi-bin/test/test100-4.asp ↑テスト実行 実行結果 商品CD 合計金額 A001 120 B002 50 CCCC 1200 A001 100 CCCC 500 B002 250 DDDDD 5000 ↑あらら、年度別に集計されちまったよ これは、これで、使えそうな場合もあるんだけど今回はダメだよね。/* * ６.UNIONでつなげたテーブル(結果)をSUM Group Byする(集計する) */
だから、三流プログラマーはダメなんだって？ SUM Group By したテーブルをUNIONでつなげる なんて思考じゃなくて、 UNIONでつなげたテーブル(結果)をSUM Group Byする(集計する) そんな感じでしょ？ そっか、発想が、考え方の流れがSQLしてなかったのね。 データの集合を作ったら、それに対して処理かぁ。 Select 商品CD, SUM(売上金額) AS 合計金額 From ( Select 商品CD, 売上金額 From T_2002年売上 UNION ALL Select 商品CD, 売上金額 From T_2003年売上 UNION ALL Select 商品CD, 売上金額 From T_2004年売上 ) Group By 商品CD と、 ()の中がUNIONでつなげた明細です これをSUM,Group Byすると？ http://www.ken3.org/cgi-bin/test/test100-5.asp ↑テスト実行 実行結果 商品CD 合計金額 A001 220 B002 300 CCCC 1700 DDDDD 5000 ↑やっと複数テーブルから集計できたのかな？/* * ７．終わりの挨拶 ＜／ＨＴＭＬ＞ */
今回は、 ・構造が同じ複数のテーブルをUNIONでつなげた ・UNIONとUNION ALLの違い ・UNIONでつなげたテーブルに対してSUM,Group Byしてみた そんなところがポイントかな。 無駄な部分多いですが、集計処理の一つの案として、受け取ってください。 何かの参考となれば幸いです。 ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
| No.101 | 2005/01/21 ADO CSV接続 勝手に型を決めんなよ・・・ |
[ページTOPへ戻る] |
＜ADO CSV接続 勝手に型を決めんなよ・・・＞
こんにちは、三流プログラマーのＫｅｎ３です。 今回は、 ADOのCSV接続の自動で決まってしまうデータの型について少々。 でも、解決しなかったり・・・ いつもの三流的なアプローチなので、実際はアレンジして使ってください。 ※一部でも参考になればいいんだけど・・・/* * １．今回のキッカケ */
No.080 Microsoft Text Driver (*.txt; *.csv) で接続してみた http://www.ken3.org/asp/backno/asp080.html から >質問なのですがｃｓｖファイルを接続し、データを吸い上げたときに >IPアドレスのように111.222.333.444といった物が111.222333444とな >って上がってきてしまいました。吸い上げた時点で既にそのような形 >式になっていましたのでどうした物かと困っています。掲示板のログ と、質問をもらった。/* * ２．テストデータを作成してテストしてみる */
オバケが出たと言われたら、 そんなことないだろ、見間違いだろ と 言うのだが、現実に現象が発生するのか？ コンピュータの場合は確認しやすい。 なぜ？確認しやすいかって？ それは、同じデータを流して、何度も検証できるからかなぁ。 オバケの発生条件を教えてもらったので、 私もテストデータを作成して、見物してみることにした。 作成したCSVファイルは、ヘッダー付きで下記のように作成した。 http://www.ken3.org/cgi-bin/test/test101.csv 日付,IP,URL,分数TEST,少数TEST,備考,予備 2004/12/25,192.168.0.1,ken3.org,1/20,1.25,備考の文字,1-2-3 2005-01-08,192.168.0.25,/asp/,1/3,5.10,日付の形式を少し変えた 2005/01/19 17:30,192.168.0.17,vba,2/7,5,時刻を付けた,04-05-06 まだまだ、テストデータとして、足りないけど、↑手抜きでこんな感じ。 ※カンマと小数点が見難いが・・・ ADOでCSVの接続は、 'SQLのテーブル名には、ファイル名を指定します。 strSQL = "select * from test101.csv" '接続情報の作成 ドライバーの指定と、DBQには、パスのみを指定する Con = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & _ Server.MapPath(".") & ";" '↑MapPathに(".")を渡し、カレントディレクトリを渡す こんな感じで普通に接続のテストを行ってみます。 http://www.ken3.org/cgi-bin/test/test101-1.asp ↑実行テスト ↓実行結果 日付 IP URL 分数TEST 少数TEST 備考 予備 2004/12/25 192.168 ken3.org 1/20 1.25 備考の文字 2001/02/03 2005/01/08 192.168 /asp/ 1/3 5.1 日付の形式を少し変えた 2005/01/19 17:30:00 192.168 vba 2/7 5 時刻を付けた 2004/05/06 ホントだ、出たよ、不具合が・・・ ※だから読者が質問してきたんだろ読者が・・・/* * ３．テストデータから不具合のパターンを分析してみる */
結果が出たので分析してみます。 192.168.0.1 や 192.168.0.25 、 192.168.0.17 が、 192.168 と、なってしまった。 ここは、純粋に文字列で扱ってほしかったのに・・・ 次に、小数点付きの数値をテストしてみると、 1.25 5.10 5 は、5.1となっているので、数値扱いされている。 頭に戻って日付、 2004/12/25 と 2005-01-08 どちらも、日付と認識されるのか、 2005-01-08が2005/01/08となった。 興味があるのが、年が無くて、月日だったら？ 1/20は、変換して１月２０日と見るのか、文字列の1/20と見るのか気になった。 1/20 1/3 2/7 は、大丈夫みたいだ。 とすると、あとは、2000年問題で流行った、yy-mm-dd形式は？ そこで、 1-2-3 と 04-05-06をテストしてみた。 なんと、2001/02/03と2004/05/06と自動変換されていた。 原因を探っていくと、 CSVファイルには、フィールドの型情報が無いので、 先頭から数行のデータを使って、フィールドの型を判断していて、 文字列でそのまま扱ってほしいのに、 これは日付、これは小数点付き数値などと自動で決めてしまう。 チラッと頭の毛が金髪とみただけで、心の中まで見ないで、 不良と決め付けている大人みたい・・・ なんて冴えない話は置いといて、どうしましょう？？？ 参考資料： http://support.microsoft.com/default.aspx?scid=kb;ja;278973 >データ型 >Excel テーブルは、従来のデータベースとは異なり、列に直接データ型を指定 >する方法がありません。代わりに、列の中の一定数の行が OLE DB プロバイダ >によりスキャンされ、そのフィールドのデータ型が推測されます。スキャンさ >れる行数は、デフォルトでは 8 行ですが、接続文字列の拡張プロパティで、 >MAXSCANROWS 設定に 1 ～ 16 の値を指定することでスキャンされる行数を >変更できます。 CSVの資料じゃないけど、８行スキャンして、データ型を決めているのかなぁ。/* * ４．原因がわかったら対策を立てるのでは？ */
原因が頭だけ見た自動判断なら、頭に文字列のデータを置けばいいんじゃない？ チラッと頭の毛が金髪とみただけで不良少年と決め付ける大人がいるんなら、 先頭が１人でも黒髪だったら、鼻ピアスヘソピアスもＯＫで通るのかな。 初めだけ猫かぶるじゃないけど、先頭行を完璧な文字列として扱うように細工すれば？ いいんじゃないの？ 先頭行に礼儀正しいダミー君、ダミーのデータを送り込むとか？ えっ、そんなことするの・・・セコ。。。/* * ５．ダミーファイルを先頭で読むか(UNION ALL でつなげるか・・・) */
もし、可能なら、フィールドがひと目で見てわかる、 優等生軍団じゃなくて、ダミーのファイルを１つ作成します。 ファイル名 dummy101.csvなどで１行型決めをする http://www.ken3.org/cgi-bin/test/dummy101.csv 日付,IP,URL,分数TEST,少数TEST,備考,予備 2000/01/01,ABCDEFG,文字だよ,ここも文字,9.999,備考は文字,予備も文字列 上記を作成して、 select * from dummy101.csv UNION ALL select * from test101.csv とSQLを発行してみた↓。 http://www.ken3.org/cgi-bin/test/test101-2.asp が、 結果は、変わらなかった(笑) 修正構想は、崩れ去った・・・/* * ６. それならと思い、ADOでCSVに接続して .GetString を テストしてみた */
他に何か無いかなぁと探ってみて、 .GetString なんてメソッドをADOで発見した。 .GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr) 戻り値 Recordset をバリアント型 (Variant) 変数 (BSTR) の文字列として返します。 単語だけ見ると Get String これって文字列だよな？ .GetString(adClipString, 行数, 区切り文字, 行間の区切り, NULL時の代替) を指定できるので、 .GetString(adClipString, , vbTab, vbCrLf, vbNullString) と、 タブ区切り、CRLFで改行された文字列を指定してみた 期待に胸を膨らませ、テストしてみた。 http://www.ken3.org/cgi-bin/test/test101-3.asp ↑.GetStringをテストしてみた Response.Write "<PRE>" strDATA = rs.GetString(adClipString, , vbTab, vbCrLf, vbNullString) Response.Write strDATA '内容を表示する Response.Write "</PRE>" とするが、期待通りの結果は得られず。 ※データがタブと改行で区切られたので、 別の処理では何か使えるかもしれないメソッドですが、 期待した無変換では、無かったです。/* * ７．終わりの挨拶 ＜／ＨＴＭＬ＞ */
今回は、 ADOでCSV接続したときに、 192.168.0.1 が、 192.168 と なってしまう件を調査したのですが、解決しませんでした。 やはり、テキストファイルで開いて、 自分でSplit関数で分解するしか無いのかなぁ・・・ 奥が深いですよね。 今回も解決していないのですが、 何かの参考となれば幸いです。 ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
| No.102 | 2005/01/23 ADO CSV接続 schema.iniを使い型を設定してみた |
[ページTOPへ戻る] |
＜ADO CSV接続 schema.iniを使い型を設定してみた＞
こんにちは、三流プログラマーのＫｅｎ３です。 今回は、読者２人からヒントをもらった、 schema.iniを使い ADOのCSV接続の自動で決まってしまうデータの型を指定してみました。 いつもの三流的なアプローチなので、実際はアレンジして使ってください。 ※一部でも参考になればいいんだけど・・・/* * １．今回のキッカケ */
No.080 Microsoft Text Driver (*.txt; *.csv) で接続してみた http://www.ken3.org/asp/backno/asp080.html から >質問なのですがｃｓｖファイルを接続し、データを吸い上げたときに >IPアドレスのように111.222.333.444といった物が111.222333444とな >って上がってきてしまいました。吸い上げた時点で既にそのような形 >式になっていましたのでどうした物かと困っています。掲示板のログ と、質問をもらった。 No.101 ADO CSV接続 勝手に型を決めんなよ・・・ http://www.ken3.org/asp/backno/asp101.html で、いろいろと試行錯誤するも、できなかった・・・ 読者さんから、 schema.ini を使ってみては、調べてみては、 とヒントをもらったのでチャレンジしてみました。/* * ２．schema.iniって何？ */
schema.iniって何？ まぁ、拡張子がiniなので、何かの設定ファイルなんだろうけど・・・ 読者から教わった [AC97]VBAから Schema.ini ファイルを作成する方法 http://support.microsoft.com/default.aspx?scid=kb;ja;155512&Product=accJPN を見ると、 テキストファイルのデータ型を ファイル単位、フィールド単位で指定するファイルみたいです。 schema.ini は、 [ファイル名] ColNameHeader=True MaxScanRows=0 Coln=ColumnName type [Width #] と言った構成でTXTやCSVと同じディレクトリに保存しておくみたいです。 見つけた参考資料(下記MSの英語だけど、もしかしたら日本語もあるかも) Schema.ini File (Text File Driver) http://msdn.microsoft.com/library/en-us/odbc/htm/odbcjetschema_ini_file.asp こいつを指定すれば、CSVファイルで192.168.0.1も文字として扱ってもらえそうですね/* * ３．実際にschema.iniを作成してみた */
テストで、schema.iniを作成してみたいと思います。 違いをテストしてみたかったので、 test101.csvと中身が一緒のtest102.csvをまず作成しました。 http://www.ken3.org/cgi-bin/test/test102.csv 日付,IP,URL,分数TEST,少数TEST,備考,予備 2004/12/25,192.168.0.1,ken3.org,1/20,1.25,備考の文字,1-2-3 2005-01-08,192.168.0.25,/asp/,1/3,5.10,日付の形式を少し変えた 2005/01/19 17:30,192.168.0.17,vba,2/7,5,時刻を付けた,04-05-06 ↑このファイルのフィールドを全てテキストのサイズ９９と指定してみます。 作成したschema.iniファイルは下記のような感じです http://www.ken3.org/cgi-bin/test/schema.ini [test102.csv] ColNameHeader=True Format=CSVDelimited Col1=日付 Char Width 99 Col2=IP Char Width 99 Col3=URL Char Width 99 Col4=分数TEST Char Width 99 Col5=少数TEST Char Width 99 Col6=備考 Char Width 99 Col7=予備 Char Width 99 ↑オイオイ、ホントにこんなんでＯＫなの？ [test102.csv]まず、ファイル名のtest102.csvを[]で囲ってセクション指定かな。 次に、 ColNameHeader=True で、ヘッダーありだよと教えてあげて、 さらに Format=CSVDelimited で、フォーマットはCSVだよ そしたら、あとは、項目単位でデータの型を指定してあげる。 ＣｏｌＮって感じで、n番目を作っていきます。 Col1=日付 Char Width 99 Col2=IP Char Width 99 ・ ・ ・ Col7=予備 Char Width 99 ここでは、全てテキストの９９としたけど、 CSVは固定サイズじゃないのでサイズは不要かも。 ※固定長テキストの時など、いろいろと調査をしてみたいですね/* * ４．実行テスト */
schema.iniファイルの指定有りと無しを区別するために、 test101.csv(設定なし) と test102.csv(schema.ini内で設定有り) に接続してテストしてみました。 http://www.ken3.org/cgi-bin/test/test102-1.asp ↑実行テストとソースファイル 結果は、192.168.0.1を文字列として認識、 04-05-06も日付ではなく文字列として無事に認識されました。/* * ５．終わりの挨拶 ＜／ＨＴＭＬ＞ */
今回は、 ADOでCSV接続したときに、 192.168.0.1 が、 192.168 となってしまう件を 読者から教わったschema.iniファイルを作り CSVファイルのフィールド型を指定してみました。 何かの参考となれば幸いです。 ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
| No.103 | 2005/01/30 ADOでExcel(*.xls)に接続 指定したセルを更新する |
[ページTOPへ戻る] |
＜ADOでExcel(*.xls)に接続 指定したセルを更新する＞
こんにちは、三流プログラマーのＫｅｎ３です。 今回は、 ADOでExcel(*.xls)に接続して、 指定したセルを更新してみました。 いつもの三流的なアプローチなので、実際はアレンジして使ってください。 ※一部でも参考になればいいんだけど・・・/* * １．今回のキッカケ */
ADO Excel接続関係のリンク情報 http://www.ken3.org/cgi-bin/group/asp_ado_excel.asp から >質問ですが、ＡＳＰからＡＤＯを使ってエクセルに接続し >その後セルを指定してデータをエクセルにつっこみたくて >色々調べたんですがさっぱりわかりませんでした。 >セルを指定するにはどうしたらいいんでしょうか～～？ と、質問をもらった。/* * ２．不親切な回答 */
不親切な できるだろう 的 な、手抜きの回答は、 Excel(*.xls)グラフ付雛形にADOで接続しデータ更新 http://www.ken3.org/cgi-bin/test/test057-1.asp ↑ASPからADOを使用してExcel(*.xls)と接続して、.Updateでレコードを更新する。 '接続文字列作成、test057.xlsを指定する strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("test057.xls") & ";" & _ "Extended Properties=Excel 8.0" とかで接続して、 rs.Open "Select * from [DATA$A1:B9]", db, adOpenStatic, adLockOptimistic と、指定範囲を開き、 rs.MoveFirst '先頭へ(A2:B2の行) Response.Write "ループで配列からフィールド（セル）へセット<br>" 'ループ For n = 1 To 8 '当日＋一週間の７日で８回まわる rs.Fields(0).Value = dHIZUKE(n) rs.Fields(1).Value = PageCNT(n) rs.Update '更新 rs.MoveNext '次のレコードへ（次の行へ） Next と、できるので、 Select * from [DATA$A1:B9] ヘッダー無しの接続指定にして、 こいつを Select * from [DATA$E5:E5] とかにして E5を更新できればいいんだけど、、、 なんて感じの一方通行の想像／妄想の回答かなぁ/* * ３．１つ１つつぶすかな Extended Properties=Excel 8.0;HDR=NO */
先輩や上司、はたまた、偉そうなHP/掲示板の常連から案をもらったら、 自分なりに１つ１つつぶしていくのがハヤイと思う。 不親切な三流君からのヒントは、 ・ヘッダー無しで接続して、 ・Select * from [DATA$E5:E5]、 とか言ってたっけ。ホントにできんのかよ・・・ ADOは、接続の文字列作りがポイントなんだっけ？ ASPからADOを使用してExcel(*.xls)に接続してみた http://www.ken3.org/cgi-bin/test/test053-1.asp 作った(使った)接続文字列は Driver={Microsoft Excel Driver (*.xls)}; DBQ=d:\users\ken3_org\Cgi-bin\test\test053.xls; と、指定して接続はできるのね。 でも、 select * from DATA_RANGE とか、事前にセル範囲の名前を定義(作ってた)よね。 これだと、使いにくいんだけど。 そこで、 ADOでExcel(*.xls)に接続 [Sheet1$]で列名無しテスト http://www.ken3.org/cgi-bin/test/test084-1.asp 事前に名前の定義無しで、 select * from [Sheet1$] とかやったよね。 でもさ、１行目がフィールド名に強制的になってたよね？ えっ、、そうだったっけ？ 自分で作った、 ADOでExcel(*.xls)に接続 .GetRowsで全件読み込んでみた http://www.ken3.org/cgi-bin/test/test085-1.asp を見てみろよ、 １行目が列名として使用されてただろ？ ヤバイなぁ、 とすると、先頭行をヘッダーで使用しない、 そんなオプションが必要(オプションの有無を探さないとね) 似通った処理のCSV接続で、 ADO Extended Properties='text;HDR=NO' でヘッダー無しのCSVと接続 http://www.ken3.org/cgi-bin/test/test090-1.asp ヘッダー(１行目)にフィールド名が無いCSVファイルと接続してみたので、 Extended Properties で、何か(HDR=NO)できそうですね。 ADO xls HDR=NOの３つのキーワードでMSを検索すると、 [SAMPLE] ADO を使用して Excel ブックのデータの読み取り および書き込みを行う方法 (ExcelADO) http://support.microsoft.com/default.aspx?scid=kb;ja;278973 がみつかります。 この中で、 Dim oConn As New ADODB.Connection oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Book1.xls;" & _ "Extended Properties=""Excel 8.0;""" oConn.Close を発見しました。 これを使い、 ADO Excel(*.xls) 接続 Extended Properties=Excel 8.0;HDR=NO で ヘッダー行を無しにする http://www.ken3.org/cgi-bin/test/test103-1.asp テストで、接続文字を '接続文字 Con = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("test084.xls") & ";" & _ "Extended Properties='Excel 8.0;HDR=NO'" として作成、テストしてみました。 ポイントは、 Extended Properties='Excel 8.0;HDR=NO です、 ※無事に先頭行から(左上のA1セル)をデータとして取り込めました。/* * ４．個別につぶし終わったので、つなげたテストプログラムを組む */
接続文字の問題が解決したので、 test103.xls の Sheet1,Sheet2,Sheet3 を選択、 A B C D E 列を選択 1～5行を選択 データを入力 させて、xlsファイルを更新してみたいと思います。 仕事人のサブ関数が受け取りたいのは、 ・Excelファイル名 ・シート名 ・セルのアドレス ・書き込みデータ の４つです。 関数は、引数４つを受け取って仕事をする、そんな関数を作成します。
Sub TestExcelDATASet(strXLSNAME, strSHEETNAME, strRANGE, strDATA)
'４つの引数を受け取り
'指定されたxlsファイルを開き
'指定したシート、セル範囲にデータを書き込む(更新する)
End Sub |
Sub TestExcelDATASet(strXLSNAME, strSHEETNAME, strRANGE, strDATA)
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3
'ADO DB Connection オブジェクトを作成する、英文そのままじゃん
Set db=Server.CreateObject("ADODB.Connection")
'接続文字を作成する
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strXLSNAME & ";" & _
"Extended Properties='Excel 8.0;HDR=NO'"
Response.Write "作った、使った接続文字列は<br>"
Response.Write "<CODE>Con = <b>" & strCon & "</b></CODE><hr>"
'データベース(xls)を開く
db.open strCon
'SQL文 Sheet$範囲 として設定
strSQL = "select * from "
strSQL = strSQL & "[" & strSHEETNAME & "$" & strRANGE & ":" & strRANGE & "]"
Response.Write "作った、使ったSQL文字列は<br>"
Response.Write "<CODE>strSQL = <b>" & strSQL & "</b></CODE><hr>"
'ADO DB Recordset オブジェクトを作成する、英文そのままじゃん
Set rs=Server.CreateObject("ADODB.Recordset")
rs.Open strSQL, db, adOpenStatic, adLockOptimistic 'レコードセットを開く
Response.Write "開いたレコードセットに対して.MoveFirst<br>"
rs.MoveFirst '先頭へ ※イラナイかもね
Response.Write "データ[" & strDATA & "]をセット後.Updateする"
rs.Fields(0).Value = strDATA 'データのセット
rs.Update '更新(データ確定)
Response.Write "rs.Close でレコードセットを閉じる<br>"
rs.Close
Set rs = Nothing 'オブジェクト変数を開放する
Response.Write "db.Close でxlsとの接続を閉じる<br>"
db.Close
Set db = Nothing 'オブジェクト変数も開放する
End Sub |
| No.104 | 2005/03/26 ADO SQL文のNOT EXISTS 句で集合の差を求める |
[ページTOPへ戻る] |
＜ADO SQL文のNOT EXISTS 句で集合の差を求める＞
こんにちは、三流プログラマーのＫｅｎ３です。 今回は、 私も初めて使うSQL文のNOT EXISTS 句です。 いつもの三流的なアプローチなので、実際はアレンジして使ってください。 ※一部でも参考になればいいんだけど・・・/* * １．今回のキッカケ */
>テーブル ＡとＢがあります。Ａ，Ｂともに 伝票№、伝票行、商品名で構成 >されています。 > >質問ですが、Ｂの抽出条件は、Ａの№と同じＢの伝票№で、Ａの伝票行と違う >データだけを抽出したいのですが..。 > と、質問をもらった。/* * ２．テーブルをつなげるのはUnionだったけど・・・ */
同じ構造のテーブルかぁ、よく目にする耳にするのは、 Unionって言葉かな、でも、これは、つなげるだけだしね。 SQL UNIONでつなげ複数テーブルを読み込む http://www.ken3.org/cgi-bin/test/test100-2.asp で、３つのテーブルをつなげてたっけ。 UNION と UNION ALL が微妙に違ってたり SQL UNION ALLでつなげ複数テーブルを読み込む http://www.ken3.org/cgi-bin/test/test100-3.asp で、ALLでつなげると同じデータ(重複)を弾かなかったり。 だから、今回はつなげて一緒にする話じゃないだろ、 横道にそれてスミマセン、いつものクセで・・・/* * ３．UNION 真逆の差を求めたいんです Minus発見 */
やりたいことは、UNIONの逆、差を求めたいんですよね。 そんな便利な集合演算できるのか？と思い、 SQL SELECT 差 上記の３つのキーワードで検索すると、 Minus なんて素敵な女性じゃなかったキーワードを見つける・・・ さっそくテストしてみますか。 テストデータは手抜きでNo100で使ってた、 DB:db100.mdb Select * From T_2002年売上 商品CD 売上金額 A001 100 B002 50 A001 20 CCCC 1200 Select * From T_2003年売上 商品CD 売上金額 A001 100 CCCC 500 の２つを使って、 T_2003年売上 テーブル から T_2002年売上 テーブルにあるA001 100を取り除いた結果を求めたいと思います。 得意になって、 Minus 演算子でつなげ、複数テーブルをSelectしてみました Select * From T_2003年売上 Minus Select * From T_2002年売上 ↑こんなSQLを書くと・・・ Microsoft JET Database Engine エラー '80040e14' FROM 句の構文エラーです。 /cgi-bin/test/test104-1.asp, 行 31 あらら、、、何でだろ。。。/* * ４．MinusはMS SQLServerに無し、EXISTS 句と NOT EXISTS 句を使え？ */
アタックに失敗して、気落ちしながら他を当たってみると、 TechNet Online -Oracle から MS SQL Server 7.0 へのデータベースの移行 http://www.microsoft.com/japan/technet/prodtechnol/sql/deploy/upgrdmigrate/oracle.asp を読むと >SQL Server では、 >INTERSECT 集合演算子および MINUS 集合演算子をサポートしていませんが、 >EXISTS 句と NOT EXISTS 句を使うと同じ結果が得られます。 あらら、攻めどころが違ったのね。 なんか変なSQL文だけど、 http://www.ken3.org/cgi-bin/test/test104-1.asp で、 Select * From T_2003年売上 T2003" Where NOT EXISTS (Select * From T_2002年売上 T2002 Where T2002.商品CD = T2003.商品CD AND T2002.売上金額 = T2003.売上金額) と発行してみました。 結果は、 商品CD 売上金額 CCCC 500 なんとか、A001 100を取り除いて表示されてるけど。。。 今度は基準のテーブルを逆にして、 Select * From T_2002年売上 T2002" Where NOT EXISTS (Select * From T_2003年売上 T2003 Where T2002.商品CD = T2003.商品CD AND T2002.売上金額 = T2003.売上金額) と、基準を2002年にすると、 http://www.ken3.org/cgi-bin/test/test104-2.asp 商品CD 売上金額 B002 50 A001 20 CCCC 1200 ↑みたいになり、 共通する、A001 100を取り除いて集合を作ることができます。 ^^^^^^^^^^^^^^^^^^ テストデータ元 Select * From T_2002年売上 商品CD 売上金額 A001 100 B002 50 A001 20 CCCC 1200 Select * From T_2003年売上 商品CD 売上金額 A001 100 CCCC 500/* * ５．終わりの挨拶 ＜／ＨＴＭＬ＞ */
今回は、 集合(テーブル)の差を求めたくて、 Minusを発見するが、 MS SQLServerに無し、EXISTS 句と NOT EXISTS 句を使え？ ってことで、 Select * From T_2003年売上 T2003" Where NOT EXISTS (Select * From T_2002年売上 T2002 Where T2002.商品CD = T2003.商品CD AND T2002.売上金額 = T2003.売上金額) ↑こんなSQLを作ってみました。 質問は、 --- >テーブル ＡとＢがあります。Ａ，Ｂともに 伝票№、伝票行、商品名で構成 >されています。 > >質問ですが、Ｂの抽出条件は、Ａの№と同じＢの伝票№で、Ａの伝票行と違う >データだけを抽出したいのですが..。 --- なので、Bテーブルを基準にAと同じデータを取り除きたいので、 Select * From Ｂテーブル BB" Where NOT EXISTS (Select * From Ａテーブル AA Where AA.伝票№ = BB.商品CD AND AA.伝票行 = BB.伝票行 ) で、いけるかな？ ○○したあとのタバコはうまいとよく聞くが(オイオイ,タバコ吸わないでしょアンタ） 終わったなぁと一息ついてると いつもの読者の心の声(クレーム)が聞こえてきました、 そんなEXISTS 句と NOT EXISTS 句使わなくても、 オレ様が頭に浮かんだキーワード１つ教えてやろうか！！！ えっ、どんなキーワード？ LEFT JOIN だよJOIN ^^^^^^^^^^ なんですか？それ？ Access好きなのに知らないのアンタ。 はい、読者さん達ほどSQLに詳しくないので・・・ 次回は、LEFT JOIN かな？ 今回も、中途半端ですが、 何かの参考となれば幸いです。 ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。 PS.正直今回、メルマガ書きながらサンプル作ってました。 Minusを発見して楽勝と思ったのですが、失敗失敗。。。 OracleファンはMinus使っているのかなぁ？それともLEFT JOIN？
三流解説を読んでいただき、どうもです。ここから下は、三流君宛のメッセージ送信や 三流君のホームページの紹介・案内です
目的の情報が見つかったか？少々心配しつつ、、、※質問や感想は、気軽に送ってくださいね。
まぁ、基本はデータの受け取りかなぁ。
・[Form等を使用したデータのやり取り]・・・ASPと言っても、HTMLの入力フォームからデータを受け取ります。POSTやGETでやりとりを押さえますか。
次は、データの入出力 で ADOを使った(ADOで接続) と SQLの解説を少々
・[ADOでMdbファイルを使う]・・・MDBと接続して、簡単な追加・更新・削除を行った。
・[ADOでExcelと接続してみた]・・・.xlsと接続してSQLを使ってみた。
・[ADOでCSVと接続してみた]・・・.CSV テキストを読み出した。※更新・削除はできません
DBが使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。１行読み込みなどを軽く
VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。
ニガテな環境設定系など
[Win2003 Server に IIS を Setup]・・・ポイントの無い、ほぼ一本道解説だけど。
[IIS 仮想ディレクトの作成とASP動作TEST]・・・Web拡張でASPを有効にしただけです。
Blog:[三流君の作業日記]/ [サンプルコードのゴミ箱]/ 広告-[通販人気商品の足跡]
質問や要望など連絡方法でお互い確認が取りやすく、便利なのが掲示板なのですが、私の対応のまずさから不定期で荒れてしまい、掲示板は現在封鎖中です。(反省しなきゃ)
感想や質問・要望・苦情など 三流君へメッセージを送る。
時間的余裕のある要望・質問・苦情の場合は、下記のフォームからメッセージを送ることができます。