Web data to Nodered

[gorem...@gmail.com]

I am new to this web scraping. How do i get these data to node-red  from this link http://publicinfobanjir.water.gov.my/View/OnlineFloodInfo/PublicWaterLevel.aspx?scode=SEL

Do i need to use a 3rd party software? 

Thank you

[gorem...@gmail.com]

It's a live data and i need to trigger an event if water level above the normal level. 

[Dave C-J]

Hi

this simple flow should get you started

[{"id":"6356e541.11a4dc","type":"inject","z":"f03b57d5.e525f8","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":120,"y":1200,"wires":[["f10c4c74.0c258"]]},{"id":"f10c4c74.0c258","type":"http request","z":"f03b57d5.e525f8","name":"","method":"GET","ret":"txt","url":"http://publicinfobanjir.water.gov.my/View/OnlineFloodInfo/PublicWaterLevel.aspx?scode=SEL","tls":"","x":290,"y":1200,"wires":[["6deab1bb.58e8a"]]},{"id":"6deab1bb.58e8a","type":"html","z":"f03b57d5.e525f8","name":"","tag":"table[class=Grid]","ret":"html","as":"multi","x":490,"y":1200,"wires":[["4eb5946f.7eae7c"]]},{"id":"4eb5946f.7eae7c","type":"html","z":"f03b57d5.e525f8","name":"","tag":"tr","ret":"html","as":"single","x":690,"y":1200,"wires":[["80bd91fa.77ce5"]]},{"id":"80bd91fa.77ce5","type":"debug","z":"f03b57d5.e525f8","name":"","active":true,"complete":false,"x":850,"y":1200,"wires":[]}]

It gets the web page - then slices out the relevant table, then puts each row into an array.

You can then slice these up however you want.

[gorem...@gmail.com]

Hi

Thank you for the flows. I have tried them but i do not know how to proceed further . Is there any prebuild node that can make it more readable? Basically the output should be like this . 

Thank you

STATE :  SELANGOR

Sg.Bernam di Jambatan SKC Hulu Selangor, SELANGOR  Date: 26/12/2017 23:15 Water Level 18.23 m has exceeded the ALERT!   level 18.10 m by 0.13m WL Trend : No Change

[Julian Knight]

OK, so I think the real problem is converting from an HTML table to JSON data for processing. This is certainly a non-trivial task. All of the examples I have found make use of JQuery or a similar node.js equivalent such as Cheerio.

While there is a cheerio based node, as far as I can see, its configuration interface is too simplistic to be useful here and there are no examples to help.

The format of the table is quite complex - the easiest approach unfortunately would seem to be to install the cheerio npm package into ~/.node-red, reference it to a global variable in settings.js and then reference that in a function node to extract the data to JSON. There are a number of examples on the web of how to extract a table to JSON using JQuery which should work with Cheerio.

This certainly seems to highlight a need for a new node to do this as it is a very common requirement for any data processing task.

This is going to annoy me now as this really should be the kind of thing that Node-RED should be good for.

[Dave C-J]

Julian

The example I gave sliced up the page ok.  It does now need to be re-rendered back into HTML.  Which is fairly standard template stuff. 

--

Sent from phone.

[Julian Knight]

Sorry Dave but I don't believe it gives the level of information that he needs to do what he indicated. I tried it and it returned an array of rows so each row would have to be processed and then everything put back together - not easy in itself.

Here is my first take on a function node that uses cheerio to parse an html table - set to the table in question. You only need the inject and request nodes from your example - obviously, you also have to add a global reference to the cheerio module.

const tableSelector = '#ContentPlaceHolder1_grdStation'


const cheerio = global.get('cheerio')
const $ = cheerio.load(msg.payload)
const options = {
    rowForHeadings: 0,  // extract th cells from this row for column headings (zero-based)
    ignoreHeadingRow: true, // Don't tread the heading row as data
    ignoreRows: [],
}
const jsonReponse = []
const columnHeadings = []


$(tableSelector).each(function(i, table) {
    var trs = $(table).find('tr')
   
    // Set up the column heading names
    getColHeadings( $(trs[options.rowForHeadings]) )


    // Process rows for data
    $(table).find('tr').each(processRow)
})


msg.payload = {
    columnHeadings: columnHeadings,
    rows: jsonReponse,
}


return msg;


function getColHeadings(headingRow) {
    const alreadySeen = {}
   
    $(headingRow).find('th').each(function(j, cell) {
        let tr = $(cell).text().trim()
       
        if ( alreadySeen[tr] ) {
            let suffix = ++alreadySeen[tr]
            tr = `${tr}_${suffix}`
        } else {
            alreadySeen[tr] = 1
        }
       
        columnHeadings.push(tr)
    })
}


