Chief Delphi

Chief Delphi (http://www.chiefdelphi.com/forums/index.php)
-   General Forum (http://www.chiefdelphi.com/forums/forumdisplay.php?f=16)
-   -   Pulling a List of Teams into Excel (http://www.chiefdelphi.com/forums/showthread.php?t=129356)

ttldomination 06-05-2014 19:43

Pulling a List of Teams into Excel
 
For the past year, I've been using Excel's web queries to pull a list of teams attending the event. The process is relatively simple, go to frclinks.com/event/gadu, point to the table that contains the information, and *bam*, done.

However, recently, the web queries have been failing, and the information isn't showing up.

I'm at a bit of a loss and am wondering if anyone else is either (a) seeing a similar failure and (b) has a solution that let's me continue to avoid parsing through the HTML.

Thanks,
- Sunny G.

Eugene Fang 06-05-2014 20:14

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by ttldomination (Post 1383365)
For the past year, I've been using Excel's web queries to pull a list of teams attending the event. The process is relatively simple, go to frclinks.com/event/gadu, point to the table that contains the information, and *bam*, done.

However, recently, the web queries have been failing, and the information isn't showing up.

I'm at a bit of a loss and am wondering if anyone else is either (a) seeing a similar failure and (b) has a solution that let's me continue to avoid parsing through the HTML.

Thanks,
- Sunny G.

The Blue Alliance has CSV dumps of all events in our database. For your GADU example, the team list would be here: https://raw.githubusercontent.com/th...gadu_teams.csv

Complete data cam be found at: https://github.com/the-blue-alliance/tba-data-backup

Edit: We will provide a CSV dump of all teams (name, sponsors, location, etc.) in the near future.

ttldomination 06-05-2014 20:23

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by Eugene Fang (Post 1383375)
The Blue Alliance has CSV dumps of all events in our database. For your GADU example, the team list would be here: https://raw.githubusercontent.com/th...gadu_teams.csv

Complete data cam be found at: https://github.com/the-blue-alliance/tba-data-backup

Edit: We will provide a CSV dump of all teams (name, sponsors, location, etc.) in the near future.

My need is more along the lines of getting the most up-to-date information straight from FIRST's webpages.

- Sunny G.

Pat Fairbank 06-05-2014 20:28

Re: Pulling a List of Teams into Excel
 
FIRST added a check on the HTTP Referer header around the time that divisions came out, preventing direct access to event team lists unless linked there from the FIRST website itself. This was presumably to save their server from the deluge of traffic from the various "are divisions out yet" scrapers.

I have a workaround that makes FRCLinks still work, but unfortunately Excel will keep running into that HTTP Referer issue, since it tries to access the data directly at that https://my.usfirst.org URL that FRCLinks redirects to.

Tom Line 06-05-2014 22:20

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by Pat Fairbank (Post 1383384)
FIRST added a check on the HTTP Referer header around the time that divisions came out, preventing direct access to event team lists unless linked there from the FIRST website itself. This was presumably to save their server from the deluge of traffic from the various "are divisions out yet" scrapers.

I have a workaround that makes FRCLinks still work, but unfortunately Excel will keep running into that HTTP Referer issue, since it tries to access the data directly at that https://my.usfirst.org URL that FRCLinks redirects to.

So rather than actually provide an easy way for the community to get to the information, they've intentionally obscured it.

Dear Frank,

Please get your IT guys straightened out and provide a method for FIRST developers to get their info! Thanks,

All of FIRST

Ed Law 07-05-2014 23:04

Re: Pulling a List of Teams into Excel
 
Is the block just for event teams list? I didn't seem to have a problem with my scouting database macros when I access Team Standing and Match Results.

stingray27 07-05-2014 23:10

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by Ed Law (Post 1383680)
Is the block just for event teams list? I didn't seem to have a problem with my scouting database macros when I access Team Standing and Match Results.

I believe it is just for the team lists. I didn't have a problem with my standing and match result queries either...

Eugene Fang 08-05-2014 00:22

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by stingray27 (Post 1383683)
I believe it is just for the team lists. I didn't have a problem with my standing and match result queries either...

Anything with a my.usfirst URL is affected.

Ed Law 08-05-2014 00:58

Re: Pulling a List of Teams into Excel
 
I have another spreadsheet that gets the team lists from usfirst website directly and it still works. How do we access through the my.usfirst for team list? I must not have used it that way.

Eugene Fang 08-05-2014 01:16

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by Ed Law (Post 1383707)
I have another spreadsheet that gets the team lists from usfirst website directly and it still works. How do we access through the my.usfirst for team list? I must not have used it that way.

Team list for CASJ here: http://frclinks.com/e/casj

You can't directly navigate to https://my.usfirst.org/myarea/index....014&event=casj due to the reason Pat mentioned above. FRCLinks has a redirect hack that makes it work.

What page do you use to get team lists that's not a my.usfirst page?

Christopher149 08-05-2014 01:54

Re: Pulling a List of Teams into Excel
 
If you need only current data (and not anything historical):

https://my.usfirst.org/frc/scoring/i...page=eventlist contains and ID_event column, which you can use like https://my.usfirst.org/frc/scoring/i...ID_event=10837 (for Traverse City). Is this good enough?

ttldomination 08-05-2014 10:28

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by Eugene Fang (Post 1383712)
What page do you use to get team lists that's not a my.usfirst page?

Looks like Ed pulls his information from the event pages (www2.usfirst.org) and not from the event listing.

Quote:

Originally Posted by Christopher149 (Post 1383715)
https://my.usfirst.org/frc/scoring/i...page=eventlist contains and ID_event column, which you can use like https://my.usfirst.org/frc/scoring/i...ID_event=10837 (for Traverse City). Is this good enough?

This actually works pretty well. I'm wondering (a) where do we get event ids and (b) why this data pulling isn't using an HTTP referrer check.

Interesting,
- Sunny G.

Ed Law 08-05-2014 10:59

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by Christopher149 (Post 1383715)
If you need only current data (and not anything historical):

https://my.usfirst.org/frc/scoring/i...page=eventlist contains and ID_event column, which you can use like https://my.usfirst.org/frc/scoring/i...ID_event=10837 (for Traverse City). Is this good enough?

This is interesting. It works well.

I was tired last night and got confused. I thought I was pulling team list data from another place. It turns out my other spreadsheet pulls in match schedule and determine the team list that way. It is only useful after match schedule is out and before matches begin.

stingray27 08-05-2014 11:39

Re: Pulling a List of Teams into Excel
 
Quote:

Originally Posted by Eugene Fang (Post 1383697)
Anything with a my.usfirst URL is affected.

Oh I was using the www2.usfirst URLs so I didn't realize it. Thanks for the info

Pat Fairbank 08-05-2014 15:38

Re: Pulling a List of Teams into Excel
 
The referrer check is just on https://my.usfirst.org/myarea URLs. The https://my.usfirst.org/frc/scoring URLs are used by FMS to download team lists when setting up at an event. They're tab-separated tables, so they're easier to parse but uglier to look at which is why I implemented the hack in FRCLinks instead of redirecting to them.

You can get the event IDs by looking at the list of all events: https://my.usfirst.org/frc/scoring/index.lasso

I'm guessing there's no referrer check on them because then FMS would have had to add that header in its data download requests.


All times are GMT -5. The time now is 22:59.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright © Chief Delphi