Could someone please help me on a Web Query? (Solved)

**Dimple777** · 09-02-2013, 03:37 PM

Hello, I'm a complete newbie on Excel web queries, so please bear with me and keep the explanations as simple as possible.

I am using Excel 2002.

Here is what I am trying to accomplish:

1)Query this website:
http://na.finalfantasyxiv.com/lodest...=&blog_lang=en

and import the field with "15" as a number result in a cell on my spreadsheet. The rest of the field/info can be ignored, that's the only thing I need to query.

2)Query this:
http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
and import the "13" in another cell, as before the rest of the info can be ignored (i'm trying to make the query as fast as possible as well since I will have a ton of them in the same spreadsheet)

Then I'll simply add those 2 in another cell on the spreadsheet. I have a lot more than 2 to query, but I can just insert more steps once I know how to do those 2 already.

Thanks to anyone who can help me do this.

PS: is there a limit to how many queries can be done in a single spreadsheet? Would there be an easier/faster way to add the same field on a large number of similar URLs and have it spit out to a .txt file or excel spreadsheet?

**JasperD** · 09-03-2013, 01:14 AM

I don't see the field you're refering to... can you upload a screenshot of the field you want to get?

**Dimple777** · 09-03-2013, 04:16 AM

On my first link, it's the "[15] Total" field (at the time of writing this, the total is still 15). It's right before the list of characters. It's basically the number of results that the search returned.

**JasperD** · 09-03-2013, 05:42 AM

Do you need the list of characters or just the number of characters? Just the number 15 or the rest as well?

**Dimple777** · 09-03-2013, 06:20 AM

Absolutely just the number of characters, I don't need anything else and I'd rather not retrieve any of the other info as well in order to speed up the queries.

**Dimple777** · 09-03-2013, 06:26 AM

Also, I know it's a long shot, but on some particular queries, the max number of results displayed is 1000.
See this example:
http://na.finalfantasyxiv.com/lodest...bo&race_tribe=

Is it possible at all to pull the actual number of results before it gets truncated down to 1000? (since I don't need to display any result but I am just interested in the total numbers).

**JasperD** · 09-03-2013, 07:11 AM

Put your urls in column A, then run the code.
The number you want to return will be put in column B next to it. You can't get the actual result if it's rounded down to 1000 - then it'll display 1000.

Please Login or Register  to view this content.

Please remember to click the * below if this helps

**Dimple777** · 09-03-2013, 10:37 PM

**Dimple777** · 09-03-2013, 10:39 PM

**Dimple777** · 09-03-2013, 10:39 PM

**JasperD** · 09-04-2013, 02:14 AM

It's not about the links, it's about hammer protection on the site.
You're basically accessing the site many times in a short period, after which it times out for a little bit before letting you on again.

Try to do it in batches of 50~100 with a few minutes interval.
It will still be faster than doing a web query

The only other option I can think of is by alternating the parsing of the site through boilerpipe and google.com/gwt/n

I can write that later for you, but am in the train typing on my phone, so right now is a bit difficult...

**Dimple777** · 09-04-2013, 02:42 AM

Yes that would be awesome, thank you.

Another thing I have noticed is that the website I'm querying has both a desktop version and a mobile version (the option is at the bottom below the results). Since the mobile version is much faster to access and loads up less stuff, that's the one I use most of the time and It may have some cookies or something associated with that view. From the little I understand of your code, I saw that you are looking for what follows the "of" in the page, but in mobile view (the faster one to load) the results just have a "[XX] Total" display. That may also be throwing off the code, no? I have noticed that even in my browser, when I load a bunch of tabs with the mobile view, once in a while some of them will default to desktop view, so I imagine it could do the same the other way around. Maybe adding a check for either "of XXX" or "[XX] Total" would make it errorproof?

Thanks again for your help, it's very much appreciated.

**JasperD** · 09-04-2013, 03:50 AM

The code doesn't look at the mobile version of the site, so the way I look up the values is fine for any link. (You've got it almost right, I am looking for "Displaying" then the first "of" after that word and then the first numeric value after that one).
I'll try to get you the "alternating" version later.

**JasperD** · 09-04-2013, 04:27 AM

Try this :

Please Login or Register  to view this content.

**Dimple777** · 09-04-2013, 07:10 AM

Alright, after having tried the new alternating version, after 100 or so URLs, it started alternating between a blank result and a valid one. Then after a while, two valid results again. I narrowed it down to the boilerpipe URL having this limiter, while google didn't seem to have it.
So I took off boilerpipe and have all the URLs go through google. So far so good, it's running as I speak in the background and after 400 URLs still no problem with google only. It seems a little slower than the first code with boilerpipe only, but I'll take accuracy over speed any day.
I particularly like that new version updating the numbers as it goes, it's neat! Thanks again for the help.
I have one small last question if you don't mind. In order to avoid hitting the limit of 1000 on some searches, I've been trying to do searches by characters' name starting let's say with the letter M, but I haven't been able to do it. It would return all characters with an M in their name even if they started with another letter (so the searches would not be mutually exclusive like I want them to be). After some googling, I tried an "a%" search but still no luck. Do you know of any text search syntax that would allow me to do a query for only characters whose name start with a particular letter?

**JasperD** · 09-04-2013, 08:16 AM

Can't be done - the search engine just searches for "*M*" if you search for "M" -- you can not specify that it needs to be the first letter. Sorry about that, but I think you have the most usable solution now.
So please mark the thread as solved and happy playing around with it.

**Dimple777** · 09-06-2013, 07:39 PM

Alright, I had a few issues with google also returning blank fields once in a blue moon. That did not bother me too much, but it also would fail to return 0 when the search had 0 results, so I could not distinguish between a failed attempt and a 0 result query.
I ended up tweaking the code in the following ways:
- I added a 0 return to the query when it detected "no results" in the page
- I made the default text extractor engine the boilerpipe one (since it was substantially faster than google) and when it would fail to return something, it would cycle into the google engine before going on to the next cell.
It seems to work very well now.

I have learned a lot by reading your code, thanks again. The things I still don't get at all is how come you were able to use "Post" and "Get" interchangeably and how come the google search code was so different from the boilerpipe one, I tried just inserting the google URL in the boilerpipe code, but it would not work, so I take it that they both work pretty differently.

Also, by any chance, does anyone know reliable and fast alternatives to those two sites for text extraction? I was really happy with the speed of boilerpipe but it seems to be quite limited in how many times you can use it. And google seems way more lenient for its usage limit (but still has one it seems, unless my experiences were occasional random timeouts?) but is at least twice slower than boilerpipe. Are there other alternatives to those 2?

Could someone please help me on a Web Query? (Solved)

LinkBack

Thread Tools

Rate This Thread

Display

Could someone please help me on a Web Query? (Solved)

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query?

Re: Could someone please help me on a Web Query? (Solved)

Thread Information

Users Browsing this Thread

Similar Threads

Reference/use a cell value in a SQL query in Excel 2007 - Query Wizard

[SOLVED] Loop through list, perform web query and save each query on its own page

Problem with selecting range with in query table after query refresh

Web Query - Change a word in query to form a new query

[SOLVED] Importing Data via Web Query - Can values be passed to query?

Bookmarks

Bookmarks

Posting Permissions