function processRow(i, row) {
    const rowJson = {}
   
    if ( options.ignoreHeadingRow && i === options.rowForHeadings ) return
    // TODO: Process options.ignoreRows
   
    $(row).find('td').each(function(j, cell) {
        rowJson[ columnHeadings[j] ] = $(cell).text().trim()
    })
   
    // Skip blank rows
    if (JSON.stringify(rowJson) !== '{}') jsonReponse.push(rowJson)
}


//EOF

[gorem...@gmail.com]

HI

Can you share the full flows? I m a newbie. 

Thank you

[Julian Knight]

Sorry, I've been travelling. I will try to do something today. I think I will be turning this into a node when I get a chance.

[Dave C-J]

We do have an HTML node (based on Cheerio) - but it's a PITA to be honest.... so if there was a way of improving that - that would be good.

[Nick O'Leary]

Dave,

I'm sure the maintainers of the html node would love to get some feedback on the pains it presents you. I'm not aware of any issues, and nothing appears on the whiteboard for looking at it.

Nick

On Fri, 29 Dec 2017, 12:45 Dave C-J, <dce...@gmail.com> wrote:

We do have an HTML node (based on Cheerio) - but it's a PITA to be honest.... so if there was a way of improving that - that would be good.

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/CACXWFw%2BjX9mPevoG0FXJNzm4gbYDNDdm-3o_8%3DhXm1AMrE%3DORQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Julian Knight]

I'll admit to having not gotten around to really using that in anger for anything complex. But clearly the html node if very simplistic.

That is great for people just getting going and I am not at all criticising it. But it certainly doesn't provide the capability to extract anything reasonably complex as we've seen from this thread. However, it works well at getting rid of any outer cruft before going on to process the innards.

In truth, I'm not sure how you would change the node to allow more options since, looking at how Cheerio works, it is clearly using a separate grammar - great for writing into code, not so good for translating into something visual.

If anyone can suggest a way to fold a table extractor into it, I'd be happy to create a PR. But currently, for simplicity, I think creating a simple table extractor node is going to be best. As always, open to suggestions though.

[Julian Knight]

Right, so here is the first part of the answer as a flow:

[{"id":"815bf28f.d0691","type":"inject","z":"e7463dd2.db517","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":140,"y":100,"wires":[["46f8420f.a312dc"]]},{"id":"46f8420f.a312dc","type":"http request","z":"e7463dd2.db517","name":"","method":"GET","ret":"txt","url":"http://publicinfobanjir.water.gov.my/View/OnlineFloodInfo/PublicWaterLevel.aspx?scode=SEL","tls":"","x":290,"y":100,"wires":[["d0660df5.4f339"]]},{"id":"fe447848.e25938","type":"debug","z":"e7463dd2.db517","name":"","active":true,"complete":false,"x":602.0000228881836,"y":99.50000262260437,"wires":[]},{"id":"d0660df5.4f339","type":"function","z":"e7463dd2.db517","name":"","func":"const tableSelector = '#ContentPlaceHolder1_grdStation'\n\nconst cheerio = global.get('cheerio')\nconst $ = cheerio.load(msg.payload)\nconst options = {\n    rowForHeadings: 0,  // extract th cells from this row for column headings (zero-based)\n    ignoreHeadingRow: true, // Don't tread the heading row as data\n    ignoreRows: [],\n}\nconst jsonReponse = []\nconst columnHeadings = []\n\n$(tableSelector).each(function(i, table) {\n    var trs = $(table).find('tr')\n    \n    // Set up the column heading names\n    getColHeadings( $(trs[options.rowForHeadings]) )\n\n    // Process rows for data\n    $(table).find('tr').each(processRow)\n})\n\nmsg.payload = {\n    columnHeadings: columnHeadings,\n    rows: jsonReponse,\n}\n\nreturn msg;\n\nfunction getColHeadings(headingRow) {\n    const alreadySeen = {}\n    \n    $(headingRow).find('th').each(function(j, cell) {\n        let tr = $(cell).text().trim()\n        \n        if ( alreadySeen[tr] ) {\n            let suffix = ++alreadySeen[tr]\n            tr = `${tr}_${suffix}`\n        } else {\n            alreadySeen[tr] = 1\n        }\n        \n        columnHeadings.push(tr)\n    })\n}\n\nfunction processRow(i, row) {\n    const rowJson = {}\n    \n    if ( options.ignoreHeadingRow && i === options.rowForHeadings ) return\n    // TODO: Process options.ignoreRows\n    \n    $(row).find('td').each(function(j, cell) {\n        rowJson[ columnHeadings[j] ] = $(cell).text().trim()\n    })\n    \n    // Skip blank rows\n    if (JSON.stringify(rowJson) !== '{}') jsonReponse.push(rowJson)\n}\n\n//EOF","outputs":1,"noerr":0,"x":430,"y":100,"wires":[["fe447848.e25938"]]}]

