Parsing with Regular Expressions

Question

https://www.youtube.com/watch?v=Yb3jurkfDg4

Sometimes using the text-to-columns or the formula is not enough. It's high time for a skills upgrade. With a working knowledge of regular expressions, you'll soon tame the most wildly cluttered data.

You’ll Learn How to:
Parse Data

Featured Tools: 
RegEx

Presenter: @AlexKo

Date: 2017-03-27

Alteryx Designer Version: 11.0

starting_workflow.yxmd

completed_workflow.yxmd

AlexKo · Answer

Hi @Inactive User, Cool question. Yeah you can do that. This website uses PHP for its interactive elements. If you use that year selection menu, you might notice that the URL changes to... http://www.energy.ca.gov/almanac/renewables_data/solar/index.php But it doesn't change the URL based on the year, it just updates the tables on the webpage. So what's going on behind the scenes? You can click anywhere on the page and "View page source" to find out. Here's the part that's important. This is the drop-down list, which exists in a PHP form object. The form sends an API post request when the user hits the "Go" button. Since there is a name associated with this form, newYear, we can pass along that variable right in the URL to get back the years we want. http://www.energy.ca.gov/almanac/renewables_data/solar/index.php?newYear=2014 In Alteryx, you could have a field with all of the URLs from the different years as rows, pass it through the Download tool, and get a field of DownloadData as your response. Best part? You can just add a few lines to the Text Input tool in the workflow we built out today and get back all the data from these years, without having to change any RegEx. Cheers, Alex

unknown · Answer

Great Session, really demystified RegEx.

Using the URL in the demo, http://www.energy.ca.gov/almanac/renewables_data/solar/, how could you create a workflow to pull all years in the year selection drop down (or perhaps 2010 - 2015).  Is that possible?

Thanks.

JoeM · Answer

@MarqueeCrew

Thanks for noting that error. I updated yesterday morning so that the date should now be correct.