ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Posts Tagged ‘Access

9月21日に延期します – 2019年8月17日(土) – 第4回神戸SQL Serverラボ – 神戸SQL Server User Group (7月20日の予定を日程変更しました)

leave a comment »

※2019年9月17日更新
※2019年8月15日更新

主催者所用のため、8月17日は中止し、9月21日に延期いたします。参加ご予定の方々、まことに申し訳ありません。

※2019年7月5日更新

2019年4月から毎月定期開催で「神戸SQL Serverラボ」と題したSQL Serverの勉強会を始めました。場所は神戸三ノ宮駅から徒歩約5分の便利な場所にある、神戸青少年会館をお借りし、毎回テーマを決めて、初心者がSQL Serverデータベースの運用ができることを目指します。また経験者の方にも、それぞれお持ちのノウハウを交流できる場になれば幸いです。

第2回からネットワーク環境で SQL Server を利用できるようにしました。LAN ポートで有線接続できるか、Wi-Fi 機能を持った Windows PC をお持ちいただければ SQL Server をインストールしなくとも管理ツール SSMS だけ設定すれば実習ができます。用意する SQL Server のバージョンは SQL Server 2014 と SQL Server 2017 です。

場所:神戸青少年会館(神戸市勤労会館の5階6階です)
神戸市中央区雲井通5丁目1番2号 34.694927, 135.197403
kobeshiseishounenkaikan_map bingmapで表示

神戸市青少年会館のアクセスページ
日時:毎月第3土曜日 18:00~19:30

第4回 2019年9月21日(土)18:00~19:30
「関数によるデータ処理、ユーザー定義関数の作成と利用」
SQL Server には様々な関数が用意されています。今回はこの組み込み関数をビューやクエリで利用する方法と、ユーザーが独自の関数を作るユーザー定義関数の作り方を解説します。関数を効果的に利用することで、ビューやクエリをわかりやすくでき、システムのメンテナンス性も良くなります。
場所:神戸青少年会館 サークル4号室(神戸市勤労会館の6階です)

申し込み方法:電子メールまたはconnpassで
電子メール:kobesqlserverlabo@matsumotoyoshio.net
電子メールのタイトル:第4回20190921神戸SQL Serverラボに参加します
電子メールの本文:お名前、所属をお書きください。
connpassでの申し込み:https://connpass.com/event/136713/
運営費:500円

ご自分の Windows PCをお持ちください。

(定期開催の予定)

第1回 2019年4月27日(土)18:00~19:30(終了しました)
「SQL Serverのインストールと管理ツールManagement Studioの基本操作」

第2回 2019年5月18日(土)18:00~19:30(終了しました)
「テーブル作成、データ型、主キー、テーブル作成とSQL文」

第3回 2019年6月15日(土)18:00~19:30(終了しました)
「ビューによるデータ操作、射影、選択、結合」

2019年7月20日(土)、8月17日は都合により中止します。

第4回 2019年9月21日(土)18:00~19:30(今回のご案内です)
「関数によるデータ処理、ユーザー定義関数の作成と利用」

第5回 2019年10月19日(土)18:00~19:30
「トリガによるデータ処理の自動化」

第6回 2019年11月16日(土)18:00~19:30
「ストアドプロシージャの作成と利用」

第7回 2019年12月21日(土)18:00~19:30
「CHECK制約と参照整合性制約の設定」

※第8回以降の予定は決定次第ここにご案内します。

2019年8月15日

松本 吉生(まつもとよしお)

Microsoft MVP Data Platform
京都に生まれ、神戸で幼少期を過ごす。大学で応用化学を学んだのち、理科教諭として高等学校に勤務する。教育の情報化が進む中で校内ネットワークの構築運用に従事し、兵庫県立明石高等学校で文部科学省の「光ファイバー網による学校ネットワーク活用方法研究開発事業」に携わる。兵庫県立西宮香風高等学校では多部制単位制の複雑な教育システムを管理する学籍管理データベースシステムをSQL ServerとInfoPath、AccessなどのOfficeソフトウエアによるOBA開発で構築・運用する。2015年から2017年まで兵庫県立神戸工業高等学校でC#プログラミング、IoTなどのコンピュータ教育を行い、現在は兵庫県立神戸甲北高等学校に勤務する。2004年からマイクロソフトMVP(Microsoft Most Valuable Professional)を受賞し、現在16回目の連続受賞。2016年にマイクロソフト認定教育者(Microsoft Innovative Educator Experts : MIEE)を受賞し、現在4回目の連続受賞。

2019年6月15日(土) – 第3回神戸SQL Serverラボ – 神戸SQL Server User Group

leave a comment »

2019年4月から毎月定期開催で「神戸SQL Serverラボ」と題したSQL Serverの勉強会を始めました。場所は神戸三ノ宮駅から徒歩約5分の便利な場所にある、神戸青少年会館をお借りし、毎回テーマを決めて、初心者がSQL Serverデータベースの運用ができることを目指します。また経験者の方にも、それぞれお持ちのノウハウを交流できる場になれば幸いです。

第2回からネットワーク環境で SQL Server を利用できるようにしました。LAN ポートで有線接続できるか、WiFi 機能を持った PC をお持ちいただければ SQL Server をインストールしなくとも管理ツール SSMS だけ設定すれば実習ができます。用意する SQL Server のバージョンは SQL Server 2014 と SQL Server 2017 です。

場所:神戸青少年会館(神戸市勤労会館の5階6階です)
神戸市中央区雲井通5丁目1番2号 34.694927, 135.197403
kobeshiseishounenkaikan_map bingmapで表示
神戸市青少年会館のアクセスページ
日時:毎月第3土曜日 18:00~19:30

