Solved: RSS to CSV on FTP help

RSS to CSV on FTP help

Hello,

I'm attempting to grab rss from a gaging station to read the latest observation. Unfortunately this is proving to be very difficult. This isn't an rss feed that has updated "items" but instead the same item, just with updated values on a schedule. So I start my flow with a schedule that matches what I know the update schedule is, around 30 minutes. Here's a link to the gage rss. The latest observation is buried here:

<description>&#x3C;h2&#x3E;Flood Categories&#x3C;/h2&#x3E;
&#x3C;h4&#x3E;&#x3C;u&#x3E;Primary (ft)&#x3C;/u&#x3E;&#x3C;/h4&#x3E;
&#x3C;ul&#x3E;&#x3C;li&#x3E;Action : 38 ft&#x3C;/li&#x3E;
&#x3C;li&#x3E;Minor : 41 ft&#x3C;/li&#x3E;
&#x3C;li&#x3E;Moderate : 44 ft&#x3C;/li&#x3E;
&#x3C;li&#x3E;Major : 48 ft&#x3C;/li&#x3E;
&#x3C;/ul&#x3E;
&#x3C;h4&#x3E;&#x3C;u&#x3E;Secondary (kcfs)&#x3C;/u&#x3E;&#x3C;/h4&#x3E;
&#x3C;ul&#x3E;&#x3C;li&#x3E;Action : Not Set&#x3C;/li&#x3E;
&#x3C;li&#x3E;Minor : Not Set&#x3C;/li&#x3E;
&#x3C;li&#x3E;Moderate : Not Set&#x3C;/li&#x3E;
&#x3C;li&#x3E;Major : Not Set&#x3C;/li&#x3E;
&#x3C;/ul&#x3E;
&#x3C;h2&#x3E;Gauge Data&#x3C;/h2&#x3E;
&#x3C;div&#x3E;Latest Observation Category: Normal&#x3C;/div&#x3E;
&#x3C;div&#x3E;Latest Observation: 17.51 ft&#x3C;/div&#x3E;
&#x3C;div&#x3E;Latest Observation (Secondary): -999 kcfs&#x3C;/div&#x3E;
&#x3C;div&#x3E;Observation Time: Aug 1, 2017 09:00 AM -0400&#x3C;/div&#x3E;

&#x3C;/br&#x3E;
</description>

My flow starts with a schedule, then lists items, and then that's where I'm lost. I follow it up with an apply to each and html to text, but I have no understanding on how to apply string functions in this tool and further parse what I need. In the destination, I have a csv on an ftp that needs the latest observation and the observation time. That's it. Should I be using SSIS instead? I was hoping to go "serverless" for this and prove that this could be done without on-prem ETL tools. Please prove me wrong!

Hi Projected,

This can definitely be solved via Serverless but will likely require Azure Logic Apps + Functions which is what would be recommended.

Here’s some details:

1. The RSS feed you provided has a description that is XML Encoded. That means to pull out values you likely need to XML Decode.
2. The two values you specified (observation and observation time) will need to be extracted from the XML Decoded

You likely need an Azure Function to do both of those. I wrote this C# HttpTrigger Azure Function that does 1 and 2 (using a basic regular expression – bottom of email). You pass in the “description” from the RSS feed and it returns and object like this:

{"observation":"13.55 ft","time":"Aug 22, 2017 03:00 PM -0400"}

The Logic App is likely:
• RSS Trigger (trigger on new RSS items)
• Call the Azure Function using the Logic Apps Function connector
• Parse JSON to parse the function response (sample above)
• FTP Get current file
• FTP Update file – upload the FTP content but add a CSV row at the bottom like:
o [observation], [observationTime]

using System.Net;
using System.Text.RegularExpressions;

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
log.Info("C# HTTP trigger function processed a request.");

// Get request body
string encodedXML = await req.Content.ReadAsAsync<string>();
string decodedXML = System.Net.WebUtility.HtmlDecode(encodedXML);

string pattern = "Latest Observation: (.*?)<\\/div>[\\S\\s]*Observation Time: (.*?)<\\/div>";
Match match = Regex.Match(decodedXML, pattern);

return req.CreateResponse(HttpStatusCode.OK, new {observation = match.Groups[1].Value, time = match.Groups[2].Value} );
}

Hope this could be a reference for you.

Best regards,
Mabel Mao

Quick Links