特集

2013年9月11日

汎用的なツールで作るDB→Excel→メール配信の自動システム：Windows環境でちょっとした定型業務を自動化する（1/2 ページ）

データベースから抽出したデータをExcelブックのレポートにまとめてメールで配信する。よくある業務だが、自動化するにはどうすればよいのか？筆者が実際に作成して現在も運用中のシステムを例に、幅広く応用しやすい汎用的なツールによる自動化手法を説明する。

[島田広道，デジタルアドバンテージ] PC用表示関連情報

LINE

Hatena

連載目次

　社内のデータベース・サーバから定期的に必要なデータを抽出し、レポートとして配布する、というのはよくある業務だ。レポートの体裁を整えたり配布先のメール・アドレスを設定したり、といった作業は定型であり、しかも手作業だと意外に時間を食われるので、ぜひ自動化したいところだ。

　ただ、自動化といってもその手法はさまざまだ。できることなら、多くの業務に応用しやすい「つぶしの効く」手法を身に付けるのが望ましい。そこで本稿では、なるべく汎用的で応用しやすいツールを活用するという方針で、筆者が実際に作った社内データベースからの自動レポート作成・配信システムの一例を紹介したい。WindowsクライアントPC上で実行することを前提に、バッチ・ファイル主体で組み上げたものだ。正直にいって実装には拙いところもあるし、高度なスクリプト言語に精通していればもっとスマートでメンテナンスのしやすい実装もあるだろう（その点で、本稿の実装がベストだなどと主張するつもりはまったくない）。一方で、あまりスクリプトに慣れていないIT Proには利用しやすいテクニックがあると思う。さまざまな処理の自動化の参考にしていただければ幸いだ。

　なお、DBMSとしてはSQL Serverを対象としている。またデータベースからデータを抽出するというテーマ上、ある程度のT-SQLの知識が必要となることはご了承いただきたい。

例として取り上げるレポート配信システム

　本稿で実例として取り上げるのは、弊社の「補助金サーチ・メール・サービス」の自動配信システムである。このサービスでは、住宅向け太陽光発電設備を設置する際に自治体から提供される補助金の金額や提供条件などを、Excelブックに取りまとめてレポート化し、毎月1回、メールに添付して顧客に配信している。

「補助金サーチ・メール・サービス」で配布しているExcelシート
閲覧しやすいようにセルや文字の色、罫線などの装飾を施している。右端のリンクをクリックするとダイレクトに各自治体の補助金情報ページを参照できる。詳細は後で説明するが、いずれも自動で設定している。

　補助金情報はSQL Serverによるデータベースに集約しているので、そこから必要な情報を抽出してExcelブックに書き込み、体裁を整えてからメールに添付して、顧客に1通ずつ配信する、という仕組みだ。以下に各処理を分類する。

SQL Serverから必要なデータを抽出
1．のデータをテキストとして整形
Excelに2．のデータを入力してExcelブックに変換
3．のExcelブックをメールに添付して各顧客に送信

どうやってSQLデータベースからデータを抽出するか？

　SQL Serverのデータベースからデータを抽出するには多くの方法がある。例えばExcelから直接SQL Serverに接続して抽出することも可能だ。前述の処理内容からすると、これがスマートな方法に感じられる。実際に筆者も最初はこの方法で実装しようとしたが、VBA（Visual Basic for Application）のデータベース関連機能を使いこなせず、SQL Serverに問い合わせる（クエリをかける）たびに異なる結果が返るなど、思ったようにデータを抽出することができなかった。

　そこで、代わりにSQL Serverへクエリをかけて戻り値を標準出力に出力するSQLCMDコマンドを利用した。これはSQL Serverに付属していて、SQL Serverのクライアント・ツールをインストールすると利用できる（データベース・エンジンをインストールできないクライアントPCでも利用可能）。SQLCMDを使うと、Excelへのインポート処理が必要になる半面、出力がExcelブック以外の場合にも応用できるというメリットがある。

　SQLCMDコマンドでデータベースからデータを抽出するコマンドラインの一例を記す。ここではWindowsにログオン中のユーザー・アカウントが持つ資格情報でSQL Serverにアクセスし、カンマ区切りのCSV形式でデータを出力し、「subsidy.csv」というファイルに保存している。

sqlcmd -S DBServer1 -E -w 4096 -s "," -W -i SQL¥make.sql -o CSV¥subsidy.csv

　各オプションの意味は次の通りだ。