第3回 2019年6月15日(土)18:00~19:30
「ビューによるデータ操作、射影、選択、結合」
ビューはテーブルから条件にあったデータを取り出したり、複数のテーブルのデータを関連付けて表示させるものです。必要なデータを必要に応じてわかりやすく表示するもので、データベースの最も特徴的な機能です。マイクロソフトの SQL Server では、SSMSを使ってマウス操作で視覚的にビューを作成することができます。もちろん SQL 文を使って作成することもでき、これらの方法を体験的に身に着けます。
場所:神戸青少年会館 サークル4号室(神戸市勤労会館の6階です)

申し込み方法:電子メールまたはconnpassで
電子メール:kobesqlserverlabo@matsumotoyoshio.net
電子メールのタイトル:第3回20190615神戸SQL Serverラボに参加します
電子メールの本文:お名前、所属をお書きください。
connpassでの申し込み:https://connpass.com/event/134449/
運営費:500円

(定期開催の予定)

第1回 2019年4月27日(土)18:00~19:30(終了しました)
「SQL Serverのインストールと管理ツールManagement Studioの基本操作」

第2回 2019年5月18日(土)18:00~19:30(終了しました)
「テーブル作成、データ型、主キー、テーブル作成とSQL文」

第3回 2019年6月15日(土)18:00~19:30
「ビューによるデータ操作、射影、選択、結合」

第4回 2019年7月20日(土)18:00~19:30
「関数によるデータ処理、ユーザー定義関数の作成と利用」

第5回 2019年8月17日(土)18:00~19:30
「トリガによるデータ処理の自動化」

第6回 2019年9月21日(土)18:00~19:30
「ストアドプロシージャの作成と利用」

※第7回以降の予定は決定次第ここにご案内します。

2019年6月6日

松本 吉生(まつもとよしお)

Microsoft MVP Data Platform
京都に生まれ、神戸で幼少期を過ごす。大学で応用化学を学んだのち、理科教諭として高等学校に勤務する。教育の情報化が進む中で校内ネットワークの構築運用に従事し、兵庫県立明石高等学校で文部科学省の「光ファイバー網による学校ネットワーク活用方法研究開発事業」に携わる。兵庫県立西宮香風高等学校では多部制単位制の複雑な教育システムを管理する学籍管理データベースシステムをSQL ServerとInfoPath、AccessなどのOfficeソフトウエアによるOBA開発で構築・運用する。2015年から2017年まで兵庫県立神戸工業高等学校でC#プログラミング、IoTなどのコンピュータ教育を行い、現在は兵庫県立神戸甲北高等学校に勤務する。2004年からマイクロソフトMVP(Microsoft Most Valuable Professional)を受賞し、現在15回目の連続受賞。2016年にマイクロソフト認定教育者(Microsoft Innovative Educator Experts : MIEE)を受賞し、現在4回目の連続受賞。

2019年5月18日(土) – 第2回神戸SQL Serverラボ – 神戸SQL Server User Group

leave a comment »

2019年4月から毎月定期開催で「神戸SQL Serverラボ」と題したSQL Serverの勉強会を始めました。場所は神戸三ノ宮駅から徒歩約5分の便利な場所にある、神戸青少年会館をお借りし、毎回テーマを決めて、初心者がSQL Serverデータベースの運用ができることを目指します。また経験者の方にも、それぞれお持ちのノウハウを交流できる場になれば幸いです。

<追記>
第2回からネットワーク環境で SQL Server を利用できるようにします。LAN ポートで有線接続できるか、WiFi 機能を持った PC をお持ちいただければ SQL Server をインストールしなくとも管理ツール SSMS だけ設定すれば実習ができます。用意する SQL Server のバージョンは SQL Server 2014 と SQL Server 2017 です。

場所:神戸青少年会館(神戸市勤労会館の5階6階です)
神戸市中央区雲井通5丁目1番2号 34.694927, 135.197403
kobeshiseishounenkaikan_map bingmapで表示
神戸市青少年会館のアクセスページ
日時:毎月第3土曜日 18:00~19:30

第2回 2019年5月18日(土)18:00~19:30
「テーブル作成、データ型、主キー、テーブル作成とSQL文」
データベースの作成とテーブル作成について実習します。実習の中でデータを一意に管理するテーブルの主キーについてや、Accessの「オートナンバー型」のようにデータに自動的に連番を与える方法、テーブル作成を SQL 文で行う方法などを体験的に身に着けます。
場所:神戸青少年会館 サークル4号室(神戸市勤労会館の6階です)

申し込み方法:電子メールまたはconnpassで
電子メール:kobesqlserverlabo@matsumotoyoshio.net
電子メールのタイトル:第2回20190518神戸SQL Serverラボに参加します
電子メールの本文:お名前、所属をお書きください。
connpassでの申し込み:https://connpass.com/event/130009/
運営費:500円

(定期開催の予定)

第1回 2019年4月27日(土)18:00~19:30(終了しました)
「SQL Serverのインストールと管理ツールManagement Studioの基本操作」

第2回 2019年5月18日(土)18:00~19:30
「テーブル作成、データ型、主キー、テーブル作成とSQL文」

第3回 2019年6月15日(土)18:00~19:30
「ビューによるデータ操作、射影、選択、結合」

第4回 2019年7月20日(土)18:00~19:30
「関数によるデータ処理、ユーザー定義関数の作成と利用」

第5回 2019年8月17日(土)18:00~19:30
「トリガによるデータ処理の自動化」

第6回 2019年9月21日(土)18:00~19:30
「ストアドプロシージャの作成と利用」

※第7回以降の予定は決定次第ここにご案内します。

2019年5月1日

松本 吉生(まつもとよしお)

