データの取得でフォルダを読み込むときに相対パスを使おうとしてはまる

Rあたりだと,複数のExcelファイルを一括で読み込まして結合するぐらいなんてことはない。パッケージがある。ところがExcelだとそれが面倒。

Excelしか使わない職場でそんなん必要なのか?なんだけど,同一ファイルの同一シートに複数の人間に入力させると見通しが悪くなるので,複数ファイルに分割させて入力させたいという要望がある。

自分一人で作業するなら,CSVで入力をしておいてPowerShellを開いて

*.csv > filename

としてファイルをくっつけて,後からソートでヘッダを消すという強引なことをやるのだが,他人様が絡むとそうはいかない。あと,CSVExcelで使ってると警告がいちいちうるさいのでできればそれを黙らせたいのでExcelてやりたい。

最近,世間にはPowerQueryという便利なものがあるらしいということを知った。これを使ってみたところ,やりたかったことができて問題はなくなった。ただ,まだ他人様に渡せない。この機能,フォルダを指定すると絶対パスで指定されるため,他のマシンに持っていくと使えないのである。自分が使う分には,ソースの変更でフォルダを再指定してやれば済むのだが,他人様にそれをお願いするわけにはいかない。

ということで,相対パスで使えないかと調べたものの,私の日本語能力では探しきれなかった。

結局,ぐぐってでてきたのは,なんかわけのわからない横文字で色々書かれているものだった。

https://techcommunity.microsoft.com/t5/Excel/Power-Query-Source-from-Relative-Paths/td-p/206150

これを参考に動かしたところ動いたのて記録を残しておく。(気が向けばスクリーンショットとか貼ってわかりやすくする)

書かれてた解決法

まず,これを適当なセルに打ち込んで,

=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

それにFilePathという名前を打ち込んでから,

    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],

としろと書いてある。

要は

  1. 読み込ませたい絶対パスをどっかのセルに作る
  2. その部分をテーブルにして名前をつける
  3. PowerQueryで読み込む

とのことらしい。まあ,そうだろうと貼り付けれる性格ならそれで問題ないのだが?本当か?とはまった。以下は適当に調べて理解したことを書きつらねたもの。

適当な解説

1.絶対パスの作成

適当なセルに

=Cell("filename",$A$1)

とすると,ファイルのフルパスが生成される。ただし,これだとファイル名まで入ってしまうので除去してしまう必要がある。この関数,ファイル名が"[]"で囲まれて帰ってくるらしい。それを利用して,

FIND ("[",CELL("filename",$A$1),1)

と[の位置を探して,Left関数で("["の1文字手前までで切り出すので式に-1が入ってる)パスの部分だけを切り出している。

このフォーラムでのシチュエーションは同一のフォルダにあるブックを読み込むというシチュエーションなのでこれで仕事は終わり。

私の場合は,その直下に作った集約用のディレクトリのファイルを全部読み込みたいという要望なので,ここで文字列を&で結合するなどして,読みたいパスを生成すれば良いということがわかった。

2.その部分をテーブルにして名前をつける

テーブルでないものはPowerQueryから読み込めないので,テーブルに変換する。何もしなければ,列名が日本語で「列1」とついてくる。あとは,テーブル名をわかりやすくするためにFilePathと変更している。

3. PowerQueryで読み込む

Letの後に

    FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[列1],

として読み込む。後は,クエリの絶対パスがあるとこをFiePathに帰ればOK。

マイクロソフト様のありがたい仕様書を読んでみたが・・・

Excel.CurrentWorkbookまではわかる

Excel.CurrentWorkbookまでは元のリファレンスを追って,手元で動かしてみればなんとなくわかる。

Excel.CurrnetWorkbookはテーブル名とコンテンツのペアのテーブルを返すらしい。

大量のかっこの規則性がまったくわかんない

https://docs.microsoft.com/en-us/powerquery-m/operators:embed

によるとはRecord lookup operatorで

Access the fields of a record by name.

で,名前でアクセスできることになってて,{}はList indexer operatorで,

{} Access an item in a list by its zero-based numeric index.

で,indexでアクセスできることになっているらしい。

Excel.CurrentWorkbookはテーブルを返す。そっから先がわからない。{}はindexによるアクセスなので,で該当するインデックスをひっぱってきてると考えるのが自然。ただ,ExcelCurrentWorkbookの直後の部分がひっかかる。{}の中にを書いたとき,がインデックスを返すと明確に書かれてる部分が自分には見つけられない。

まあ,仮に{[Name="FilePath"]}の部分を受け入れるとすると,

  1. ExcelCurrentWorkbookでテーブルの一覧を返し
  2. {[Name="FilePath"]}でテーブル名FilePathのNameとContentのペアになったリストが帰し
  3. 2.から[Content]をを抜き出して(だから名前アクセス)
  4. 3.から0行目を抜き出して({}だからindexのアクセス)
  5. 4.から列1という名前の列のものを抜き出す([]だから名前アクセス)

と思われるのだが。今のとここういう適当な理解。