オプション	意味
-S ＜サーバ名＞	SQL Serverを指定する
-E	SQL Serverにアクセスする際に、ログオン中のユーザー・アカウントが持つ資格情報を利用する
-U ＜ユーザー名＞	SQL Serverにアクセスするユーザー・アカウントを指定する（「-E」とは排他）
-P ＜パスワード＞	上記ユーザー・アカウントに対応するパスワードを指定する（「-E」とは排他）。あらかじめ SQLCMDPASSWORDという環境変数にパスワードを設定しておけば、これは不要になり、バッチ・ファイル中のコマンドラインにパスワードを平文で記さずに済む
-w ＜桁数＞	指定した桁数で出力結果をフォールドする。デフォルトは80桁。十分に長い桁数を指定すると改行されなくなり、連結など面倒な後処理をせずに済む
-s "＜区切り文字＞"	フィールドを区切る文字を指定する。デフォルトはタブ文字
-W	値の後に続く空白文字を省く。デフォルトでは空白文字列が連なって後処理が面倒になるので指定する方がよい
-i ＜T-SQLファイル名＞	指定したファイルに記述されたT-SQLでクエリを実行する
-o ＜出力ファイル名＞	指定ファイルに抽出結果を保存する（デフォルトは標準出力）
SQLCMDコマンドの主要なオプション

　SQL Serverに保存されたアカウントでアクセスする場合には、-Eオプションの代わりに-Uオプションと-Pオプションを指定する必要がある。SQL Serverの設定や環境に応じてどちらかを選べばよい。SQLCMDコマンドの詳細については次のTechNetのページを参照していただきたい。

SQLCMDコマンド（マイクロソフト TechNet）

　前述のコマンドラインにある「make.sql」すなわちT-SQLファイルには、あらかじめデータベースから必要なデータを抽出するためのT-SQL文を記述しておく。以下はその一例である（この内容はテーブルの内容や必要な出力結果に大きく依存するので、細かい説明は省かせていただく）。

use sunlife
select CityCode as '自治体コード', '都道府県名' =
    CASE
        WHEN CityName like '国' THEN '"国"'
        ELSE '"'+PrefName+'"'
    END
, '市区町村名' =
    CASE
        WHEN CityName like '国' THEN '""'
        WHEN CityName like PrefName THEN '""'
        ELSE '"'+CityName+'"'
    END
, '市区町村名（読み）' =
    CASE
        WHEN CityNameYomi is NULL THEN '""'
        ELSE '"'+CityNameYomi+'"'
    END
, '補助金の有無' =
    CASE
        WHEN HasSupport = 0 THEN '"なし"'
        WHEN HasSupport = 1 and CalcMethod = 120 THEN '"受付終了"'
        WHEN HasSupport = 1 and CalcMethod in (100, 101, 102, 103, 104, 105, 134) THEN '"あり"'
        ELSE '"Invalid Value!"'
    END
, '説明' =
    CASE
        WHEN HasSupport = 0 THEN '"現在利用できる補助金制度はありません"'
        ELSE '"'+convert(nvarchar(4000), Description)+'"'
    END
, '"'+InqURL+'"' as '連絡先URL'
from tSubsidy
where isValid = 1
order by PrefCode, CityNameYomi

補助金サーチ・メール・サービスでデータ抽出に使っているT-SQL
テーブルの各フィールドの値をそのまま出力するのではなく、一定のロジックに従って加工してから出力しているため、やや複雑になっている（このロジックは本来、データベースのビューとして実装すべきかもしれない）。

データベースから抽出されたテキスト・データをどうやって整形するか？

　データベースからデータを抽出したら、Excelブック化する前に、不要な行を削除したり日付の形式を変換したり、といったテキスト整形をする。ExcelでもVBA（Excelマクロ）の文字列操作機能によって自動整形は可能だが、そのテクニックはOffice以外のツールには応用できない。その点では、テキストのまま処理できる汎用的なツールの方が便利といえよう。

　本稿の場合、テキスト・エディタの「秀丸エディタ」が備えるマクロ機能でテキスト整形を自動化している。秀丸エディタは有償のシェアウェアで、次のページからダウンロードして試用できる。

秀丸エディタ（サイトー企画）

　マクロなどによる自動処理と正規表現によるテキスト検索／置換、コマンドラインから自動実行ができるなら、秀丸エディタ以外のテキスト・エディタ、あるいはPerlなどでも実現できるだろう。

　さて、秀丸エディタのマクロで自動処理を行うには、次のようなコマンドラインを実行する。

"c:¥Program Files¥Hidemaru¥Hidemaru.exe" /h /n /xMakeSubSidySheet.mac CSV¥subsidy.csv

　秀丸エディタの実行ファイル「Hidemaru.exe」が起動されると、まず「CSV\subsidy.csv」が処理対象ファイルとしてオープンされ、その次に「MakeSubSidySheet.mac」に記載したマクロが自動的に実行される。各オプションの意味は次の通りだ。


