Excel パワークエリでJRAのPOSTページの値を取得エクセルPower QueryでHTMLのソースとTABLEをGet

JRA競馬データ収集取得の質問で

1.ExcelのパワークエリでJRAのページからソースコードを取得したい
2.文字化けしてしまいます
3.DoActionが解りません
(転載不可希望なので、勝手に要約)

とあったので、日曜日なのでチャレンジ。※本当は昨日の土曜日からやってて、進展なし・・・

#msexcel #パワークエリ #Webクエリ #ソースコード #文字化け
#PowerQuery #HTMLソース #TABLE #マイクロソフトエクセル

https://youtube.com/live/0WA7jPsoMWw
目次
00:00 やりたいこと
00:55 1.1 空のクエリに未完成を入力してテスト
02:30 1.2 出力結果をメモ帳へ
03:31 1.3 ""を一つにして貼り付けると
05:37 2.ソースの取得
06:33 2.1 パワークエリでソースの取得は簡単で
08:04 結果が返ったら、※次、ソースの横、丸っぽいオプションを押して
11:07 2.1.1 テストでTABLEをコピーしてみたり
14:12 2.2 シフトJISのページが文字化けする
17:11 2.2.1 文字コードを直そうぜ
21:51 3.DoActionがわかりません
23:20 3.1 実は、commonにjsがある
26:47 3.2 POSTにハマる
31:32 4.オッズのページに飛んだら
32:05 4.1 開催日を選択する
36:57 4.2 レースを選択
42:43 4.3 オッズを取得
45:56 オッズを実際のページをみて確認する取得データが合っているか？
52:19 5.おわりの挨拶

1.できているところまで見せる

00:55 1.1 空のクエリに未完成を入力してテスト
https://www.youtube.com/live/0WA7jPsoMWw?si=_fO1gvkBmHfbZOZK&t=55

データ
データの取得▼
その他のデータソースから
空のクエリ

パワークエリのサンプルを入力

