「WeeklyAlteryxTips#61 異なるスキーマのデータを同じExcelブックの異なるシートに保存する１」の続きです。
今回は、、、少々手続きがややこしいです。復習として、以下のItemマスタ、レシートデータがあるとします。
アイテムマスタ：
レシートデータ：
これを一つのExcelワークブックの異なるシートにバッチマクロで書き込む、というのがお題です。
バッチマクロで異なるスキーマのデータを異なるシートに保存する
今回は手順を図解したいと思います。これを行うためには、バッチマクロの外で縦持ちのデータに変換する必要があります。その後、バッチマクロ内で各データストリームごとに横持ちに復元し、保存する、ということになります。
バッチマクロ外の作業はざっくり以下のような形になります。
ポイントは、
- バッチマクロ内で復元できるよう、以下のフィールドを追加
- 他のデータストリームと区別するための識別用のフィールドを追加
- レコードの並び順が復元できるようなレコード順序の識別用フィールド
- カラムの並び順を保存できるようなカラム順序の識別用フィールド
- 複数のデータストリームは転置ツールで転置を行い、「Name」「Value」列にして一つにユニオン。
です。
バッチマクロ内は以下のようになっています。
縦持ちのデータを横持ちにして復元する、という方法です。バッチマクロでは各繰り返しごとにスキーマが改めて決定されるため、復元後のデータのスキーマはお互いに影響されることはありません。
なお、空間型やBlob型を含む場合はこの方法は利用できません（が、そもそもExcelでは取り扱えないので問題ないと思います）。
マクロの外のロジックについて
各データストリームに対する事前処理
各データストリームに対しては、以下のような処理を行います。
- レコード順保存のためのReocrdIDツール
- 転置ツールで縦持ちにする（キー列はRecordID）
- カラム名と並びを保存するために複数行フォーミュラでColumnID付与
- 保存時のためのシート名のために「SheetName」フィールド作成
これを各データストリームごとに作成し、ユニオンツールで統合します。
先頭から10レコードだけ取ると以下のようになっています。
元のデータはName列のカラム名とValue列の値のみです。それ以外は復元用に使います。この処理自体は固定的なのでマクロ化してしまえば処理として使い回せると思います。
保存用シート名リストの取得
その後、保存用のシート名を取得するために、集計ツールでシート名をグループ化します。
バッチマクロにデータを入れる
それらのデータをバッチマクロに入れます。
ちなみに、左側のデータストリームの部分はいくらでもユニオンに突っ込んで大丈夫です。
ちなみに、バッチマクロの設定で、グループ化オプションを使うと、各インプットがフィルタリングされるため少し速度が早くなります。いずれも「SheetName」フィールドを指定します。
バッチマクロ内部
バッチマクロの内部は比較的シンプルです。縦持ちを横持ちに復帰し、リネーム、オートフィールドツールでデータ型の復元をして保存、といった形になります。
ちなみに、最初のフィルターツールはなくても動作しますが、バッチマクロの使い方を知らない方がミスしそうなので、一応入れています。
ポイントは、データを復元する際のクロスタブで、フィールド名を格納している「Name」フィールドで復元すると復元後のフィールド名が「_」に置き換わってしまうため、一度ColumnIDで復元しています。これによりフィールドの順番も保たれます。
まとめ
- スキーマの異なるデータストリームを縦持ちにした後に、バッチマクロでExcelの複数シートに一気に書き込む方法をご紹介しました。
- 通常ここまでしなくとも他の方法で十分ですが、もしかしたら役に立つ場面があるかもしれません（ないかもしれません）。
- すべてキレイにマクロ化すると、以下のようなワークフローで運用が可能になります。
サンプルワークフローダウンロード
次回
ちょっと軽めのTipsにしたいと考えています。
コメント