Microsoft MVP Data Platform
京都に生まれ、神戸で幼少期を過ごす。大学で応用化学を学んだのち、理科教諭として高等学校に勤務する。教育の情報化が進む中で校内ネットワークの構築運用に従事し、兵庫県立明石高等学校で文部科学省の「光ファイバー網による学校ネットワーク活用方法研究開発事業」に携わる。兵庫県立西宮香風高等学校では多部制単位制の複雑な教育システムを管理する学籍管理データベースシステムをSQL ServerとInfoPath、AccessなどのOfficeソフトウエアによるOBA開発で構築・運用する。2015年から2017年まで兵庫県立神戸工業高等学校でC#プログラミング、IoTなどのコンピュータ教育を行い、現在は兵庫県立神戸甲北高等学校に勤務する。2004年からマイクロソフトMVP(Microsoft Most Valuable Professional)を受賞し、現在15回目の連続受賞。2016年にマイクロソフト認定教育者(Microsoft Innovative Educator Experts : MIEE)を受賞し、現在4回目の連続受賞。

Access で年賀状のあて名印刷をするコツ

leave a comment »

この時期、1年間使わなかった年賀状印刷ソフトを久しぶりに起動し、印刷に四苦八苦というところも多いだろう。ところが年に1回しかつかわないソフトの使い方は、どうしても忘れてしまいがちになるのではないか。また今年中にプリンタや周辺機器を買い替えてアプリケーションが対応しなくなったり、もしかしたら昨今のことだから、家に何台もあるパソコンのうち、どれにソフトをインストールしてあったか忘れてしまっている、といったこともあるかもしれない。またそれが調子が悪くて捨ててしまっていた、といったこともあるだろう。

年賀状を印刷するには、いわゆる「宛名印刷ソフト」というものを使う人が多いだろうが、宛名印刷ほど Access に得意な分野はない。そこで、住所データをアクセスにインポートし、宛名印刷をするコツを紹介する。ここでは Microsoft Office Access 2007 で説明するが、他のバージョンでもほぼ同じだ。

まず住所データを用意する。もし宛名印刷ソフトを使っているならデータが取り出せるはずだ。宛名印刷ソフトからデータをエクスポートすると、たとえばこのようなデータになる。これはCSV 形式のデータで、ファイルのフォーマットはテキストファイルと同じ扱いになっている。宛名印刷ソフトによってはエクセル形式でデータを取り出せるものもあるだろうし、データがなければエクセルで作ってもいいだろう。

Access年賀状_001 

<fig.1 : ある宛名印刷ソフトからエクスポートした住所データ>

これを Access にインポートする。まず Access を起動したら「空のデータベース」を作成する。作成する場所は「ドキュメント」で、ファイル名は「年賀状印刷」とでもしておこう。

Access年賀状_002_mid_640

<fig.2 : Access を起動して「空のデータベース」を作成する>

Access年賀状_003_mid_640

<fig.3 : Access のファイル名を「年賀状印刷」として作成する>

Access が起動し、テーブルのデザイン画面になったら、テーブルは作らなくてよいので保存しないで閉じる。次にメニューから「外部データ」のタブを開き、「テキストファイルのインポート」をクリックする。もし用意した住所データがエクセルの場合は「Excel ワークシートのインポート」をクリックする。

Access年賀状_004_mid_640

<fig.4 : テキストファイルのインポートをする>

テキストファイルのインポートを実行すると、インポートするデータを指定するように求められるので、CSV のテキストファイルを保存した「ドキュメント」を指定して、ファイルを選択する。

Access年賀状_005_mid_640

<fig.5 : インポートするテキストファイルを指定する>

Access年賀状_006_mid_640

<Fig.6 : インポートするテキストファイルを保存した「ドキュメント」フォルダからファイル名を選択する>

ファイル名を指定したら「テキストインポートウィザード」がはじまる。このウィザードでは、データの形式を自動で読み取り、適切な形式に判断してくれるので、基本的には「次へ」の連続でうまくいく。ただ、郵便番号のフィールドは、場合によっては数値と判断される場合があるので、テキストになっていることを確認しよう。もし数値と判断されてしまったら、たとえば番号の最初の数字が「0」の場合、その「0」が失われて桁が小さくなるし、後でやる「郵便番号の前と後ろの切り離し」がやりにくくなる。

Access年賀状_007_mid_640

<fig.7 : テキストインポートウィザードでデータ区切りを指定する>

Access年賀状_008_mid_640

<fig.8 : データインポートウィザードでテキストの保存形態を確認する>

Access年賀状_009_mid_640

<fig.9 : データインポートウィザードでデータの型を確認する>

データの型、特に郵便番号のフィールドが「数値」ではなく「テキスト」になっていることを確認し、次へすすむと主キーの設定になる。ここでは「主キーを自動的に設定する」にして連番を自動で割り当てることとする。

Access年賀状_010_mid_640

<fig.10 : テキストインポートウィザードで主キーを自動的に設定する>

インポートウィザードが終了すると、インポートしたデータのテーブル名を指定する。ここでは自動的に割り当てられた「住所エクスポート」のままでいいだろう。

Access年賀状_011_mid_640

<fig.11 : テキストインポートウィザードでインポート先のテーブル名を指定する>

インポートウィザードを終了すると、このインポート操作を保存するかを聞かれる。もし何度も同じ操作をする可能性があるなら保存をするが、このような場合は保存しなくていいだろう。

Access年賀状_012_mid_640

<fig.12 : テキストインポートウイザードでインポート操作の保存ができる>

データのインポートができた。このままではフィールド名がわかりにくいので、テーブルをデザインしてフィールド名を「宛名」、「郵便番号」、「住所」に変えよう。

Access年賀状_013_mid_640

<fig.13 : 住所データがインポートできた>

Access年賀状_014_mid_640_480

<fig.14 : テーブルをデザインビューにしてフィールド名を変更する>

Access年賀状_015_mid_640

<fig.15 : テーブルをデザインビューにしてフィールドを「宛名」、「郵便番号」、「住所」に変更する>

Access年賀状_016_mid_640

