Interrogator data on draft class / draft utility

[aston217]

OK, so I guess not a lot of people know that you can run Interrogator from the Stelmack suite and get info about the draft class: leadership, personality, intelligence, popularity, play to win, loyalty. A lot of you folks here probably did, but I had no idea until someone mentioned it recently.

So I had a question: how do leagues get that data into the draft utility? What's the file that the admin has to upload there?

I didn't find using Interrogator for this purpose to be terribly straightforward, so here's a procedure I wanted to write down so that anybody could do it. And so I remember how to next time I need to.

Interrogator

1) Run interrogator on the league at some point after the draft class is out. Start from the current offseason year (i.e, if you are about to start the 2025 draft, start from 2025) or it will take a really long time.

2) Interrogator outputs something like a dozen or more .csv files. They will be, by default, located in the install director of Stelmack Soft. e.g, C:\Program Files\StelmackSoft\FOF2k7 Utility Suite\.

3) The two spreadsheets we need are Player Active and Player Historical. Player Active.csv has the info we want, but no player names. Player Historical.csv has the player names. Now we have to combine them. Open these files up in any spreadsheet editing software - Excel, Open Office, whatever.

4) Delete any players who have an experience of "2" or more in both spreadsheets. We only want the draft class rookies.

5) Next, sort both spreadsheets by the Player ID column. This ensures that both spreadsheets now contain only the rookies and are sorted in the same order.

6) Delete any extraneous columns in Player Active.csv. There are a bunch of 'em...basically, just keep the ones you are interested in knowing. It's up to you what that is, but the ones we came here for are Loyalty, Play to Win, Leadership, Personality, Intelligence, Popularity. Might be good to keep the Position or Position Grouping column as well.

7) Now, take Player ID and Player Name column from Player Historical, and paste them as new columns in Player Active. Move the columns around so the two ID columns are next to each other (just a sanity check, to make sure they match up), and so that the Names column is the leftmost column. That's just a formatting detail.

8) Each row should now give you the Player ID, Player Name, and the pertinent data about the draft class guy. If you need to look anything up, simple Find function will do.

9) Save the file in a convenient location under a nice name. There you go!

10) If you want, you can sort by position grouping. You can also select the Position Group column and replace the numbers with the names of the actual positions. To do that, select the *entire* Position Group column, and use the Find & Replace function to "Replace All" for the following, in order:


16 (replace with S)
15 (replace with CB)
14 (replace with OLB
13 (replace with ILB)
12 (replace with DT)
11 (replace with DE)
10 (replace with K)
9 (replace with P)
8 (replace with OT)
7 (replace with OG)
6 (replace with C)
5 (replace with WR)
4 (replace with TE)
3 (replace with FB)
2 (replace with RB)
1 (replace with QB)


Or, assuming your position group is in column C and the first player starts in the 2nd row:


=IF(C2=1,"QB",IF(C2=2,"RB",IF(C2=3,"FB",IF(C2=4,"TE",IF(C2=5,"WR",IF(C2=6,"C",IF(C2=7,"OG",IF(C2=8,"OT",IF(C2=9,"P",
IF(C2=10,"K",IF(C2=11,"DE",IF(C2=12,"DT",IF(C2=13,"ILB",IF(C2=14,"OLB",IF(C2=15,"CB",IF(C2=16,"S","XXXXXXXXXXX"))))))))))))))))


Copy that formula into a new"Position" column and extend it all the way down. Replace C2 with A2 or B2 or whatever other column depending on whichever column the PosGroup numbers are. This automatically does the conversion. I broke it into two lines but you'll want to delete the linebreak, of course.

The order with which you do the replace-all matters because if you replace all the 1's with QBs first, you'll get all the QBs properly listed, and then the 10-16 position groups will be listed QB0, QB1, QB2, QB3, etc...you get the idea ;P

Example final product file, taken from the 2023 OSFL Draft:

Download 2023 Interrogator.csv from Sendspace.com - send big files the easy way (http://www.sendspace.com/file/ptp7gr)

[Nemesis]

Hey!

[aston217]

Oh, yes. Sorry, I neglected to give Nemesis all the credit for figuring most of this stuff out :)

[Nemesis]

Oh, yes. Sorry, I neglected to give Nemesis all the credit for figuring most of this stuff out :)
Hey!

[Nemesis]

:)

[Nemesis]

I'm just joking around, Aston. Good write up.

[aston217]