オプション	意味
/h	実行中に秀丸エディタのウィンドウを表示しない
/n	オープンするファイルをファイル履歴に加えない
/x＜マクロ・ファイル名＞	秀丸エディタの起動後に指定したマクロを自動実行する
秀丸エディタのマクロ自動実行時に指定しているオプション

　マクロの内容も記しておく。実は正規表現によるテキスト置換しかしていない単純なものだ。

setcompatiblemode 15;　　……マクロの互換性の設定
disabledraw;　　　　　　……実行中の描画を禁止する（高速化のため）

// タブ文字の削除
replaceallfast "¥¥t" , "" , regular, nocasesense;

// 半角スペースやハイフンのみ、ハイフンとカンマのみの行の削除
replaceallfast "^ +¥¥n" , "" , regular, nocasesense;
replaceallfast "^¥¥-+¥¥n" , "" , regular, nocasesense;
replaceallfast "^[-,]+¥¥n" , "" , regular, nocasesense;

// "NULL"を空文字に変更
replaceallfast "NULL," , "," , regular, nocasesense;

// 文字列フィールドの先頭と末尾にある連続スペースを削除
replaceallfast "¥" +" , "¥"" , regular, nocasesense;
replaceallfast " +¥"" , "¥"" , regular, nocasesense;

// 日時から時刻を削除し、年月日の区切り文字を「-」から「/」に変更
replaceallfast ",¥¥f[0-9]+¥¥f-¥¥f[0-9]+¥¥f-¥¥f[0-9]+¥¥f [0-9]+:[0-9]+:[0-9]+[0-9¥¥.]*," , ",¥¥1/¥¥3/¥¥5," , regular, nocasesense;

// SQLCMDコマンドによる余分なメッセージの削除
replaceallfast "^データベースコンテキスト[^¥¥n]+に変更されました。¥¥n" , "" , regular, nocasesense;
replaceallfast "¥¥n¥¥([^¥¥n]+処理されました¥¥)¥¥n" , "" , regular, nocasesense;

save;　　　　　　……上書き保存

enabledraw;　　　　　　……実行中の描画の有効化
exit;

テキスト整形のための秀丸マクロの例
実質的に文字列の置換しかしていない単純なマクロである。
※replaceallfast：全文を対象とした文字列置換
　 regular：検索／置換に正規表現を用いる
　 nocasesense：大文字／小文字を区別しない

どうやってExcelブックの体裁整形を自動化するか？

　CSVファイルのテキスト整形を終えたら、Excelに読み込ませてExcelブックを作成し、罫線や文字色などの装飾をしたり、セルの列幅を揃えたりしながら体裁を整える。これを自動化するにはVBAを利用するが、一からVBAを記述するのは手間が掛かるので、Excelのマクロ記録（いわゆるキーボード・マクロ）で体裁を整える操作をVBAに変換してから、必要に応じて編集・修正するのが簡単だ。

●マクロを記録する

　体裁の整え方を決めたら、対象のCSVファイルを開いた状態でリボンの［表示］タブ－［マクロ］の▼マークをクリックし、メニューから［マクロの記録］をクリックする。

Excelでキーボード・マクロを記録する手順
手動で操作した結果を「キーボード・マクロ」として記録し、体裁を整える手順をマクロ化する。
　 （1）このタブを選ぶ。
　 （2）この▼マークをクリックしてメニューを表示させる。
　 （3）これをクリックするとマクロ記録のダイアログが表示されるので、マクロ名を指定して［OK］ボタンをクリックする。これでマクロの記録が始まる。

　表示されたダイアログでマクロ名を指定し、マクロの保存先に「新しいブック」を選んで［OK］ボタンをクリックする。「新しいブック」にマクロを保存するのは、他のユーザー・アカウントで自動実行する際に扱いやすいからだ（個人用マクロブックは記録時のログオン・アカウントからしか利用できない）。

　これでマクロの記録が始まるので、体裁を整える操作をしていく。そのときのポイントの1つは、データ範囲内でセルのフォーカスを移動する際は［Ctrl］＋矢印キー、範囲指定の際は［Ctrl］＋［Shift］＋矢印キーをそれぞれ使うことだ。これにより絶対座標（"B2"とか）でセルを参照するマクロが減るため、後で行うマクロの編集作業が少なくなる。

　操作を終えたら、［表示］タブ－［マクロ］の▼マーク－［マクロの記録終了］をクリックする。また、マクロを保存した新規ブックは、マクロ有効ブック（*.xlsm）フォーマットで名前を付けて保存する。

Excelマクロの編集・実行から一連の処理の自動化まで

　　　　　　 | 次のページへ