<fig.16 : 「住所エクスポート」テーブルのフィールド名を変更した>

次に、このテーブルをもとにして、はがきの宛名印刷に使いやすいデータをクエリで加工する。「作成」タブの「クエリデザイン」で新しいクエリを作成しよう。クエリを作成すると、どのテーブルのデータからデータを取得するかを選択する。ここでは「郵便番号住所」のデータしかないので、それだけが選択できる。

Access年賀状_017_mid_640

<fig.17 : 「作成」タブの「クエリデザイン」で新しいクエリを作成する>

Access年賀状_018_mid_640

<fig.18 : クエリデザインでテーブルを選択する>

Access年賀状_019_mid_640

<fig.19 : 新しいクエリにテーブルが関連付けられた>

新しいクエリにテーブル「住所エクスポート」が関連付けられたら、表示するフィールドを選んでいく。ここで単にフィールドを「宛名」、「郵便番号」、「住所」と選択してもいいが、後で印刷画面のレポートを作りやすくするために、フィールドを少し加工しよう。

宛名には「様」を付けたいので、全角スペースをひとつ挟んで「様」を表示するようにする。郵便番号は前の 3桁と後ろの 4桁を分離する。住所はそのままでいいだろう。このように表示するには、「フィールド」を次のように記述する。

———————————

宛名様: [宛名] & " " & "様"
郵便番号前: Left([郵便番号],3)
郵便番号後: Right([郵便番号],4)
住所

———————————

まず最初のフィールドに「宛名様」とするのは、フィールドの別名だ。そのあとに「:」をはさんで表示するデータを記述する。「&」は文字列をつなぐ演算子で、” ”は全角のスペースをひとつ挟んでおり、次の「&」で”様”の文字をつないでいる。

次のフィールドの別名は「郵便番号前」とし、「:」ではさんで Left([郵便番号],3) とするのは、「郵便番号」のフィールドのデータを左から、つまり前から3文字だけ表示する関数だ。同様に次のフィールドは別名を「郵便番号後」とし、「:」ではさんで Right([郵便番号],4) としている。これは「郵便番号」のフィールドのデータを右から、つまり後から4文字だけ表示する関数だ。これで郵便番号の前と後ろを別のフィールドで表示し、区別できる。

「住所」フィールドはそのまま使えばよいだろう。

Access年賀状_020_mid_640

<fig.20 : 表示するフィールドの式を記述する>

フィールドの式が記述できたらクエリを保存する。「印刷用クエリ」の名前にしよう。

Access年賀状_021_mid_640

<fig.21 : クエリを保存する>

クエリが保存できたらデータを確認しよう。宛名にはスペースをはさんで「様」が、郵便番号は前 3桁と後 4桁が別フィールドで分離されて表示されているはずだ。

Access年賀状_022_mid_640

<fig.22 : 「印刷用クエリ」のデータを確認する>

次に印刷用のレポートを作成する。「作成」タブから「レポートデザイン」で空白のレポートを作成する。空白のレポートでは自動的に「ページヘッダー」と「ページフッター」ができるが、年賀状印刷にヘッダーやフッターは必要ないので削除する。ヘッダーとフッターを削除するには、

Access年賀状_023_mid_640

<fig.23 : 「作成」タブから「レポート」「レポートデザイン」で新しい空白のレポートを作成する>

Access年賀状_024_mid_640

<fig.24 : レポートの「ページヘッダー」と「ページフッター」を削除する>

次にレポートに対してデータを割りつける。レポートのビューでは、左上の「■」をポイントするとレポート全体を選択したことになるので、その状態で「プロパティシート」から「データ」タブの「レコードソース」をクリックして関連付けるテーブルまたはクエリを選択する。ここでは「印刷用クエリ」を選択する。

Access年賀状_025_mid_640

<fig.25 : レポートに対してレコードソースを割り当てる>

次にレポートにフィールドを追加する。「デザイン」タブから「既存のフィールドの追加」をクリックすると、レポートに関連付けられた「印刷用クエリ」の各フィールドを選択できるので、レポートにドラッグしてフィールドを追加する。

あるいは「デザイン」タブの「コントロール」から「テキストボックス」をレポートにドラッグし、後でフィールドのデータを関連付けてもいいだろう。

フィールドを画面に追加すると、フィールド名のラベルも同時にできるが、必要ないので削除する。

Access年賀状_026_mid_640

<fig.26 : レポートにフィールドを追加する>

Access年賀状_027_mid_640

<fig.27 : 不要なラベルを削除した>

次に、1ページごとに改ページされる設定をする。それには「詳細」のセクションを選択したまま「プロパティシート」で「改ページ」を「カレントセクションの前」に変更する。これで 1レコードずつ改ページされて印刷されるようになる。

Access年賀状_028_mid_640

<fig.28 : 「詳細」セクションを改ページするように設定する>

後は用紙のサイズを「はがき」にし、各フィールドのフォントや文字の大きさ、位置を調節するだけだ。フォントは、郵便番号は「MS Pゴシック」に、住所と宛名は「HG教科書体」がおすすめだ。文字の大きさは「郵便番号前」を 16ポイント、「郵便番号後」を 14ポイント、「住所」を 12ポイント、「宛名様」を 22ポイントとしてみた。

レポートデザインのコツとして、印刷の余白はできるだけ小さくしておくことがある。「郵便番号前」と「郵便番号後」のフィールドは、年賀はがきの郵便番号枠に収まるようにするために「文字配置」を「均等割り付け」にするとよい。また住所のフィールドは、長い住所もあるので大きめにしておき、さらに切手印刷に重ならないようにレイアウトする。「宛名様」のフィールドはハガキの中央に収まるように「文字配置」を「中央」にするとよいだろう。

Access年賀状_029_mid_640

<fig.29 : フィールドの文字フォントと文字の大きさを設定する>

