topic Re: Web scraping/regex in Alteryx Designer Desktop Discussions

Web scraping/regex

MTMjames — Thu, 13 Jun 2024 21:32:27 GMT

Hi all,

I watched a webinar on this and thought it would be easy... I'm trying to download some data that is held in a table on a website. I have got as far as downloading the data, but am stuck on how to extract what I want out of that data. The source code looks like:

<div>
	<table cellspacing="0" border="0" id="ctl00_MainContent_ucFinancialComplianceTable_gdvFinancialAndComplianceHistory" style="border-width:0px;border-style:None;border-collapse:collapse;">
	<tr>
	<th class="GridHeading" scope="col" style="width:146px;">Financial year end (FYE)</th><th class="GridHeading" scope="col" style="width:73px;">Income</th><th class="GridHeading" scope="col" style="width:73px;">Spending</th><th class="GridHeading" scope="col" style="width:146px;">Accounts received</th><th class="GridHeading" scope="col" style="width:146px;">Annual Return received</th><th class="GridHeading" scope="col" style="width:73px;">View</th>
	</tr><tr style="background-color:#FFFF99;">
	<td class="GridColumnText" style="width:146px;">31 Aug 2018</td><td class="GridColumnNumeric" style="width:73px;">£1,313,932</td><td class="GridColumnNumeric" style="width:73px;">&#163;1,312,739</td><td class="GridColumnText" style="width:146px;">30 Jun 2019</td><td class="GridColumnText" style="width:146px;">30 Jun 2019</td><td class="GridColumnText" style="width:73px;"><a title='31 Aug 2018 Accounts PDF' target='_blank' href='/Accounts/Ends74\0000267174_AC_20180831_E_C.pdf'>Accounts</a></td>
	</tr><tr style="background-color:#FFFFCC;">
	<td class="GridColumnText" style="width:146px;">31 Aug 2017</td><td class="GridColumnNumeric" style="width:73px;">£1,166,743</td><td class="GridColumnNumeric" style="width:73px;">£1,127,987</td><td class="GridColumnText" style="width:146px;">05 Jun 2018</td><td class="GridColumnText" style="width:146px;">05 Jun 2018</td><td class="GridColumnText" style="width:73px;"><a title='31 Aug 2017 Accounts PDF' target='_blank' href='/Accounts/Ends74\0000267174_AC_20170831_E_C.pdf'>Accounts</a></td>
	</tr><tr style="background-color:#FFFF99;">
	<td class="GridColumnText" style="width:146px;">31 Aug 2016</td><td class="GridColumnNumeric" style="width:73px;">£1,169,515</td><td class="GridColumnNumeric" style="width:73px;">£1,159,754</td><td class="GridColumnText" style="width:146px;">13 Jun 2017*</td><td class="GridColumnText" style="width:146px;">13 Jun 2017</td><td class="GridColumnText" style="width:73px;"><a title='31 Aug 2016 Accounts PDF' target='_blank' href='/Accounts/Ends74\0000267174_AC_20160831_E_C.pdf'>Accounts</a></td>
	</tr><tr style="background-color:#FFFFCC;">
	<td class="GridColumnText" style="width:146px;">31 Aug 2015</td><td class="GridColumnNumeric" style="width:73px;">£1,193,076</td><td class="GridColumnNumeric" style="width:73px;">£1,112,737</td><td class="GridColumnText" style="width:146px;">12 May 2016</td><td class="GridColumnText" style="width:146px;">26 May 2016</td><td class="GridColumnText" style="width:73px;"><a title='31 Aug 2015 Accounts PDF' target='_blank' href='/Accounts/Ends74\0000267174_AC_20150831_E_C.pdf'>Accounts</a></td>
	</tr><tr style="background-color:#FFFF99;">
	<td class="GridColumnText" style="width:146px;">31 Aug 2014</td><td class="GridColumnNumeric" style="width:73px;">£1,129,065</td><td class="GridColumnNumeric" style="width:73px;">£1,021,361</td><td class="GridColumnText" style="width:146px;">06 Mar 2015</td><td class="GridColumnText" style="width:146px;">08 Jun 2015</td><td class="GridColumnText" style="width:73px;"><a title='31 Aug 2014 Accounts PDF' target='_blank' href='/Accounts/Ends74\0000267174_AC_20140831_E_C.pdf'>Accounts</a></td>
	</tr>
	</table>
	</div