let
    strURL = "https://www.jra.go.jp/JRADB/accessO.html",
    strPARA = Text.ToBinary("cname=pw151ouS307202401061120240324Z/34"),
    strHEAD = [#"Content-Type"="application/x-www-form-urlencoded"],
    ソース = Web.Contents(strURL, [Headers = strHEAD, Content = strPARA]),
    文字列 = Text.FromBinary(ソース, 932),
    開始位置 = Text.PositionOf(文字列, "<table"),
    終了位置 = Text.PositionOf(文字列, "/table"),
    切り取り文字数 = 終了位置 - 開始位置 + 7,
    部分文字列 = Text.Range(文字列, 開始位置,切り取り文字数)
in
    部分文字列

02:30 1.2 出力結果をメモ帳へ

ダブルコーテーション
など、ついてますが、
ここまで、今、できました。

03:31 1.3 ""を一つにして貼り付けると
https://www.youtube.com/live/0WA7jPsoMWw?si=9uWLVF0pwo7WSPrM&t=213

置換(Ctrl+H)で、""を"に置き換えてから
Ctrl+A,Ctrl+Cで全コピー
Excel新規シートへCtrl+Vしてみる

05:37 2.ソースの取得

サンプルURL

HTML表
https://ken3chira4ura.hatenablog.jp/test202403

Post シフトJIS
JRA
https://www.jra.go.jp/

06:33 2.1 パワークエリでソースの取得は簡単で
https://www.youtube.com/live/0WA7jPsoMWw?si=e5ZZd7ki3xPlvms1&t=394

データ
データの取得▼
その他のデータソースから

URLを入れる

08:04 結果が返ったら、※次、ソースの横、丸っぽいオプションを押して
https://www.youtube.com/live/0WA7jPsoMWw?si=ALtbiR8_MSA7k9yb&t=485

HTMLをテキストに変えるだけ。

11:07 2.1.1 テストでTABLEをコピーしてみたり
https://www.youtube.com/live/0WA7jPsoMWw?si=eqGPx69y8h2MQCxe&t=668
蛇足ですが、TABLEタグ/TABLEをシートへコピーすると、
意外と、きれいに貼り付いたり。

14:12 2.2 シフトJISのページが文字化けする
https://www.youtube.com/live/0WA7jPsoMWw?si=jmEDTqqZlnrOOG7X&t=852

JRA
https://www.jra.go.jp/
を同様に取得すると、文字化けする

let
    ソース = Table.FromColumns({Lines.FromBinary(Web.Contents("https://www.jra.go.jp/"))})
in
    ソース

17:11 2.2.1 文字コードを直そうぜ
https://www.youtube.com/live/0WA7jPsoMWw?si=kAsHLjL_e8SUfh6G&t=1032

不思議なことに、再度オプションを開くと、
(HTMLからテキストにしているからなんですが、)

元のファイル・・なんて、項目が、
ここに、
932 日本語シフトJIS
があるので、指定

let
    ソース = Table.FromColumns({Lines.FromBinary(Web.Contents("https://www.jra.go.jp/"), null, null, 932)})
in
    ソース

21:51 3.DoActionがわかりません
https://www.youtube.com/live/0WA7jPsoMWw?si=JIahzCM5HHlG2Av1&t=1311

オッズの部分が

<li><a href="#" onClick="doAction('/JRADB/accessO.html','pw15oli00/6D');return false;">
<span class="icon"><i class="fa fa-caret-right" aria-hidden="true"></i></span>
<span class="txt">オッズ</span></a></li>

となっていて、わかりません。

<link rel="stylesheet" href="/_top/_css/font-awesome.css" />
<link rel="stylesheet" href="/_top/_css/top.css?version=202312" />
<link rel="stylesheet" href="/_top/_css/custom.css?version=20220925" />
<script src="/common/js/common2.js"></script>
<script src="/_top/_js/jquery-3.5.1.min.js"></script>

23:20 3.1 実は、commonにjsがある
https://www.youtube.com/live/0WA7jPsoMWw?si=oWemXIs8ZygW-nYw&t=1400

16 : <script src="/common/js/common2.js"></script>

に、
ここに埋まってます。

中身をみると、

/*****************************************************************************/
//
//  機能：引数として渡されたURLとCNAMEパラメータを元にダミーFORMをSUBMITする。
//  引数：url   遷移先のURL
//      ：cname ProxyCGIに渡すパラメータ
//
/*****************************************************************************/
function doAction( url , cname ){
    //alert('url=['+url + ']' + '\n' + 'cname=['+cname+']');
    //渡されたCNAMEパラメータをhiddenに設定
    document.getElementById( "cname" ).value = cname;
    //alert('cname設定後=['+document.getElementById( "cname" ).value+']');
    //渡されたurlをFORMのACTION属性に設定
    document.getElementById( "commForm01" ).action=url;
    //alert('action設定後=['+document.getElementById( "commForm01" ).action+']');
    //FORMをSUBMITする
    document.getElementById( "commForm01" ).submit();
    return false;
}

と、なっていて、
cnameとURLがわかります。

ここに、POSTすればいいのかな？

26:47 3.2 POSTにハマる
https://www.youtube.com/live/0WA7jPsoMWw?si=_HgW-mhnWVfqTDfS&t=1607

私は、
www.jra.go.jp/JRADB/accessO.html
に
cname=pw15oli00/6D
をPOSTで送り、開きソースコードやテーブルを取得したいだけなんだ

わからなかった。おいおい。

googleで
"Web.Contents" POST
で検索
イロイロみつかりますが、MSのページ
https://learn.microsoft.com/ja-jp/powerquery-m/web-contents
より

例 2
URL に対して POST を実行し、バイナリ JSON ペイロードを渡し、その応答を JSON として解析します。

let
    url = ...,
    headers = [#"Content-Type" = "application/json"],
    postData = Json.FromValue([x = 235.7, y = 41.53]),
    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = postData
        ]
    ),
    jsonResponse = Json.Document(response)
in
    jsonResponse

↑POSTのサンプルがあったけど、
jsonのやりとりかぁ・・・

これを修正して、作成