「印刷プレビュー」でおおまかなレイアウトを確かめながらデザインする。だいたいのレイアウトができたら、A4サイズの用紙を 4つに切って葉書大にし、印刷する。印刷した紙を年賀葉書に重ねて位置を確認する。プリンタによって印刷する位置に違いがあるので、ここは何度かやってみながら調節するしかない。

Access年賀状_030_mid_640

<fig.30 : 「印刷プレビュー」でレイアウトを確認する>

IMG_4099_mid_640_480

<fig.31 : 普通の紙に印刷して年賀葉書に重ねて位置を確かめる>

おおむね位置があえば、年賀葉書をプリンタにセットして印刷する。ここでも一度に印刷せず、何枚か試し印刷をし、郵便番号の位置などレイアウトがうまく印刷されるか確認しながらしよう。最終調整だ。あっていなければフィールドサイズを変えたり、フィールドの位置を動かしたりしてベストの位置に修正しよう。

IMG_4098_mid_640_480

<fig.32 : 郵便番号の位置を確認しながら印刷する>

このように説明すると手間がかかるようだが、Access がある程度わかっているなら簡単だ。住所データさえあれば 15分もあれば印刷できる状態まで作れるだろう。

作業手順とコツをまとめると次のようになる。

(1)データをインポートする。このとき郵便番号を数値にせずテキストでインポートする。
(2)データをそのまま使わず、宛名を「様」付きにしたり郵便番号を前と後ろに分けるクエリを作る。
(3)レポートを作ってクエリを割り当てる。ページのヘッダーとフッターを削除し、詳細セクションを改ページする。
(4)フィールドの大きさやフォント、文字の大きさをレイアウトする。文字位置を「均等割り付け」や「中央」で工夫する。
(5)試し印刷を念入りにして、文字の大きさや郵便番号の位置を丁寧にあわせる。

Access は複数ページにわたる帳票印刷が得意だ。これは葉書印刷にも活かせるので、ぜひ Access で年賀状印刷をやって欲しい。そしてこの年賀状印刷のコツは他の帳票印刷でも使えるはずだ。

Written by Yoshio Matsumoto

2014年12月30日 at 1:05 PM

郵便番号データを SQL Server で利用するには(2) – データ変換サービス DTS を使わず Access を利用してデータを SQL Server に取り込む

leave a comment »

SQL Server に Excel や Access、CSV形式などのテキストファイルを取り込むために、データ変換サービス、DTS がある。だがデータ変換サービスはデータ型に厳密であるなど、実際の運用においては使いにくい面もある。そこでデータ変換サービス DTS を使わず、Access を利用してデータを SQL Server に取り込むことを紹介する。Access を使うと、取り込みたいデータを確認しながら作業ができるので、データ型やフィールドサイズが不明の場合でも対応しやすい。

ここで SQL Server に取り込みたいデータが、日本郵便からダウンロードできる全国郵便番号であるとする。郵便番号データのダウンロードについては、前回の blog を参考にしてほしい。

 

ダウンロードした csv データファイルを Access に取り込むため、まず Access を起動して空のデータベースを作成する。ここで使用した Access のバージョンは Microsoft Office Access 2007 だが、違うバージョンでもうまくいくはずだ。Access データベースファイルを作成する場所はローカルコンピュータのドキュメントなど適当な場所でいい。

SQL_DTS_Access_001_mid_640

<Fig.1 : Microsoft Office Access 2007 を起動して空のデータベースを作成する。

空のデータベースを作成したら、リボンメニューの「外部データ」から「テキストファイルのインポート」を実行する。日本郵便からダウンロードしたデータが、csv 形式のテキストファイルだからだ。

SQL_DTS_Access_002_mid_640

<Fig.2 : 「外部データ」メニューの「テキストファイルのインポート」を実行する>

データのインポートはウイザードに従ってすすめる。「データのインポート元とインポート先、またはリンク元とリンク先の選択」でデータをインポートする郵便番号ファイルを選択し、データの保存方法は「現在のデータベースの新しいテーブルにソースデータをインポートする」を選ぶ。

SQL_DTS_Access_003_mid_640

<Fig.3 : データのインポート元とインポート先、またはリンク元とリンク先の選択>

ファイルを指定したら読み込むデータが表示されるので、データの区切り形式を選択する。データの区切りは「区切り記号付き」を選ぶ。

SQL_DTS_Access_004_mid_640

<Fig.4 : データインポートウィザードでデータの区切り形式を選択する>

次にフィールド区切り記号を選択する。フィールド区切り記号は「カンマ」を指定する。また「先頭行をフィールド名として使う」オプションは、読み込まれたデータを見てフィールド名に相当するデータがないので、チェックを外す。

SQL_DTS_Access_005_mid_640

<Fig.5 : フィールド区切り記号の選択と先頭行をフィールド名として使うかの選択>

次に読み込むフィールドのフィールド名や型を設定する。Access が読み取ったデータからフィールドの型を類推して決めてくれるが、郵便番号は数値として判断される場合がある。確かに郵便番号は数値なのだが、数値として読み取った場合は先頭のゼロが無視されてしまうので、郵便番号のフィールドは「テキスト型」に変更しておく。

SQL_DTS_Access_006_mid_640

<Fig.6 : フィールドのオプションを設定する>

主キーの設定では、ここでインポートするデータの主キーが不明であるので、「主キーを自動的に設定する」を選んでおく。こうすれば自動的に「ID」のフィールドが作られ、連番が与えられる。

SQL_DTS_Access_007_mid_640

<Fig.7 : 主キーの設定>

インポートウィザードの最終場面では、インポート先のテーブル名を記述する。ここではファイル名から自動的に割り当てられた「KEN_ALL」の名前とした。

SQL_DTS_Access_008_mid_640

