Excel関数でスクレイピング
Webページ/システムからデータを取得、Excelに転記するシナリオを扱う方向けの参考情報です。
URLとXPathのリストアップだけでスクレイピングが一瞬で完了するとしたらオイシイですよね。
ゴール目前です。
ゴールされたら回答やコメントでお知らせいただけると嬉しいです。
きっかけのスレッド
漢字やひらがな(2バイト文字)が含まれているかの判定
https://winactor.com/questions/?p=86092
WEBSERVICE関数
Excel URLを指定してデータを取得(WEBSERVICE関数) | 誰でもできる業務改善講座
https://blog-tips.sekenkodqx.jp/2019/07/11/excel-function-webservice00001/
テストA
=WEBSERVICE("https://www.yahoo.co.jp/")
→ HTMLソースが取得できた
思考回路
- URLさえ判ればHTMLが確認できる
- HTMLさえ確認できればXPathを作れる
活用場面例
- URLを把握できる
- ページのソースを表示できない
- デベロッパーツールに制限がかかってる等でXPathのコピーが叶わない
- Excelが使える
FILTERXML関数
Excel XMLにXPathを指定してデータを取得(FILTERXML関数) | 誰でもできる業務改善講座
https://blog-tips.sekenkodqx.jp/2019/07/10/excel-function-filterxml00001/
テストB
=FILTERXML(WEBSERVICE("https://www.yahoo.co.jp/"), "//title")
→ #VALUE! になっちゃう、、（原因は後述★）
リファレンスサイトを眺める限り、この場合の#VALUE!はXMLが正しくないかXPathが正しくないか
引数が2つしかないから当然どちらかなんだろう
テストC
=FILTERXML(XMLのセル番地,XPathのセル番地)
- このXML（HTMLはXMLの一種）なら//spanで要素2つ（「あいうえお」と「かきくけこ」）の配列が取得できた
<html>
<head>
<title>ページタイトル</title>
</head>
<body>
<div>
<span>あいうえお</span>
<span>かきくけこ</span>
</div>
</body>
</html>
- 配列として要素のメンバーを取得した場合の見えかた
Excel関数 | FILTERXML関数：XML文書からXPathが示すデータを取得する
https://www.officepro.jp/excelfunc/web/index3.html
- spanを1つだけにすれば当然1つだけのヒットになる
- spanがいくつあっても、たとえば(//span)[1]とかで一意に特定可能
感じたポテンシャル
- RPAでブラウザを立ち上げて値を取得してSheet1のセルB2以下に順に転記みたいなことが関数のセットだけで済む
- これまで1件ずつループで、最終までページ送りみたいな工夫をしてきた対象が、まるっと取得できる
- 1つのセルだけに入力すれば良い（スピれる）
- メンバーを一挙に釣ることもできるので、要素数分を繰り返さずに済む
要素のメンバーを一括取得してみた
https://winactor.com/questions/?p=63150
- メンバーが順にヒットするXPathを見出すだけで済む
- もちろん(//span)[1]、(//span)[2]みたいな狙い撃ちも可能
- FILTERXML関数はWinActorブラウザ操作の「値の取得」「属性値の取得」「プロパティ値の取得」にリーチできるポテンシャルがある
- //a/@href みたいなXPathも使える
- WinActorでは@属性表記を[]の外で使えるケースは限られている
- WebDriverも拡張機能も使わずに、Excel関数だけで取得系をカバーできそうな予感
- きっとWinActorの「リスト選択取得」「リスト一括取得」「ラジオボタン/チェックボックスオンのVALUEやLABEL取得」相当も可能
- Excelを開いたときに最新情報が取得される
- IEのしくみが裏にありそうな［Webクエリ/Webから］の後継としても有望
- URLやHTMLソースの構造に変化があると、#VALUE!になっちゃう
- 必要に応じて値で上書く運用も検討
位置付けの考察
- FILTERXML(WEBSERVICE("URL"), "XPath")もスクレイピングってことみたい
ライセンスの考察
Microsoft 365 無人ライセンス概要
https://www.microsoft.com/cms/api/am/binary/RE4ELC4
無人 RPA 環境での Microsoft 365 での Office の無人自動化に関する考慮事項 | Microsoft Learn
https://learn.microsoft.com/ja-jp/office/client-developer/integration/considerations-unattended-automation-office-microsoft-365-for-unattended-rpa
【あなたも知らずにライセンス違反！？】RPAでExcelをUnattended型(無人自動化)で使う際の注意点 - Qiita
https://qiita.com/RPAbot/items/db0e6ae261fd4ff72619
- あるいは人が毎回スイッチを操作するか
★後回しにした、Yahoo!JAPANの//titleが取得できなかった症状の原因
- 開始タグに対応する終了タグがないため、第1引数がXMLとして無効
このサンプルコードではやはりXPathの値が取れない
HTMLタグ/フレームタグ/フレーム内にページを表示する - TAG index
https://www.tagindex.com/html_tag/frame/frame_src.html
- 終了タグのないframeタグの2行を省いたらXPathの値が想定通りに取れた
- 開始タグに対応する終了タグがないため、WEBSERVICEの結果がXMLのテイをなしていなかった
- 終了タグのないHTMLはXMLの基準を満たさない
XMLは厳格という情報
XML用語事典 [終了タグ]
https://atmarkit.itmedia.co.jp/aig/01xml/endtag.html
XMLの記述ルール | DTPサポートサービス
http://dtp.screen-cre.co.jp/xml0004/要素の終端を「 />」と記述することで終了タグを省略できます。
HTMLでは終了タグが無くても表示できますが、XMLではエラー扱いになって全く動作しなくなります。
タグは必ず閉じなければいけないの？ | ホームページ制作つくばアドワークス｜茨城県
https://adworks24.co.jp/archives/549.html
閉じタグを考える - Qiita
https://qiita.com/jkr_2255/items/5fad618df2501dba180f
XMLの文法 - データ記述とXML
https://www.mlab.im.dendai.ac.jp/~yamada/web/xml/syntax.html
ここまで把握したうえで、、
順当なスタンス
- FILTERXML関数はRSS等、厳格にXMLの記述ルールが守られているブツに対してのみ使う
ワークシートにWEBSERVICEとFILTERXML関数を追加する | GrapeCity.devlog - グレープシティ株式会社
https://devlog.grapecity.co.jp/diodocs-excel-webservice-filterxml/
- FILTERXML関数はHTMLに対しては使わない
- RSSとかXMLを扱う場面はそう多くないはずだから、実質的にFILTERXML関数のポテンシャルを捨てる判断になる
- MOTTAINAI
チャレンジングなスタンス
- ない終了タグを補ってFILTERXML関数を使いたい
思いを馳せる
- ない終了タグだけ一気に一瞬で補う方法が見つかるかどうか
- さすがにWebを作る側のノウハウばかり
- 閲覧する側/使う側はブラウザがよしなに処理してくれれば、タグなんて意識することはない
HTMLで閉じタグが補完される訳~HTMLパーサーを読む~ - Qiita
https://qiita.com/P_cablin321/items/57f44a2732079cac8ce1
- ブラウザ上で補完ではない
- あくまでもExcel上で、あるいはテキストとかVBAの変数上で補完する方法が見つかるか思いつくかどうか
- frameみたいな古いタグも含めて一律終了タグを補完
- pもbrも
- tbodyなしをブラウザが補ってくれるのと同じように、作り手の閉じ忘れもブラウザが補ってくれてるのだろうか
- WEBSERVICE関数で取得されるHTMLが、終了タグが補われた状態なら、、frame/p/br/img周辺のいくつかを補完するだけで済みそう
- HTML→XHTML変換を経れば良いのかも
- 少し古めの情報
HTML4.01からXHTML1.0に変換する－HTMLの基本
https://www.htmq.com/htmlkihon/102.shtml
- 20年近く前の情報
HTMLをXHTMLに変換するツール｜企画マーケティング部（システム開発のアイロベックス｜東京都新宿区の業務システム開発会社）
https://www.ilovex.co.jp/Division/PMD/2005/06/htmlxhtml.html
- 残念なことに、リンク先が死んでる
- 新しめだけど、HTML5はXHTMLじゃないという情報
XHTMLをHTML5に一括置換する方法 - web覚書
https://singoro.net/note/html5-from-xhtml/
- 何やら核心っぽい情報
VBAでHTMLに対してXPathで取得する良い方法ないですか？
https://teratail.com/questions/43626
- ここを理解するには、今のわたしではあと3年くらいかかりそう、、
- WEBSERVICE関数が効く、XMLの要件も満たしてる、終了タグが揃ってるサイト/ページ/HTMLもある
- どれくらいあるのか
- このポテンシャルを諦めるのはもったいない、
時間を見つけてもう少し深堀ってみようと思います。
「これだけだからこうしちゃえば良いんじゃない？」とか「この正規表現で補完できるはず」なズバリとかヒントがあればお知らせいただけると嬉しいです。
HTMLタグを掴む正規表現はある。
開始タグに対する終了タグがないパターンで、終了タグを追記。
正規表現で何とかなりそうな気がしてます。
今のわたしでは3か月コースかも、、
、、と思いきや、サクラエディタでこれでいけちゃったみたいです。
置換前：\<(frame|br|p|img)(.*)\>
置換後：<\1\2></\1>
- 回答欄で半角￥はバックスラッシュに置き換わっちゃうみたい
（機能は同じはずなので実用上影響はないと思うものの、念のため）
BEFORE
<frame name="aaa">
<span>あああ</span>
<br>
<p id="bbb">
<img title="ばば">
AFTER
<frame name="aaa"></frame>
<span>あああ</span>
<br></br>
<p id="bbb"></p>
<img title="ばば"></img>
現実的になってきた段取り
- HTMLソースを取得
=WEBSERVICE("https://www.yahoo.co.jp/") - 終了タグを補完*
- XPathの値を表示
=FILTERXML(終了タグを補完してXMLとして通用するHTML, "//title")
正規表現で終了タグ補完の模索
- VBAでユーザー定義関数を作るか、、
- VBAのノウハウで置換用メタ文字が使える例を見かけない
正規表現サンプル集
https://www.megasoft.co.jp/mifes/seiki/meta.html
- サクラエディタのマクロファイルを併用することになるか
- VBAからのコマンド実行でサクラのマクロを実行
- Excelのユーザー定義関数内で、サクラのマクロを実行した結果を拾う
目指せるかどうか、、
終了タグの補完は避けて通れない。
頑張る価値はありそう。
今日は力尽きたのでここまで。
「こうしちゃえば？」「できたYO！」等、アドバイスやヒントをいただけると嬉しいです。
、、と思いきや、Excelの正規表現（VBSのRegExp）でも置換用メタ文字が使えるみたいです。
VBAで正規表現を利用する（RegExp）｜VBA技術解説
https://excel-ubara.com/excelvba4/EXCEL232.html#sec08Replaceの使用例
$numberのキャプチャグループ
Excelではこれでいけちゃうのかも
置換後：<$1$2></$1>
これができれば、、
- XMLとしても機能するHTMLが得られる
- Excelだけで一瞬スクレイピングのしくみができあがる
時間を見つけて試します。
「試してみた」「こんなメリットもある」「ここがイマイチ」等、お寄せいただけると嬉しいです。
anothersolution 新しいコメントを投稿
回答とコメントは、会員登録(無料)で閲覧できるようになります。
新規登録