I want to extract the data in bold. they are DATE, INCOME and EXPENDITURE over 5 years. I've been trying to build a regular expression but to avail. Similarly, I've downloaded the data to a temp file and if I try and filter it does it on the tempfile name, not the content... Feel I'm a little out of my depth. Any pointers?

Re: Web scraping/regex

JordyMicheal — Mon, 23 Mar 2020 23:22:18 GMT

Hey @MTMjames,

Any chance you could like the website you're trying to scrape?

Thanks,

Jordan

Re: Web scraping/regex

DavidP — Tue, 24 Mar 2020 00:08:40 GMT

I modified the workflow found in this post slightly based on the data you provided and it came up with the results below.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Parsing-HTML-tables/td-p/25886

Re: Web scraping/regex

MTMjames — Tue, 24 Mar 2020 08:38:15 GMT

The website is https://apps.charitycommission.gov.uk/Showcharity/RegisterOfCharities/FinancialHistory.aspx?RegisteredCharityNumber=267174&SubsidiaryNumber=0

I have around 400 of these where the number is difference else the URL is the same. That bit I have sorted and all works, just not how to grab the data our. I have attached it here...

Re: Web scraping/regex

DavidP — Tue, 24 Mar 2020 08:43:05 GMT

Also look at the solutions to weekly challenge 13 and 40 for more ideas on parsing html

Re: Web scraping/regex

MTMjames — Tue, 24 Mar 2020 08:43:11 GMT

That is brilliant, thanks. I'm new to the community and should have explained better! How do I plug that in to the rest of the flow?

Re: Web scraping/regex

DavidP — Tue, 24 Mar 2020 09:58:02 GMT

Ok, no worries. Leave it with me and I'll work on it this morning. Now that I can see what the data looks like, perhaps we can do something better.

Re: Web scraping/regex

DavidP — Tue, 24 Mar 2020 10:50:19 GMT

Here's a quick and dirty version for you to play with. I'll see if I can get the data out for you in a more elegant representation.

Re: Web scraping/regex

DavidP — Tue, 24 Mar 2020 11:41:57 GMT

This is slightly cleaner I think. Since we're only looking to extract the Table data, I went looking for those rows and filtered the rest out early on. Also replaced &#163 (£ ascii code) with £ sign in the amounts. I added Charity No to show what each table relates to.

Let me know if you have any other questions

Re: Web scraping/regex

MTMjames — Tue, 24 Mar 2020 11:54:30 GMT

That is totally amazing, thank you!!

Re: Web scraping/regex

MTMjames — Wed, 25 Mar 2020 20:30:01 GMT

I must admit I'm totally impressed with that but can't work out how you did it! I'm trying to get some more data from the same site... how would I adapt that flow to pull the postcode and the name of the Chair?

In this page:

https://apps.charitycommission.gov.uk/Showcharity/RegisterOfCharities/ContactAndTrustees.aspx?RegisteredCharityNumber=307322&SubsidiaryNumber=0

I'm looking to pull SP10 3AL and CAROLE MACHIN. The email of sbench@rookwoodschool.org would be helpful too, but I guess that breaks some rules.

Thanks again

Re: Web scraping/regex

DavidP — Wed, 25 Mar 2020 23:10:58 GMT

To make the workflow a bit easier to understand, I've added some documentation to the original workflow. I've also duplicated the workflow to show how to retrieve the address and trustees info from the new URL

Hope you find this helpful.

Re: Web scraping/regex

MTMjames — Thu, 26 Mar 2020 08:11:04 GMT

Ah, I think I understand it more now. Could you put each of the outputs per URN into its own Column? so the postcode and Key person have their own columns? That will help me reference them against other data. Hope thats ok! I've been working with

267174	https://apps.charitycommission.gov.uk/Showcharity/RegisterOfCharities/FinancialHistory.aspx?RegisteredCharityNumber=267174&SubsidiaryNumber=0
268482	https://apps.charitycommission.gov.uk/Showcharity/RegisterOfCharities/FinancialHistory.aspx?RegisteredCharityNumber=268482&SubsidiaryNumber=0

Thanks!

Re: Web scraping/regex

DavidP — Thu, 26 Mar 2020 09:08:37 GMT

Firstly, let's make the input a bit more dynamic so that you just enter the URN and Charity No and the URL is built dynamically.

Then you have isolate the postcode row and the last row for each set and join them to the 1st data set, like this