let
    strURL = "https://www.jra.go.jp/JRADB/accessO.html",
    strPARA = Text.ToBinary("cname=pw15oli00/6D"),
    strHEAD = [#"Content-Type"="application/x-www-form-urlencoded"],
    ソース = Table.FromColumns({Lines.FromBinary(Web.Contents(strURL, [Headers = strHEAD, Content = strPARA]), null, null, 932)})
in
    ソース

ここまで、たどり着きました。
"Content-Type"="application/x-www-form-urlencoded"
と、古いFormのエンコード指定だけど※今風のサンプルはjsonばかり・・・

31:32 4.オッズのページに飛んだら

<a href="#" onclick="return doAction('/JRADB/accessO.html', 'pw15orl00072024010620240324/9C');"><i class="fa fa-chevron-circle-right" aria-hidden="true"></i>1回中京6日</a></div>

32:05 4.1 開催日を選択する
https://www.youtube.com/live/0WA7jPsoMWw?si=lSZehinGj5EZWZET&t=1925

開催日を選びたいので、
日をフィルターする？

②日
をフィルター
Column1

<div class="cell left"><h1><a href="/"><img src="/JRADB/common_d/header/img/logo_jra.png" alt="JRA 日本中央競馬会" /></a></h1></div>
<li><a href="https://www.nvinq.jra.go.jp/jra/servlet/JRAVotereference" target="_blank" title="新規ウインドウで開きます"><span class="icon"><i class="fa fa-external-link-square" aria-hidden="true"></i></span><span class="txt">ネット投票履歴（60日分）</span></a></li>
<li><a href="/facilities/farm/hidaka/"><span class="icon"><i class="fa fa-caret-right" aria-hidden="true"></i></span><span class="txt">日高育成牧場</span></a></li>
  <h3 class="sub_header">3月23日（土曜）</h3>
          <a href="#" onclick="return doAction('/JRADB/accessO.html', 'pw15orl10062024030120240323/E1');"><i class="fa fa-chevron-circle-right" aria-hidden="true"></i>3回中山1日</a></div>
          <a href="#" onclick="return doAction('/JRADB/accessO.html', 'pw15orl10092024020120240323/8E');"><i class="fa fa-chevron-circle-right" aria-hidden="true"></i>2回阪神1日</a></div>
          <a href="#" onclick="return doAction('/JRADB/accessO.html', 'pw15orl10072024010520240323/95');"><i class="fa fa-chevron-circle-right" aria-hidden="true"></i>1回中京5日</a></div>
  <h3 class="sub_header">3月24日（日曜）</h3>
          <a href="#" onclick="return doAction('/JRADB/accessO.html', 'pw15orl00062024030220240324/E8');"><i class="fa fa-chevron-circle-right" aria-hidden="true"></i>3回中山2日</a></div>
          <a href="#" onclick="return doAction('/JRADB/accessO.html', 'pw15orl00092024020220240324/95');"><i class="fa fa-chevron-circle-right" aria-hidden="true"></i>2回阪神2日</a></div>
          <a href="#" onclick="return doAction('/JRADB/accessO.html', 'pw15orl00072024010620240324/9C');"><i class="fa fa-chevron-circle-right" aria-hidden="true"></i>1回中京6日</a></div>
  <h3 class="sub_header">3月23日（土曜）</h3>
                <span class="txt"><span class="race_num"><span>中山11R</span></span>日経賞<span class="grade_icon lg"><img src="/JRADB/img/grade/icon_grade_g2.png" alt="G&#8545;" /></span></span></span></a>
                <span class="txt"><span class="race_num"><span>阪神11R</span></span>毎日杯<span class="grade_icon lg"><img src="/JRADB/img/grade/icon_grade_g3.png" alt="G&#8546;" /></span></span></span></a>
  <h3 class="sub_header">3月24日（日曜）</h3>

開催日のcnameを取得

<a href="#" onclick="return doAction('/JRADB/accessO.html', 'pw15orl00072024010620240324/9C');"><i class="fa fa-chevron-circle-right" aria-hidden="true"></i>1回中京6日</a></div>

pw15orl00072024010620240324/9C

36:57 4.2 レースを選択
https://www.youtube.com/live/0WA7jPsoMWw?si=Nkgc_HTH356mDXeD&t=2217

レースを選びたいので、
フィルターする？
②レース
をフィルター

これで、レースのcnameを取得

<th class="race_num" scope="row"><a onclick="return doAction('/JRADB/accessO.html', 'pw151ouS307202401061120240324Z/34');" href="#"><img alt="11レース" src="/JRADB/img/race_select/btn_race_num11.png"></a></th>

pw151ouS307202401061120240324Z/34

let
    strURL = "https://www.jra.go.jp/JRADB/accessO.html",
    strPARA = Text.ToBinary("cname=pw151ouS307202401061120240324Z/34"),
    strHEAD = [#"Content-Type"="application/x-www-form-urlencoded"],
    ソース = Table.FromColumns({Lines.FromBinary(Web.Contents(strURL, [Headers = strHEAD, Content = strPARA]), null, null, 932)})
in
    ソース

42:43 4.3 オッズを取得
https://www.youtube.com/live/0WA7jPsoMWw?si=AKQG8hrZbqXdsoBv&t=2563

あとは、単純にtableの文字を探しただけです

let
    strURL = "https://www.jra.go.jp/JRADB/accessO.html",
    strPARA = Text.ToBinary("cname=pw151ouS307202401061120240324Z/34"),
    strHEAD = [#"Content-Type"="application/x-www-form-urlencoded"],
    ソース = Web.Contents(strURL, [Headers = strHEAD, Content = strPARA]),
    文字列 = Text.FromBinary(ソース, 932),
    開始位置 = Text.PositionOf(文字列, "<table"),
    終了位置 = Text.PositionOf(文字列, "/table"),
    切り取り文字数 = 終了位置 - 開始位置 + 7,
    部分文字列 = Text.Range(文字列, 開始位置,切り取り文字数)
in
    部分文字列

45:56 オッズを実際のページをみて確認する取得データが合っているか？
https://www.youtube.com/live/0WA7jPsoMWw?si=3AovcQYr6p3_UYyk&t=2756

52:19 5.おわりの挨拶

中途半端ですが、
こんな感じです。
※ですが、テキスト変換しているので、最終目的？のシートへ書き込むときに
　ハマっていたり。

途中ですが、処理のヒントとなれば、幸いです。

※時間オーバーで、JRAのレース、終わってしまいましたね・・・・