Reply
Thread Tools Display Modes
#1
Old 12-29-2017, 08:25 PM
Guest
Join Date: Oct 2000
Posts: 11,576
Help with Excel Web Queries,

I am trying to extract data from the PGATour website via Excel web query.

This is sample of the page that I want to extract:

https://pgatour.com/players/play...corecards/r493


After saying yes to a ridiculous amount of script errors, excel normally locks up and does not download.

OK, doing some of my own troubleshooting, I figured the problem might be because the html is in the middle of the address.

So I tried a leaderboard sheet from the PGATour portal, which has this address,

https://pgatour.com/competition/...aderboard.html

with html at the end of link. More script errors and locking up

OK, maybe my EXCEL is too dated to capture this date (EXCEL - 2007) so I try to extract the data using Google sheets and the IMPORTHTML or the IMPORTXML

Watched some tutorials on Youtube for help on these functions, which helped just a little.

The only thing I can think of is the PGATour is not setting up this data in a conventional table.

FWIW, I have successfully extracted data from other parts of the PGATour portal using EXCEL web queries and google IMPORTHTML. Like this page.

https://pgatour.com/stats/stat.02671.html

FTR, the two pages that are troublesome are pages designed to continuously update, while the third link is updated once a week. Not sure if that makes a difference.
#2
Old 12-30-2017, 07:34 AM
Charter Member
Join Date: Sep 2003
Location: Southeast Florida USA
Posts: 21,035
You're stuck with those first two. Excel web query (at least back in 2007) is designed to parse plain old HTML pages that contain all the info statically.

Those pages are all the new modern kind where the page itself contains nothing but the graphical outline of the page and a program written in JavaScript that separately loads and displays the data you want. Excel totally can't handle that.

The third page is better; the raw data is in there. But lots of the page is also designed to be script-operated and to fail if the script isn't there. So Excel might be able to get you some data, but only after lots of fighting.


Your best bet (and it's not a good one) is take your browser, switch off or block all scripting of any kind, then go hunting around the internet to try to find a page that will still display the info you hope to scrape off of it.

Good luck. I bet you'll fail. That sort of page design is at least a decade out of date now. A real programmer could reverse engineer the scripting on those pages, or contact pgatour's app developer interface people to find out how to obtain the data the way their server wants to provide it.

The fact you're asking these questions says you're not that person and it's not something that can be taught over a messageboard. Sorry to be the bearer of unhappy news.

There might be some better point and click tools for data scraping these kinds of modern script-driven sites. There's certainly a desire for it. I'm enough out of the biz now that I can't offer any suggestions. Anyone?

Last edited by LSLGuy; 12-30-2017 at 07:36 AM.
#3
Old 12-30-2017, 08:18 AM
Guest
Join Date: Oct 2000
Posts: 11,576
LSLGuy, Thank you for the reply. And you are absolutely correct, I am a novice when it comes to programming.

My current laptop will need replacing soon, and I have thought upgrading my microsoft Office package to the latest version at that time. Would that help? The fact that Google spreadsheet can't import the data suggests that it won't help.

I have been able to data-mine the info that is presented like the third page. Using visual basic macros and the scripting errors do not show up.
#4
Old 12-30-2017, 08:31 AM
Charter Member
Join Date: Sep 2003
Location: Southeast Florida USA
Posts: 21,035
I don't know specifically about Office 2016, but I agree that your experience with current Google spreadsheet is not encouraging on that front.
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 08:34 AM.

Send questions for Cecil Adams to: [email protected]

Send comments about this website to:

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: [email protected].

Copyright 2018 STM Reader, LLC.

Copyright © 2017
Best Topics: backward baseball cap corp pronunciation entertain me internet ww2 russian weapons commercial tankless toilet oven plug types eating gristle african american legs daylight headlight section allstate background check boss revenge m4m ads 200 lb bow guitar case lock hairy buttcrack padawan hair microsoft flowcharts replacement oven insulation flower company names bull moo surgery vs operation safe box keys call aids hulu expiring shows super glue poisoning mazikeen sandman wombat pet 3 stooges theme lock lunch box strep test cvs dramatic sting restoring corian countertops limerick nantucket is shithead a real name the stand part 3 does applesauce need to be refrigerated carbonaro effect is it real why do people pierce their tongues 49cc scooter weight limit how long is two months how long does clamato last can too much oil damage engine how long can you employ a temporary employee do black widows play dead where to buy chloroform remington 870 price walmart will a gfci work without a ground how tall were the romans what is the best metal for a sword can you use toysrus gift cards at amazon gauge blocks stick together can you eat yogurt while on antibiotics there once a man from nantucket non scary stephen king books cost of honeybaked ham why do i like pantyhose is battlestar galactica good is it safe to send a check through the mail how many numbers in ups tracking day trading with td ameritrade how many heavenly chips should i get back to the future timeline pennies on your eyes is maalox still on the market melting point aluminum foil how to set up dvd player with direct tv