I'm trying to paste the interrogator data into my extractor CSV, but there are a variety of problems preventing this from working. Well, one, really - Excel (and Open Office) will spit out a CSV that doesn't match up to the formatting standards of the one it takes in.

For one, the double quotes around text fields are lost, and a whole crap ton of extra commas are added at the end of every entry. Anybody more technically inclined have an idea of how to proceed?

The idea here is to get the interrogator data viewable in Extractor. Matching up the players from the two CSVs is not a problem, the problem is just saving the Extractor CSV without breaking the structure of the file.

[Yoda]

From my understanding, that data is already there, it just has to be added to the code for the conscriptor.

[Ben E Lou]

From my understanding, that data is already there, it just has to be added to the code for the conscriptor.Well, yeah. All of the gyrations in this thread are completely unnecessary if the league has someone who knows MySQL/php. It's a fairly straightforward query from the tables created by DBUpdater. Once DBUpdater is run after the franchise/summer/ticket stage, it's all right there. Here's the query
SELECT m.positiongroup, lastname, firstname, birthmonth, birthday, personality, loyalty, playstowin, leadership, popularity, intelligence, volatility, redflagmarker
FROM fof_playerhistorical h
JOIN fof_playeractive a on a.id=h.id
JOIN fof_mappings m on m.id=a.positiongroup
WHERE h.experience=1
ORDER BY a.positiongroup, lastname, firstname
SORTABLE ROOKIE PERSONALITY RATINGS (http://www.fof-bfl.com/rookiepersonality.php)

A similar query can add the info to Conscriptor so you don't have to bother with uploading all the extra Interrogator stuff.

SELECT personality, loyalty, playstowin, leadership, popularity, intelligence, volatility, redflagmarker
FROM fof_playerhistorical h
JOIN fof_playeractive a on a.id=h.id
JOIN player p on p.player_name=concat(h.firstname,' ',h.lastname)
WHERE player_id={$player_id}

http://www.younglifenorthdekalb.com/ccfl/draft/show_player.php?player_id=4
(bottom of player card)

[aston217]

Thanks. Actually all I wanted at first was to figure out how to do that (in conscriptor) but I couldn't find anyone who knew and could explain it at the time. So this is great!

Stupid question, but who in the league has access to these DBs? Only the commishes? I don't suppose I could manually generate and query the databases from a local server for myself and then supply the commish with the end result, or could I?

[Ben E Lou]

Stupid question, but who in the league has access to these DBs? Only the commishes? I don't suppose I could manually generate and query the databases from a local server for myself and then supply the commish with the end result, or could I?Access to the main database depends on the league, but anyone can set one up using DBUpdater, as long as they have access to a MySQL database, either through a hosting plan or on a local machine. Yes, you can set up php and MySQL on your local machine and do whatever you want. Mine is set up to do just that. DBUpdater works much faster that way, as a matter of fact. It can process an entire season in less than 30 seconds on my machine. Over the internet to a hosted server, it takes 5-6 minutes to do just one gameday stage, and 45-90 minutes to do a whole season. (And that's on a good day with a pretty fast connection.)

[Yoda]

I only know enough to cause it to crash when I try to add that stuff to player.inc.php :/

[Ben E Lou]

Heh. If I get time at some point I can give it a look for you.

[Yoda]

I'd appreciate that. I sent you a PM on the CCFL forums.

[aston217]

hey, I figured out how to modify the extractor file for draft analyzer. (Excel generates trailing commas and they can be regexed out)

Ben, your instructions are great, but they are just for the conscriptor, correct? (that's fine, I just wanted to get it into DA as well) Importing the DA extractor as a csv and running the commands with the stuff from DBupdate would be much faster than executing the queries manually.

I just can't figure out how to import the extractor used for DA into an sql database. The variable record length seems to be causing problems and I haven't the programming chops to overcome it.

Not sure if you would consider that something worth doing or not if it's already available in conscriptor - I think the DA result is pretty handy though: example, load this up in draft analyzer (http://www.cyberfootballleague.com/forum/index.php?action=dlattach;topic=4214.0;attach=148)

[WheelsVT]

I was just referred to this thread. I'm adding this to Conscriptor as an import when setting up a draft.

It's up to the draft admin to upload the PlayersActive and PlayersHistorical CSV files then these attributes will appear in the player detail page just like popularity.

[PhinsFan]

Good read here folks. I'll be toying with this at a later date so may have some questions then. Thanks aston17 for your write up and thanks to all with your comments. Great information