+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    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?
    Last edited by Dimple777; 09-06-2013 at 07:28 PM.

  2. #2
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

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

    I don't see the field you're refering to... can you upload a screenshot of the field you want to get?
    Please click the * below if this helps

  3. #3
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    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.

  4. #4
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

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

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

  5. #5
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    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.

  6. #6
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    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).

  7. #7
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

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

    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
    Last edited by JasperD; 09-03-2013 at 07:16 AM.

  8. #8
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    Whoa, thanks a lot for that code.
    Unfortunately, when I run it with a relatively large number of URLs, it starts returning empty fields randomly after 50-100 URLs. Following those empty fields, some are populated, and then some more are blank, etc... I checked those URLs manually and they definitely do not return 0 result either. I have to rerun it a few times on the affected URLs in order for them to return a value.
    Do you have any idea what could be causing the issue?
    Let me see if I can copy paste the URL batch I was using for testing: (I'll copy it over the next 2 messages since it seems they are limited in length)

    http://na.finalfantasyxiv.com/lodest...1&blog_lang=ja
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=ja
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=ja
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=ja
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=ja
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...8&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...9&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...8&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...7&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...8&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...9&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...8&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...7&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...7&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en

  9. #9
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...7&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...0&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=en
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de

  10. #10
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=de
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...1&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...2&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...3&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...4&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...5&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr
    http://na.finalfantasyxiv.com/lodest...=&blog_lang=fr

  11. #11
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

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

    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...

  12. #12
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    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.

  13. #13
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

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

    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.

  14. #14
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

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

    Try this :
    Please Login or Register  to view this content.
    Last edited by JasperD; 09-04-2013 at 04:52 AM.

  15. #15
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    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?

  16. #16
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

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

    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.

  17. #17
    Registered User
    Join Date
    09-02-2013
    Location
    US
    MS-Off Ver
    Excel 2002
    Posts
    25

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

    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?
    Last edited by Dimple777; 09-06-2013 at 07:41 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-01-2013, 04:21 PM
  2. [SOLVED] Loop through list, perform web query and save each query on its own page
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 07:50 AM
  3. Problem with selecting range with in query table after query refresh
    By shooter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2012, 11:55 AM
  4. Web Query - Change a word in query to form a new query
    By scottymelloty in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-25-2011, 04:13 AM
  5. Importing Data via Web Query - Can values be passed to query?
    By jgalenski@charter.net in forum Excel General
    Replies: 5
    Last Post: 05-09-2006, 01:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1