<Fig.8 : インポート先のテーブルの指定>

インポートが完了したら、「インポート操作の保存」の画面になる。同じインポート操作を後で繰り返したい場合は、ここで「インポート操作の保存」にチェックをしておくと、簡単に再現できる。ここでは繰り返ししないつもりなので、チェックをしないでおく。

SQL_DTS_Access_009_mid_640

<Fig.9 : インポート操作の保存>

インポートウィザードが終わったら、インポートデータは新たに生成した「KEN_ALL」テーブルに保存されている。テーブルの下部にはレコード数が 123,708 件であることがわかる。

SQL_DTS_Access_010_mid_640

<Fig.10 : インポートウィザードを終了しデータをインポートした>

読み込んだ「KEN_ALL」テーブルのデータを見ると、フィールド1 は意味がよくわからない何らかの管理番号になっているようだ。フィールド2 は郵便番号の前部分、フィールド3 は郵便番号の全6桁、フィールド4 からフィールド6 が住所の読み仮名、フィールド7 からフィールド9 が住所になっている。フィールド10 から15 は何らかの管理フラグだ。

データ構造がわかったら、これに相当するテーブルを SQL Server に作る。CREATE 文は次のようなものとした。

——————————————————————————-

CREATE TABLE 郵便番号住所2(
    管理番号 int NULL,
    プレ郵便番号 varchar(7) NULL,
    郵便番号 char(7) NULL,
    都道府県カナ nvarchar(7) NULL,
    市区町村カナ nvarchar(25) NULL,
    住所それ以降カナ nvarchar(80) NULL,
    都道府県 nvarchar(10) NULL,
    市区町村 nvarchar(25) NULL,
    住所それ以降 nvarchar(80) NULL,
    管理フラグ1 char(1) NULL,
    管理フラグ2 char(1) NULL,
    管理フラグ3 char(1) NULL,
    管理フラグ4 char(1) NULL,
    管理フラグ5 char(1) NULL,
    管理フラグ6 char(1) NULL
)

——————————————————————————-

SQL_DTS_Access_011_mid_640

<Fig.11 : SQL Server Management Studio で郵便番号住所をストアするテーブルを作る>

次に Access を SQL Server のテーブルに「リンクテーブル」として接続する。リボンメニューの「外部データ」から「その他」をクリックし「ODBC データベース」を実行する。

SQL_DTS_Access_012_mid_640

<Fig.12 : リボンメニューの「外部データ」から「その他」をクリックし「ODBC データベース」を実行する

外部データの取り込みウィザードがはじまる。日本郵便のサイトからダウンロードした csv のテキストファイルは Access に取り込んだが、ここでは SQL Server のテーブルにリンクするために「リンクテーブルを作成してソースデータにリンクする」を選択する。

SQL_DTS_Access_013_mid_640

<Fig.13 : 外部データの取り込みウィザードでリンクテーブルを作成してソースデータにリンクする>

「データソースの選択」では、適切な SQL Server へのデータソースがあれば選択するだけでいいが、なければ新規作成する。DNS 名のところの「新規作成」ボタンをクリックする。

SQL_DTS_Access_014

<Fig.14 : データソースの選択>

セットアップするデータソースのドライバーを選択する。接続したい SQL Server を選択して「次へ」をクリックする。

SQL_DTS_Access_015

<Fig.15 : セットアップするデータソースのドライバーを選択する>

データソース名を決めて入力する。データソース名は、後で見てわかるような、適当な名前でいい。

SQL_DTS_Access_016

<Fig.16 : データソース名を決めて入力する>

データソースが作成される。「完了」をクリックする。

SQL_DTS_Access_017

<Fig.17 : データソースの作成>

接続する SQL Server を選択する。「サーバー」テキストボックスの右端▼をクリックすると、利用できる SQL Server が選択できるので、テーブルを作成した SQL Server を選択して「次へ」をクリックする。

SQL_DTS_Access_018

<Fig.18 : 接続する SQL Server を選択する>

SQL Server のログイン認証を選択する。「ネットワークへのログイン ID で、Windows NT の認証メカニズムを使う」はいわゆるWindows 統合認証で、「ユーザーが入力する SQL Server 用のログイン ID とパスワードを使う」は SQL Server にユーザーを作ってログインするものだ。作業している環境によるが、ここでは前者のログインで利用するものとする。

SQL_DTS_Access_019

<Fig.19 : SQL Server のログイン認証を選択する>

既定のデータベースを設定する。郵便番号住所テーブルを作成したデータベース名は「test」だったので、それを選択する。

SQL_DTS_Access_020

<Fig.20 : 既定のデータベースを設定する>

これでデータソースの作成を完了する。

SQL_DTS_Access_021

<Fig.21 : データソースの作成を完了する>

データソースが作成されたら、データソースの詳細が表示される。ここで「データソースのテスト」をクリックすると、SQL Server に接続テストをすることができる。

SQL_DTS_Access_022

<Fig.22 : データソースの作成完了とテスト>

データソースの接続テストが成功した。

SQL_DTS_Access_023

<Fig.23 : SQL Server へのデータソースの接続テスト>

データ接続ができたら、リンクしたいテーブルを指定する。ここでは「郵便番号住所2」テーブルに接続したい。

SQL_DTS_Access_024

<Fig.24 : テーブルのリンク>

リンクテーブルが作成できた。リンクテーブルは地球のようなアイコンで、画面の左側「すべてのテーブル」に「dbo.郵便番号住所2」の名前で表示されている。このテーブルは Access 上に実体はなく、SQL Server のテーブルにリンクしている。

SQL_DTS_Access_025_mid_640

<Fig.25 : リンクテーブルの作成完了>

リンクテーブル「dbo.郵便番号住所2」を開くと、SQL Server 上のデータを見ることができる。まだデータは入っていないので、一件もデータがない。

