Quote:
Originally Posted by percolaten
Lans,
I'm thinking along the lines of something like, say, every player from the D-Backs, 1999-2003, from each individual season. So in this example, there'd be 5 Gonzos in there, but one Reggie Sanders from the one season he was with the club.
|
That should be easy enough. Don't even need Stathead for that.
Start here:
https://www.baseball-reference.com/t...9-roster.shtml
Select that table with all the names like you are doing in an ordinary highlight selection. Then copy it. Then paste it into Excel.
You will have a nice bunch of columns reflecting that very table. Name, Age, Country, Bats, Throws... etc. Color the bottom row (the one with the last guy on the roster list so you know he is the last guy in 1999).
Repeat this for every year.
Then delete all the columns except the name (which pasted as a hyperlink). You should have just a list of the names with that colored fill line marking every year break.
Not in the column to the right of that name list, but the next column (So if names are in column A go to column C) and then put 1999 in column C for every row that is populated until you get to that first colored line (who also gets 1999.) Then after that line you know the 2000 guys start so add 2000 in their column C. (also delete any of the headers from these additional year pastings). Repeat for all the years you posted.
Then save that Excel sheet as a Macro-enabled Excel sheet (these end in .xlsm). Then go to the Developer tab and select "Virtual Basic". That opens a box with its own header. From that new header, select "Insert" and then select "Module" and a big blank white space you can type in appears. Paste this into that page:
Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
Then go back to the box header and select "Run" and from that list choose the option "Run/SubUserForm". Close out of that Developer area now.
You can now see why we left column B blank. The hyperlinks of the names from column A are extracted into Column B. Select column B in its entirety and pull up Find and Replace. Find "https://www.baseball-reference.com/players/*/" - notice my wildcard asterisk. That's important. Leave the "Replace" line blank since we want to delete that part of the web address. Now repeat the find and replace with ".shtml". See that we have removed the other part of the link we didn't need.
Column B is just the import code now for all those players - and you have their year in Column C. That code still is a hyperlink though, so go and highlight all of column B again and right click. Select "Remove hyperlink".
Delete Column A. Delete blank columns on the left so that the IDs are in Column A and the years in Column B.
Save that macro-enabled Excel file as a .csv file. Convert that .csv file to a text file and you should have the code, a comma and then the year. That's the mass import text file.
I do this all the time (except the .csv to .txt. conversion) but that should work as described. It seems like a lot when I type it out but it's just a lot of clicks that you get used to.
You know how to find me if you need a hand in any of the steps. Good luck!