AlteryxでExcelファイルの各シートからデータをまとめて読み込みたいのに、動的入力ツールが期待通りに動かないとき
こんにちは。DI部プリセールスエンジニアの兼本です。
AlteryxでExcelファイルの各シートからデータをまとめて読み込む代表的な手法として、「動的入力（Dynamic Input）」ツールを使用する方法があります。
ところがこの方法、シートのデータ書式が違う場合や一部フィールドのデータが欠けているとき、期待通りに動作しないことがあります。 例として、以下のようなExcelシートを読み込む場合を考えてみます。
使用するデータは、気象庁が提供している過去の気象データ・ダウンロードからダウンロードした3地点（東京、羽田、練馬）の気象データです。 気象庁から提供されているデータは、観測地点によっては全てのデータが記録されていないので、いくつか空欄のフィールドがあることが確認できます。
一つのファイルに地点ごとに分けた3つのシートを作成しています。シートのデータの並びは同じなんですが、1シート目以外は、データが欠けています。
まずは動的入力ツールの動作確認
上記のExcelファイルを動的入力ツールを使って一括読み込みすると、動的入力ツールが結合しようとしたフィールドのデータ型が一致しない旨を示すエラーが出力されます。
確認のため、データ入力ツールで自動的に設定された各シートのメタデータを確認したところ、天気概況のデータがないフィールドはV_WString型ではなくてDouble型で定義されてしまっていますね。
- シート「東京」のメタデータ
-
シート「羽田」のメタデータ
-シート「練馬」のメタデータ
Alteryxでは、Excelファイルのデータを読み込むときにデータの内容によってデータタイプを自動的に設定しているのですが、この機能が裏目にでてしまっています。 こんなときはマクロを使って対処する必要があります。
マクロとは？
Alteryxでは色々なツールを繋げて処理を作りますが、その際によく使う処理や繰り返しが必要な処理が出てきます。そのような一連の処理を繰り返し使うための仕組みとして、マクロという機能が提供されています。
マクロに関する基本的な説明は以下のエントリをご確認いただくのがわかりやすかと思います。
繰り返し処理を実現するマクロにはバッチマクロと反復マクロの2種類があるのですが、今回は複数のデータに対して同一の処理を繰り返したいので、バッチマクロを使用します。
シートからのデータ読み取りを実現するマクロの作成
今回やりたいことは指定されたExcelファイルのシートを読み込んで出力するだけなので、まずはそのためのマクロを作ります。 ただ、読み込んだシートのデータを出力するだけでは寂しいので、ファイルのフルパスからシート名を取り出して「地域名」として出力する処理を追加しています。
Alteryxでスタンダードマクロや反復マクロを作るときはワークフローに「インターフェース」カテゴリの「マクロ入力」ツールを配置するのですが、バッチマクロを作成するときは「コントロールパラメータ」ツールを配置します。 ただし、「コントロールパラメータ」ツールを配置しただけではマクロは動作しないので、続けて動作を定義します。
- まず、「コントロールパラメータ」ツールのプロパティを開き、ラベルに「シート名を含むExcelファイルのフルパスを入力」と入力しておきます。ここは任意の文字列で構いません。ラベルは作成したマクロをワークフローに配置したときの説明分として表示されます。
- 「アクション」ツールを配置して、入力アンカーを「コントロールパラメータ」ツールに、出力アンカーを「データ入力」ツールに接続します。プロパティのアクションタイプはデフォルトで「値を更新」が選択されていますので、このままでOKです。プロパティの「更新する値または属性」の一覧から「File-Value」を選択します。
- 「データ入力」ツールでは、今回読み込みたいExcelファイルをデータソースとして指定します。使用するシートは全てのデータが入っている「東京」を選択します。また「ファイル名をフィールド名として出力」プロパティで「フルパス」を選択しておきます。フルパスの情報は自動的に「Filename」フィールドに格納されます。
- 「正規表現」ツールで「Filename」フィールドに格納されているフルパス（<ファイル名>|||<シート名>）から<シート名>の部分を抽出して「地域名」フィールドに格納しています。
作った正規表現はこんな感じ。
.*.*\|{3}.(.*)\$.
私はどうも正規表現が苦手なので、rubularのような正規表現のチェックサイトを活用しています。 おまけで「選択」ツールを配置して不要になった「Filename」フィールドを除外しておきます。
- 次にDesignerのメニューから「表示＞インターフェースデザイナー」を選択します。マクロの入力画面のレイアウトを変更するための画面が表示されますが、ウィンドウ左側にある歯車アイコンをクリックしてプロパティを表示します。
- ここではマクロに関する設定を行うことができますので、出力モードを「名前による自動構成」に変更します。※ここが重要です。このプロパティのデフォルト設定となる「すべての反復は同じ出力スキーマ・・・」オプションは受け取るデータのスキーマが同じになることを想定しており、「動的入力」ツールと同じ動作をします。この設定を名前による構成に変更することで、今回のエラーに対処することができます。
- 最後に「マクロ出力」ツールを接続してマクロを保存します。マクロは *.yxmc という拡張子で保存されます。
ワークフローからマクロを呼び出す
作成したマクロを呼び出すためのワークフローを新規作成します。
- 「データ入力」ツールを配置して、再度読み込みたいExcelファイルを指定します。ただし、今回はシート名を指定するときのオプションで「シート名のリストのみをインポート」を選択します。
- フォーミュラツールを使って、後工程でマクロに渡すシート名を含むフルパス（<ファイル名>|||<シート名>）という文字列を作成します。
- それではマクロの呼び出しです。マクロはワークフローを作成するスペースで右クリックすると表示されるメニューから「挿入＞マクロ」を選択することで追加できます。作成済みのマクロファイル（*.yxmc）を指定するのですが、Designerの別タブですでにマクロを開いている場合はダイレクトに呼び出すことが可能です。
- マクロを配置したら、フォーミュラツールの出力アンカーをマクロの入力アンカーに接続します。
- マクロのプロパティを表示してフィールド選択で、先ほど作成した「Filename」を選びます。
ここまで作成したら、ワークフローに名前をつけて保存し、実行します。 エラーにならず、3つのシートからデータを読むことができました。1シートには90件のデータがあるので、3シート分270件のデータです。
ただ、地域名が表示されていないですね。実はフルパスの表記がマクロを作成したときに想定していた書式と違っていました。 マクロ作成時は
C:\気象情報-東京-201901-201903.xlsx|||`東京$`
という形式でしたが、フォーミュラツールで作成したフルパスは、以下の形式です。
C:\気象情報-東京-201901-201903.xlsx|||東京
というわけで、ワークフローが呼び出しているマクロを再度開いて、「正規表現」ツールの正規表現を以下のように修正しました。
.*.*\|{3}(.*)
マクロを保存して、ワークフローに戻ると以下のようなダイアログが表示されてマクロに変更があったことが通知されます。
再度、ワークフローを実行すると無事に地域名が表示されるようになりました。
まとめ
今回はマクロを使ってExcelシートを一括読み込みする方法についてご紹介いたしました。 マクロは高度なテクニックではありますが、使いこなせるとワークフローで処理できる内容の幅がとても広がります（あと、なんかカッコいい。）ので、ぜひとも会得していただきたいテクニックのひとつです。
Alteryxの導入なら、クラスメソッドにおまかせください
日本初のAlteryxビジネスパートナーであるクラスメソッドが、Alteryxの導入から活用方法までサポートします。14日間の無料トライアルも実施中ですので、お気軽にご相談ください。
