本記事では、Excelアクション及びOpenDocumentスプレッドシートアクションでデータセット化した後にループでデータ走査を行うのではなく、SQL文を使用してデータ抽出を行うサンプルを紹介します。
(本記事の内容は、記事「CSVファイルをデータベースに見立ててSQL文でデータ抽出したい」の内容を使用したものとなります)
なお、SQL文の条件式の書き方など、AutoMateとは直接関係のない内容に関する問い合わせは弊社ではお答え致しかねますのでご留意ください。
■0. ODBCデータソースの設定
基本的には、上記記事の内容と同様に設定を行います。(ODBCドライバーは、「Microsoft Excel Driver」を指定)
なお、接続用のブックを選択する箇所[図1]ですが、ここで処理対象のExcelを選ぶ必要はなく、任意のExcelファイルを指定する形でかまいません。
(例：「C:\RPA_TASKS\【消すな】AutoMate接続用.xlsx」など)
したがって、ExcelファイルごとにODBCデータソースを作成する必要はなく、ODBCデータソースは1つあればどのExcelファイルに対しても流用出来ます
[図1：ODBCデータソース名を「ExcelReader」とした場合の設定画面]
。
■1. データ抽出(基本編)
以下のようなExcelのデータを考えます。(ブック名は「海産物マスタ」、シート名は「商品マスタ」とします)
----------------以下、データ(コピペでExcelに貼り付けるとセルが分かれます)----------------
| 商品コード | 商品名 | 定価 |
| 1 | イクラ | 1000 |
| 2 | ウニ | 5300 |
| 3 | タラコ | 800 |
| 7 | イセエビ | 10000 |
| 6 | サザエ | 1200 |
| 8 | ナマコ | 1200 |
----------------以上、データ(コピペでExcelに貼り付けるとセルが分かれます)----------------
このようなExcelがデスクトップに存在する状態において、以下のアクションを実行します。
なお、ファイル名(フルパス)およびシート名の部分は、「`」で括ります。
(必ず、接続設定の部分は実行環境に合わせて変更ください)
<AMDATABASE ACTIVITY="sql_query" CONNECTION="0AM5Q7XqUKhx33u+iZMN5TJq8KVugbAd6QUzTJgFqq7Y/dSAUxfmsDzjVzROg4s7zghfIqX9x98eyJ9moJNewFJJzyLPsMDQFcfX8DxHKdmQZVA=aME" RESULTDATASET="ds_result">select * from `%GetDesktopDirectory()%\海産物マスタ.xlsx`.`商品マスタ$`;</AMDATABASE>
実行結果として、以下が得られます。列名およびデータが確認できます。
なお、Excelのデータ1列目がヘッダーになっていない(データが無い)場合は、列名が左から順に「F1」,「F2」,・・・,「F26」,「F27」,・・・・のようになります。
また、ヘッダー行がブランクになっているセルが複数存在(B1セルおよびC1セルがブランクなのに、D1セルは列名が存在している等の状態)していたり、ヘッダー名が数字から始まっていたり、全角数字を含んでいたりするとエラーになるのでご注意ください。
[図2：SQL実行結果(全列取得、フィルタ条件および並び替えなし)]
次に、定価が1000円より高い商品を商品コード昇順で抽出します。
<AMDATABASE ACTIVITY="sql_query" CONNECTION="0AM5ED6TlL3FvXPhkfyHNXbnL16KJkHjrWPurxscbIkgi0fWvot4tII6N8GKRkw8mrzIpuIDT1xVqkWNiwT5SNB4Ql2+MvAPakmHuJB0QZJTj6A=aME" RESULTDATASET="ds_result">select * from `%GetDesktopDirectory()%\海産物マスタ.xlsx`.`商品マスタ$` where 定価 > 1000 order by 商品コード asc;</AMDATABASE>
実行結果のデータセットは以下のようになります。
[図3：SQL実行結果(全列取得、定価1000円より高いものを商品コード昇順で並び替え)]
■2. データ抽出(応用編)
以下のようなシートを追加で同じブックに追加したと仮定します。
シート名：「仕入先マスタ」
----------------以下、データ(コピペでExcelに貼り付けるとセルが分かれます)----------------
| 仕入先コード | 仕入先名 | 掛け率 |
| 10001 | X商事 | 0.6 |
| 10002 | Y水産 | 0.8 |
| 10003 | Z商店 | 0.75 |
----------------以上、データ(コピペでExcelに貼り付けるとセルが分かれます)----------------
シート名：「取引履歴」
----------------以下、データ(コピペでExcelに貼り付けるとセルが分かれます)----------------
| 取引番号 | 仕入先コード | 商品コード | 数量 |
| 20001 | 10001 | 7 | 8 |
| 20002 | 10003 | 2 | 14 |
----------------以上、データ(コピペでExcelに貼り付けるとセルが分かれます)----------------
以下のサンプルは、「取引履歴」シートの詳細を、「商品マスタ」「仕入先マスタ」両方のシートから情報を割り出してダイアログに表示するサンプルになっております。
----------------以下、サンプル----------------
<AMVARIABLE NAME="var_FileName" VALUE="海産物マスタ.xlsx" />
<AMVARIABLE NAME="var_FilePath">%GetDesktopDirectory()%</AMVARIABLE>
<AMVARIABLE NAME="var_SheetName" VALUE="取引履歴$" />
<!--↓のステップを環境に応じて変更する-->
<AMDATABASE SESSION="DatabaseSession1" CONNECTION="0AM5gksiYZb/Bjpgx1aRv5rnOeUdsVP74GthKGgAsl/HpqLKjpdNiLdTqqxTXOkdm9RUBX0hIM4sZhaWex2ugnRqIKfDwBQ2qV1pvP3tXQhc1KQ=aME" />
<!--取引履歴をデータセット化-->
<AMDATABASE ACTIVITY="sql_query" SESSION="DatabaseSession1" RESULTDATASET="ds_Torihiki">select * from `%var_FilePath%\%var_FileName%`.`%var_SheetName%`;</AMDATABASE>
<!--取引履歴の詳細を各テーブルから抽出して表示-->
<AMLOOP ACTIVITY="dataset" DATASET="ds_Torihiki" />
<!--取引履歴のデータより、仕入先名、商品名、取引金額(定価 * 掛け率 * 数量)を取得する-->
<AMDATABASE ACTIVITY="sql_query" SESSION="DatabaseSession1" RESULTDATASET="ds_result">select B.仕入先名, A.商品名, A.定価 * B.掛け率 * %ds_Torihiki.数量% as 取引金額 from `%var_FilePath%\%var_FileName%`.`商品マスタ$` A, `%var_FilePath%\%var_FileName%`.`仕入先マスタ$` B where A.商品コード = %ds_Torihiki.商品コード% and B.仕入先コード = %ds_Torihiki.仕入先コード%;</AMDATABASE>
<!--ヒット件数が0件の場合はエラーメッセージを出す(結果が0件 = 「%ds_result.TotalRows%」の値が0)-->
<AMIF EXPRESSION="%ds_result.TotalRows% = 0" />
<AMSHOWDIALOG MESSAGE="該当するデータが存在しません！" COUNTDOWNDELAY="5" />
<AMELSE />
<AMSHOWDIALOG COUNTDOWNDELAY="5">取引番号「%ds_Torihiki.取引番号%」の詳細は
------------------------------------------
仕入先名：%ds_result.仕入先名%
商品名 ：%ds_result.商品名%
取引金額：%ds_result.取引金額%
------------------------------------------
です。</AMSHOWDIALOG>
<AMIF ACTIVITY="end" />
<AMLOOP ACTIVITY="end" />
<AMDATABASE ACTIVITY="close_sql_connection" SESSION="DatabaseSession1" />
■3. 全体を通しての留意点
1. SQLによるExcelデータに対する操作は「参照(Select)」のみとお考え下さい。UpdateやDeleteはエラーになります。Insertも、データが被るとエラーになります。
(エラーメッセージ：[EXCEL_SQL][Main](Step 7) ERROR [HY000] [Microsoft][ODBC Excel Driver] このフィールドはリンクされた Excel ワークシートにあるため、編集できません。リンクされた Excel ワークシートのデータを編集する機能は、Access の今回のリリースでは無効になっています。)
2. 列名が重複していた場合は、列名に自動的に連番が振られますのでご留意ください。
3. 「.」や「*」などの特殊文字、全角数字、「▲」「・」などの記号が列名に含まれていると参照できません。SQLを実行する前にAutoMateで列名を書き換えるなどで回避してください。
SQLによるデータ抽出は、抽出元データの件数が大きい時ほど効果を発揮します。
データセットをループして大量の読み飛ばしを発生させながらデータを探すよりも、効率が一気に上がります。
SQL文を扱える方は、ぜひともお試しください。