SQL_DTS_Access_026_mid_640

<Fig.26 : リンクテーブル「dbo.郵便番号住所2」を開いてデータを確かめる>

リンクテーブルが作成できたら、Access にインポートしたデータを、リンクテーブル「dbo.郵便番号2」へコピーする。Access でテーブルへデータをコピーするとき、簡単なのは「追加クエリ」を作成することだ。

「新しいクエリ」を作成し、テーブル「KEN_ALL」を元データとする。画面に表示された「KEN_ALL」テーブルの各フィールドを下段にドラッグし、表示させる。

SQL_DTS_Access_027_mid_640

<Fig.27 : 新しいクエリを作りフィールドを表示させる>

次にこのクエリを「追加クエリ」にする。Access では SQL 文を使ってデータを追加することもできるが、単純にテーブルからテーブルへデータを追加する場合は、追加クエリを使うと便利だ。

クエリを追加クエリにするには、クエリの編集画面で「デザイン」メニューの「+!」のアイコン、「追加」をクリックする。すると「追加」のウインドウが表示され、追加先のテーブルを選択するようになる。追加先はリンクテーブル「dbo.郵便番号2」を選択する。

SQL_DTS_Access_028_mid_640

<Fig.28 : クエリを追加クエリにする>

SQL_DTS_Access_029_mid_640

<Fig.29 : 追加クエリの設定で追加先のテーブルを選択する>

dbo.郵便番号2」をクエリを追加クエリにすると、画面の下部に表示されたフィールドの下の項目が変わる。単なる選択クエリの場合は、「フィールド」、「テーブル」、「並べ替え」、「表示」、「抽出条件」、「または」となっているが、追加クエリにすると「表示」の項目はなくなり、かわりに「レコードの追加」が表示される。これは、データを追加するテーブルのどのフィールドに追加するかを選択する項目だ。各フィールドの「レコードの追加」クリックすると、追加先に設定した「dbo.郵便番号2」の各フィールドが表示されるので、追加したい適切なフィールドを選択し決定していく。追加しなくてもよいフィールドは指定しなくていい。

ここで作成した SQL Server の「dbo.郵便番号2」の場合、「KEN_ALL」テーブルの各フィールドに対して、「ID」を「管理番号」に、「フィールド1」は指定せず、「フィールド2」を「プレ郵便番号」に、以降、フィールド3から「郵便番号」以降を順に設定する。

SQL_DTS_Access_030_mid_640

<Fig.30 : クエリを追加クエリにした>

SQL_DTS_Access_031_mid_640

<Fig.31 : 追加クエリの各フィールドに追加先のフィールドを設定する>

追加クエリができたら保存する。クエリ名は「郵便番号住所の追加」とした。

SQL_DTS_Access_032_mid_640

<Fig.32 : 追加クエリを保存する>

追加クエリが保存できたら、「すべてのテーブル」にあるクエリの名前「郵便番号住所の追加」をダブルクリックする。「追加クエリを実行すると、テーブルのデータが変更されます」となるので「はい」をクリックする。次に追加されるデータの件数が表示されるので、間違いなければ「はい」をクリックする。

SQL_DTS_Access_033_mid_640

<Fig.33 : 保存した追加クエリ「郵便番号住所の追加」をダブルクリックして実行する>

SQL_DTS_Access_034_mid_640

<Fig.34 : 追加クエリを実行してテーブルにデータを追加する>

SQL_DTS_Access_035_mid_640

<Fig.35 : 追加されるデータの件数が表示される>

この追加クエリは、SQL の INSERT 文になっている。SQL 文を確かめるには、追加クエリをデザインし「SQL ビュー」にするといい。次のような SQL 文が生成していることがわかる。

——————————————————————————-

INSERT INTO dbo_郵便番号住所2 ( 管理番号, プレ郵便番号, 郵便番号, 都道府県カナ
              , 市区町村カナ, 住所それ以降カナ, 都道府県, 市区町村, 住所それ以降
              , 管理フラグ1, 管理フラグ2, 管理フラグ3, 管理フラグ4, 管理フラグ5, 管理フラグ6 )
SELECT KEN_ALL.ID, KEN_ALL.フィールド2, KEN_ALL.フィールド3, KEN_ALL.フィールド4
              , KEN_ALL.フィールド5, KEN_ALL.フィールド6, KEN_ALL.フィールド7, KEN_ALL.フィールド8
              , KEN_ALL.フィールド9, KEN_ALL.フィールド10, KEN_ALL.フィールド11, KEN_ALL.フィールド12
              , KEN_ALL.フィールド13, KEN_ALL.フィールド14, KEN_ALL.フィールド15
FROM KEN_ALL;

——————————————————————————-

SQL_DTS_Access_037_mid_640

<Fig.36 : 追加クエリを SQL ビューで確認する>

追加クエリを実行すると、Access にインポートされたデータが SQL Server に追加されているはずだ。「dbo.郵便番号住所2」のリンクテーブルを開いたままなら、一度閉じて再度開くと最新のデータが表示される。

SQL_DTS_Access_038_mid_640

<Fig.37 : 「dbo.郵便番号住所2」のリンクテーブルを開いてインポートされたデータを表示する>

SQL Server Management Studio で直接 SQL Server のテーブルを確かめても、データがインポートされたことが確認できる。

SQL_DTS_Access_039_mid_640

<Fig.38 : SQL Server Management Studio でインポートされたデータを確認する>

このように、Access を使って SQL Server にデータをインポートすることができる。まず Access に CSV データを取り込み、次に「リンクテーブル」で Access を SQL Server に接続し、「追加クエリ」でデータを追加するのだ。

Access の追加クエリは画面上でフィールドを選択することで視覚的にテーブルからテーブルにデータを追加することができるので、SQL Server のデータ処理に使ってもいいだろう。

