Is there a way to scrap data from various URLs with different strings?

[PaulDBrown]

Hi all,

I wish to get data from a website from various URLs within. The thing is I think there are hundreds, and each has a unique URL. However, I believe there is a number coding going on. Here are three examples:

https://www.productionparadise.com/spotlight/1549/global-advertising-photography/magazines/

https://www.productionparadise.com/spotlight/1556/cars-transportation-and-landscape-photography-and-...

https://www.productionparadise.com/spotlight/1540/people-and-lifestyle-photography/magazines/

It seems that the content is numbered, and the title text string added on the end.

Is there a way of getting the data I need by code? for example, using a base URL along the lines of 

https://www.productionparadise.com/spotlight/1000

and adding code to get the data from say https://www.productionparadise.com/spotlight/1000 onwards?

Is there a way of making it ignore the text after the number code?

Many thanks for your help.

Best regards,

Paul.

[PaulDBrown]

HI again,

I've suceded in extracting the data. I have followed the method of creating a custom function to look up number values from a table. This works fine, except when there is a break in the number sequence, which then returns an erro and the query stops. Is there a way to make the query skip a number form a table if there is an error?

Thanks,

Paul.

[jtownsend21]

I think there is a pretty simple way to do this, but I want to make sure I understand. I am making a few assumptions about your dataset.

Assumption 1 - You havea table with all the URLs listed

Assumption 2 - All the URLs are in the same format

Assuming both are true then you could simply use the querry editor, duplicate the column (so you don't lose the original data) and then use the split column function based on the number of characters or delimiters. For yours the delimiter would be the "/". 

If assumption 2 is not correct, then there is a way to do this using the querry editor, but I would need to look deeper into it to determine how to do that. 

[PaulDBrown]

HI jtownsend21,

Thank you for looking into this. Apologies since I may not have been clear in my second post. I am succeeding in getting Power Query to extract the data I need from each URL. I followed a tutorial from Curbal on how to extract from several URLs on the same website by suing a function to change the hardcoded number in the URL string. The function reads through a table of values which are in sequence, includes that number in the URL string (where the original hard coded number was, and reaches out and reads each corresponding URL page to extract the data.

So what I'm achieving is POwer Query running through the URLs in sequence:

https://www.productionparadise.com/showcase/1394/

https://www.productionparadise.com/showcase/1395/

https://www.productionparadise.com/showcase/1396/

and so on...

The problem I'm finding is that unfortunately there are some gaps in the numbers in the URLs themselves. So initially the table with the sequence would read something like 1200..1525. However, as Power Query executed the extraction it would return an error if the string including for example 1285 didn't find a corresponding page. It would then return an error, and stop. Which in practice means rewriting the  table values to exclude numbers which returned an error. This of course is time consuming and impractical. 

So what I'm ow asking is if there is a way to instruct Power Query that when a particular number in the string returned an error, to ignore that particualr number and move onto the next and so forth.

Setting it up manually is cumbersome, since it cycles through the whole list of numbers each time I change the number sequence, and each URL takes approx 3 seconds to compute.

Any ideas?

Thanks!

[ImkeF]

Wrap your webcall in a  "try <YourWebcall>... otherwise null" -statement. That will overcome the problem with the errors.