scraping table in web page 3

[sal21]

based:

https://demo.istat.it/app/?i=D7B&l=it&a=2024

to have the result:

1) click on button: Vista Territoriale
2) click on radio button: Tutti i comuni della provincia selezionata (Seleziona la provincia)
3) choice Como
4) click on Listbox mese
choice Luglio
$) click on button Cerca

Appear a table with data.

I need to looping all the value of the table result.

Possible?
Tks

 

[strongm]

BTW, not quite sure why SoftwareRT has suggested scraping the page with Selenium, since the whole point of my suggestion of this alternative was that you'd be able to 'Export CSV'.

So here's my code for doing just that (albeit using the Edgedriver rather than the Chromedriver)

Sub TEST_Edge()
    Dim BOT As New EdgeDriver

    BOT.Start "Edge", ""
    BOT.Get "https://demo.istat.it/app/?i=D7B&l=it&a=2024" ' Get is synchronous, adoids all that troublesome WAITing for all the page to load
    
    BOT.FindElementById("tab-1").Click
    BOT.FindElementById("provincerb-1").Click
    
    ' Select from dopdown by index value
    ' BOT.FindElementByXPath("//select[@id='mese']/option[@value='7']").Click
    
    ' Select from dopdown by name
    BOT.FindElementByXPath("//select[@id='mese']//option[. = 'Luglio']").Click
    
    BOT.FindElementById("btnricerca-1").Click ' retrieve table for selected options
    
    BOT.FindElementByCss(".buttons-csv > span").Click ' Download it
    BOT.Wait 500 ' allow time for file to successfully download. May need tweaking
End Sub

 

[sal21]

For strongm.
Finally i decide to use the code "GetJsonData()", in effect the Json return all info for my project.
But a dubt...

after a send copmmend, is require a check for response, similar i use IE:

Do While IE.Busy: DoEvents: Loop
Do Until IE.readyState = 4: DoEvents: Loop

 

[strongm]

>decide to use the code "GetJsonData()"

Whilst I am naturally happy that you have returned to this, may I ask why? Did my Selenium-based code above not work for you?

>the Json return all info for my project

The json returned by my code is the json that is used to dynamically build the table on the page, it isn't 'all info'

>is require a check for response
Sure. If you switch

Set objHttp = CreateObject("MSXML2.XMLHTTP")toSet objHttp = CreateObject("MSXML2.ServerXMLHTTP")

then you'll have access to a ReadyState property - but it is better to use the WaitForResponse method that you also get access to.

Here are the changes you would need to make to my code:

Public Function GetJsonData() As String
    Dim objHttp As Object
    Dim myHTMLDoc As New HTMLDocument
    
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")

    objHttp.Open "POST", "https://demo.istat.it/app/RPCCerca.php", True ' this is what clicking Cerva runs, async call
    objHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded" ' tell server we are submitting form data
    objHttp.send "territorio=procom&province=084&mese=7&hid-i=D7B&hid-a=2024&hid-l=it&hid-cat=D7B&hid-dati=dati-form-1&hid-tavola=tavola-form-1" 'parameters submitted from the form. 

    Debug.Print IIf(objHttp.waitForResponse(5), "Success", "Timed out") ' Max number of seconds we wait for a timeout

    GetJsonData = objHttp.responseText

End Function

 

[sal21]

For strongm
I a json file is "denominazione":"Agli\u00e8" instead Agliè, peraphs is a problem of Unicode?

 

[strongm]

>Agli\u00e8" instead Agliè, peraphs is a problem of Unicode?

That's in the raw JSON string that is returned, yes? Part of the JSON standard. You really need to pass it through a JSON decoder (as we have previously shown in this forum) to recover the 'correct' strings, such as

Public Function DecodeJsonString(ByVal JsonString As String)
Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")")
End Function

It is a little harder to then walk through the returned JSON object hierarchy in VB. So ... maybe write your own code to strip out and replace the character encoding