That outputs an object that contains 2 arrays. The first is an array of the heading names, the second is an array of objects, one entry for each of the returned stations.

The next part of the answer would be to turn that into your Dashboard. To do that, unless you only want a single station, we would need to understand how you want multiple stations shown in the Dashboard.

If you only want a single station, you would need to know what row it is - there are ways to search for a specific entry but you'd need to let me know how you would identify the station you want (e.g. by Station ID). But in simple terms, something like payload.rows[40] would return the 40'th row and then each property of the object is accessed like:  payload.rows[40].District or payload.rows[40]['Station ID'] for properties with names containing spaces.

As a bonus, here is an extended version of the above but with 2 different methods of extracting or manipulating the data. In addition, it has a small caching function that speeds up development since you don't have to keep re-requesting the data from the server each time:

[{"id":"815bf28f.d0691","type":"inject","z":"e7463dd2.db517","name":"get data","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":120,"y":100,"wires":[["46f8420f.a312dc"]]},{"id":"46f8420f.a312dc","type":"http request","z":"e7463dd2.db517","name":"","method":"GET","ret":"txt","url":"http://publicinfobanjir.water.gov.my/View/OnlineFloodInfo/PublicWaterLevel.aspx?scode=SEL","tls":"","x":330,"y":100,"wires":[["808d9c98.e305d"]]},{"id":"fe447848.e25938","type":"debug","z":"e7463dd2.db517","name":"","active":true,"complete":false,"x":810,"y":60,"wires":[]},{"id":"d0660df5.4f339","type":"function","z":"e7463dd2.db517","name":"","func":"/*jshint sub:true,asi:true,maxerr:1000*/\n\nconst tableSelector = '#ContentPlaceHolder1_grdStation'\n\nconst cheerio = global.get('cheerio')\nconst $ = cheerio.load(msg.payload)\nconst options = {\n    rowForHeadings: 0,  // extract th cells from this row for column headings (zero-based)\n    ignoreHeadingRow: true, // Don't tread the heading row as data\n    ignoreRows: [],\n}\nconst jsonReponse = []\nconst columnHeadings = []\n\n$(tableSelector).each(function(i, table) {\n    var trs = $(table).find('tr')\n    \n    // Set up the column heading names\n    getColHeadings( $(trs[options.rowForHeadings]) )\n\n    // Process rows for data\n    $(table).find('tr').each(processRow)\n})\n\nmsg.payload = {\n    columnHeadings: columnHeadings,\n    rows: jsonReponse,\n}\n\nreturn msg\n\nfunction getColHeadings(headingRow) {\n    const alreadySeen = {}\n    \n    $(headingRow).find('th').each(function(j, cell) {\n        let tr = $(cell).text().trim()\n        \n        if ( alreadySeen[tr] ) {\n            let suffix = ++alreadySeen[tr]\n            tr = `${tr}_${suffix}`\n        } else {\n            alreadySeen[tr] = 1\n        }\n        \n        columnHeadings.push(tr)\n    })\n}\n\nfunction processRow(i, row) {\n    const rowJson = {}\n    \n    if ( options.ignoreHeadingRow && i === options.rowForHeadings ) return\n    // TODO: Process options.ignoreRows\n    \n    $(row).find('td').each(function(j, cell) {\n        rowJson[ columnHeadings[j] ] = $(cell).text().trim()\n    })\n    \n    // Skip blank rows\n    if (JSON.stringify(rowJson) !== '{}') jsonReponse.push(rowJson)\n}\n\n//EOF","outputs":"1","noerr":0,"x":650,"y":100,"wires":[["fe447848.e25938","c35dcf95.5cc9","641996d3.fe8118"]]},{"id":"641996d3.fe8118","type":"change","z":"e7463dd2.db517","name":"","rules":[{"t":"set","p":"selected","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"selected.station","pt":"msg","to":"payload.rows[40]['Station ID']","tot":"msg"},{"t":"set","p":"selected.level","pt":"msg","to":"payload.rows[40]['River Level (m)']","tot":"msg"},{"t":"move","p":"selected","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":100,"wires":[["f0152260.70e1d"]]},{"id":"f0152260.70e1d","type":"debug","z":"e7463dd2.db517","name":"","active":true,"complete":false,"x":990,"y":100,"wires":[]},{"id":"c35dcf95.5cc9","type":"change","z":"e7463dd2.db517","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.rows.(\t   {\t       \"station\": $.'Station ID',\t       \"name\": $.'Station Name',\t       \"level\": $.'River Level (m)',\t       \"alert\": $number($.'River Level (m)') > $number($.Alert) ? true : false\t   }\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":820,"y":140,"wires":[["d505285a.26bcb8"]]},{"id":"d505285a.26bcb8","type":"debug","z":"e7463dd2.db517","name":"","active":true,"complete":false,"x":990,"y":140,"wires":[]},{"id":"808d9c98.e305d","type":"function","z":"e7463dd2.db517","name":"cache","func":"/*jshint sub:true,asi:true,maxerr:1000*/\n// Expects input msgs with topic set \n\nconst contextVarName = 'httpReqMsgs' // homeMsgs\n\n// saved context\nvar cachedMsgs = context.get(contextVarName) || {}\n\n// Only send to single client if needed\nvar socketId = null\nif ( msg.hasOwnProperty('_socketId') ) {\n    socketId = msg._socketId\n}\n\n// Replay cache if requested\nif ( msg.hasOwnProperty('cacheControl') && msg.cacheControl.toUpperCase() === 'REPLAY' ) {\n    for (var topic in cachedMsgs) {\n        let newMsg = {\n            \"topic\": topic, \n            \"payload\": cachedMsgs[topic]\n        }\n        // Only send to a single client if we can\n        if ( socketId !== null ) newMsg._socketId = socketId\n        node.send(newMsg)\n    }\n    return null\n}\n// -- else if --\n// Empty cache if requested\nif ( (msg.hasOwnProperty('cacheControl') && msg.cacheControl === 'RESET')  ||\n     (msg.payload.hasOwnProperty('cacheControl') && msg.payload.cacheControl === 'RESET') ) {\n    cachedMsgs = {}\n    context.set(contextVarName, cachedMsgs)\n    return null\n}\n// -- else --\n\n// ignore cacheControl and uibuilder control messages\nif ( msg.hasOwnProperty('cacheControl') || msg.hasOwnProperty('uibuilderCtrl') ) return null\n\n// Add a counter for each device name\nif ( msg.topic.endsWith('$name') ) {\n    let topic = msg.topic.replace('$name', '$count')\n    let count = cachedMsgs[topic] || 0\n    count = count + 1\n    cachedMsgs[topic] = count\n    let newMsg = {\n        \"topic\": topic, \n        \"payload\": count\n    }\n    // Only send to a single client if we can\n    if ( socketId !== null ) newMsg._socketId = socketId\n    node.send(newMsg)\n}\n\n// Keep the last msg.payload by topic\ncachedMsgs[msg.topic] = msg.payload\n\n// save context for next time\ncontext.set(contextVarName, cachedMsgs)\n\nreturn msg;","outputs":1,"noerr":0,"x":510,"y":100,"wires":[["d0660df5.4f339","c0cacccb.9e3c7"]]},{"id":"afc218de.706f88","type":"inject","z":"e7463dd2.db517","name":"replay","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"x":110,"y":160,"wires":[["eddcc57e.277418"]]},{"id":"c0cacccb.9e3c7","type":"debug","z":"e7463dd2.db517","name":"","active":false,"complete":"true","x":650,"y":180,"wires":[]},{"id":"eddcc57e.277418","type":"change","z":"e7463dd2.db517","name":"","rules":[{"t":"set","p":"cacheControl","pt":"msg","to":"REPLAY","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":160,"wires":[["808d9c98.e305d"]]}]

The "Change 4 rules" change node extracts a subset of a single row of the table. The "set msg.payload" change node uses JSONata to show how you could simplify the data for all rows in a single statement, it also adds a flag which is true if the current river level is above the "alert" level.

[gorem...@gmail.com]

Thank you Julian for the flows but i have errors. First error was from the 1st flow and 2nd from the one below .  

[Julian Knight]

You will need to go through your function nodes and check them for problems. Quite possibly a small typo or something like that.

A couple of hints for debugging:

• Change the node name settings for each function and debug node so that it is obvious which node errors and debug output are from even in an image.
  
  
• Inside a function that is giving an error, you might need additional debugging output in order to identify where in the function code the error occurs. You can use "node.warn('position 1')" or similar statements throughout the code.

[Colin Law]

In Julian's flow cheerio is set to global.get('cheerio') then the code cheerio.load(msg.payload).  The errors you are getting say the you are calling load on an undefined variable, suggesting that the global context variable cheerio has not been setup.  Perhaps you have not set up the global variable at the time the function is being called.  I have no idea what the flow is actually trying to do so I don't know what the significance of that variable is.

Colin

--

http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.

To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/cda4efed-8cee-4a2c-9af2-b53c2ffb3321%40googlegroups.com.

[Julian Knight]

Good spot Colin, yes that is quite likely.

The flow is getting a page from the web that contains a fairly complex HTML table. My example flows extract the table into a JavaScript object and demonstrate how to select data from it. The lower flow has a caching function that simply lets you avoid having to requery the web all the time (which is quite slow).