Microsoft Visual Studio LightSwitch 2011を使ってみた – テンプレートとウィザードにより簡単にSQL Serverなどのデータベースと連携する業務アプリケーションを作ることができる

leave a comment »

IMG_8288_mid_640_480

Microsoft Visual Studio LightSwitchのページには「Microsoft Visual Studio LightSwitch は、テンプレートなど使って素早く簡単に業務アプリケーションを作成し、デスクトップやクラウドに展開できる簡易開発ツールです。」とある。Visual Studioはたいへん使いやすい開発ツールであり、書店にいくと初心者向けの解説書がたくさんある。どの本もわかりやすく、C#プログラミングを独習する環境は整っている。しかしほとんどの初心者向けの解説書では、Windows Formをつくりアプリケーションの中でデータを処理することは説明されているが、データベースに接続するアプリケーションを作ろうと思えば敷居がぐっと高くなる。それには「データベース接続アプリケーション開発入門」といった別の解説書があるくらいだ。

いまどきデータ処理を行うとき、SQL Serverなどのデータベース連携抜きに業務アプリケーションは考えられないだろう。データベースに連携しなくてよいなら、ExcelやAccessなどを使えばいいはずだ。いかにデータベース連携アプリケーションを簡単に作ることができるか、その解答が、このMicrosoft Visual Studio LightSwitchだ。

少し使ってみればわかるが、データベースにテーブルさえ作っておけば、Visual Studio LightSwitchではウィザードによってあっというまにデータベース連携のフォームを作ることができる。デフォルトで用意されているテンプレートは、データを閲覧するための「一覧および詳細画面」、「データ検索画面」、データを編集するための「詳細画面」、エクセルのような表の形で編集できる「編集可能グリッド画面、そして新規データを入力するための「新規データ画面」だ。

データに対して処理をしたいとき、デザイン画面からドロップダウンリストで「生徒_Inserted」や「生徒名_Changed」といったメソッドを追加でき、そこに「合計点数 = 中間考査点 + 期末考査点」や「result = 中間考査 + 期末考査」といった式を書くことで演算値を得ることができる。直観的でわかりやすい。

学校で使うなら、欠席時間数、中間考査と期末考査の素点、平常点、などを入力し、評価点を定期考査6割、平常点4割で集計し、ランク付けして10段階評価と5段階評定を自動的に計算する、欠席時間数が所定の時間をオーバーしたら評価は出さない、といったフォームを簡単に作ることができる。またデータをSQL Serverに連携しておけば、入力結果はSQL Serverにストアされる。

Microsoft Visual Studio LightSwitchは全機能を持った評価版を90日間試すことができる。また驚くべきことにアカデミック版なら、今Amazonで調べると4,109円である。Microsoft Visual Studio LightSwitch、まさに学校の教員むけの開発ツールだという印象だ。成績処理などのシステムを改善しようと思っているなら、ぜひこのMicrosoft Visual Studio LightSwitchを使ってみることをお奨めする。

アドミンティーチャーズ第6回(大阪)勉強会は2011年2月19日開催

leave a comment »

東京の勉強会に続いて大阪でも勉強会をする。東京の勉強会のテーマはデータベースにポイントを絞ったが、大阪の勉強会では広く「教育の情報化」をテーマにした。

今回の勉強会は講師陣がスペシャルである。

まず、昨年末に満を持して発売された、シャープのメディア情報端末「ガラパゴス GALAPAGOS」について、シャープの方より技術的な話をうかがう。昨年度はいわば「電子書籍元年」だと言えるが、シャープのガラパゴスは電子書籍はもちろん、メディアサービスと連携し、ビデオや音楽も再生できる端末だ。今後はこのようなPCではない情報端末も普及することが予想され、その活用は学校現場でも求められるだろう。

そして現在兵庫県立須磨東高等学校に勤務される、仲正博先生をお迎えして、長年開発を続けてこられた「IKシステム」についての話をしていただく。IKシステムは仲先生が兵庫県立伊川谷北高等学校に勤務されていた1993年に、学校で統一的に成績処理をするシステムが必要とされて開発され、以降、全日制普通高校の標準的な仕様を確立されて一般公開し、現在数多くの学校で利用されている校務処理システムだ。仲先生からこのIKシステムの歴史や開発の理念などを語っていただく。

そして実際に学校現場で、情報システムの運用管理がどのようになされていて、どんな問題があるのか、うまくいっているコツはなにか、などを、現場の担当者からレポートしていただきディスカッションする。参加者の方も積極的に参加していただければと思う。

私からは東京勉強会と同じ内容の、InfoPathとAccessを使った教務システムの構築デモを演示する。全日制普通高校の成績処理を念頭におき、フルスクラッチで50分の時間でどれだけできるかをお見せする。この勉強会で使うデモは、持ち帰ってご自分でもやってみることができるようにテキスト形式のものを用意するので、勉強会では、だいたいどのような感じで作ることができるのか、を把握していただくことを目標にする。

勉強会は参加費無料、定員40名。場所はマイクロソフト関西支店セミナールームだ。学校の先生だけでなく、教育の情報化に興味のある方なら大歓迎だ。申し込みはアドミンティーチャーズWebサイトから電子メールで受け付けている。

アドミンティーチャーズWeb

http://adminteachers.wordpress.com/

第6回勉強会(大阪)の詳細ページ

http://adminteachers.wordpress.com/2011/01/10/%e7%ac%ac%ef%bc%96%e5%9b%9e%e3%82%a2%e3%83%89%e3%83%9f%e3%83%b3%e3%83%86%e3%82%a3%e3%83%bc%e3%83%81%e3%83%a3%e3%83%bc%e3%82%ba%e5%8b%89%e5%bc%b7%e4%bc%9a%ef%bc%88%e5%a4%a7%e9%98%aa%ef%bc%89%ef%bd%9e/