Stats Import/Export, CSV Questions

[cuervo72]

(I will preface this by saying once again that I am dealing with OOTP15, but I don't believe this functionality has changed in 16 so I am asking here)

In my MP league, due to some things that I could have handled better I wound up importing ratings from one batch of draftees to overwrite another batch of draftees (the league was halfway through a draft when I realized I had to revert a file to deal with an out-of-synch retired.dat file). This actually worked quite smoothly, as the draftees in the repaired file looked just as they did in the original file. Except for one thing - their college and high school stats. I couldn't adequately import them so I just left them and figured eh, nobody pays attention to them a couple of seasons in so it's not a big deal if they are mismatched.

I noticed however that the game is not treating these stats quite right anymore. Here is a line from the players_career_batting_stats.csv CSV export file, taken before I had to apply repairs:

player_id year team_id game_id league_id level_id split_id position ab h k pa
497 2057 0 0 0 10 1 0 277 81 49 315
Here is what is showing up now:

player_id year team_id game_id league_id level_id split_id position ab h k pa
497 2057 0 0 100 10 1 0 259 77 38 291

The actual stats aren't important, as I said I know they are not going to match. What seems to be an issue though is that league_id - it is no longer 0, but is 100 -- the ID of our major league. This is causing some issues in the front page league leader displays and in the player pages -- and I am concerned that these are going to be tallied as major league stats.

My questions I suppose are these:

1. How did this happen? I assume it has something to do with importing players through the Import Roster interface, but I only did this after these players had been drafted. Why would this change their league IDs? (also - CRAP - this did this for all of the veterans in the league as well, as I had also imported them to fix issues with missing players)

2. Ok, related to 1. that I just noticed - these HS/COL stats are now being tallied in career ML stats. This is kind of an oversight, no?

3. For the stats import function, why is level_id included, but not league_id? Nearly all of the other columns between the database csv exports and the import/export stats files match. This one is missing. Why?*

4. Is there any way to get rid of these flawed stats?



* I had tested something previously, and an import of HS/College stats (levels 11 and 10) did semi-work, but I had these same issues because I couldn't specify league_id. It would seem to me that if you could indeed import league_id you might be able to import HS/COL stats, which I think some have wanted for some time

[NoOne]

Originally Posted by cuervo72

(I will preface this by saying once again that I am dealing with OOTP15, but I don't believe this functionality has changed in 16 so I am asking here)

In my MP league, due to some things that I could have handled better I wound up importing ratings from one batch of draftees to overwrite another batch of draftees (the league was halfway through a draft when I realized I had to revert a file to deal with an out-of-synch retired.dat file). This actually worked quite smoothly, as the draftees in the repaired file looked just as they did in the original file. Except for one thing - their college and high school stats. I couldn't adequately import them so I just left them and figured eh, nobody pays attention to them a couple of seasons in so it's not a big deal if they are mismatched.

I noticed however that the game is not treating these stats quite right anymore. Here is a line from the players_career_batting_stats.csv CSV export file, taken before I had to apply repairs:

player_id year team_id game_id league_id level_id split_id position ab h k pa
497 2057 0 0 0 10 1 0 277 81 49 315
Here is what is showing up now:

player_id year team_id game_id league_id level_id split_id position ab h k pa
497 2057 0 0 100 10 1 0 259 77 38 291

The actual stats aren't important, as I said I know they are not going to match. What seems to be an issue though is that league_id - it is no longer 0, but is 100 -- the ID of our major league. This is causing some issues in the front page league leader displays and in the player pages -- and I am concerned that these are going to be tallied as major league stats.

My questions I suppose are these:

1. How did this happen? I assume it has something to do with importing players through the Import Roster interface, but I only did this after these players had been drafted. Why would this change their league IDs? (also - CRAP - this did this for all of the veterans in the league as well, as I had also imported them to fix issues with missing players)

2. Ok, related to 1. that I just noticed - these HS/COL stats are now being tallied in career ML stats. This is kind of an oversight, no?

3. For the stats import function, why is level_id included, but not league_id? Nearly all of the other columns between the database csv exports and the import/export stats files match. This one is missing. Why?*

4. Is there any way to get rid of these flawed stats?



* I had tested something previously, and an import of HS/College stats (levels 11 and 10) did semi-work, but I had these same issues because I couldn't specify league_id. It would seem to me that if you could indeed import league_id you might be able to import HS/COL stats, which I think some have wanted for some time

there's a bumped thread (and i accidentally replied) that highlights the issue of amateur stats adding to pro stats... no, there doesn't seem to be a way to fix this.

sounds like just a flawed import/export command. it would take a minimal effort to fix this kind of thing.

if you can unpack their dat files(?) and get the actual databases loaded into SQL or some other RDB program, you could probably fix existing problems with a simple SQL command. i'm sure it's protected, but there are usually ways around this.

this may or may not be against the end-user license agreement (as if once you buy something the manufacturer has any right to tell you how to use your own poperty), but do as you wish since you own it. just make a backup copy before you fool around.

[cuervo72]

Originally Posted by NoOne

there's a bumped thread (and i accidentally replied) that highlights the issue of amateur stats adding to pro stats... no, there doesn't seem to be a way to fix this.

sounds like just a flawed import/export command. it would take a minimal effort to fix this kind of thing.

if you can unpack their dat files(?) and get the actual databases loaded into SQL or some other RDB program, you could probably fix existing problems with a simple SQL command. i'm sure it's protected, but there are usually ways around this.

this may or may not be against the end-user license agreement (as if once you buy something the manufacturer has any right to tell you how to use your own poperty), but do as you wish since you own it. just make a backup copy before you fool around.

Yeah, I bumped that thread too (perhaps I shouldn't have, but it's relevant discussion on an issue that has not really been addressed).

Why I'm not really freaking out about this issue is that the data is still there and yes, it can be massaged - if the import process is ever looked at. It seems to be pretty low down on the priority list though considering it is a known issue that has made its way through a few releases now. But it seems like it shouldn't be hard to fix:

1. Stop the roster importer from rewriting league_id for old stats lines
2. Add league_id to the stats importer. It writes team_id, level_id, and all the stats - why not league_id as well?

I'm not sure I know enough to crack any of the .dat files, especially if there are protections to combat multiplayer owners from doing so to get at real ratings, etc. Fixing things in a db would be trivial too, you're right. Just run a command setting the league_id back to '0' if level_id is 10 or 11. Assuming there was a fail-safe way to get the stats back into the game, which there doesn't appear to be right now.

I'll have to see how StatsLab treats this. For summing purposes, it should be pretty simple to ignore the league_id and only consider stats where level_id = 1. Either in StatsLab or any other custom-generated pages (I've thought of creating my own pages a few times - I do it for our FOF league - but have never followed through; not really sure what I could add to what StatsLab does anyway).

Actually that's a third way to "fix" the issue:

3. In the game/game html, filter by the level_id for ML stats, not the ML league_id.

That's still just a workaround though - HS/College stats should not be associated with the ML league_id. That's the root of the issue. I just don't know if it's really on the radar as a pressing enough issue to be fixed.

[NoOne]

it's a simple change in the sql command for the import and or export feature.... i can't see why they'd be worried about access to league id in the export... you can cheat all you want in the game as it is.

i may have to look into this Statslab, i just use in game reports and such. i don't import players, so i have never run into the problem you describe, but that program sounds fun.


if you do get it unpacked and loaded into MySQL or something, there is no need for import/export. you'd be working on the actual data. i'm sure someone has made a program for this use.... no heavy lifting except to find it.

[cuervo72]

Actually, this is pretty interesting. I've been doing some experimenting and can get college stats into the game, though it's a bit of a cheat.

It involved creating a new college-level shell league. What I thought would happen is that I would specify the league_level_id (10), it would recognize that the league exists, and would sub in the real id when importing rosters (instead of inserting the ML id of 100).

That didn't work.

What did was adding a team to that league; importing a player line with that team ID correctly added stats to the college level. But it was completely predicated on ID -- when I imported the stats with a team ID of 0 and a league ID of 10, the stats were considered to be at the ML level. When I imported the stats with the shell team ID it assigned the stats to the college level -- no matter what value I entered for the league level.

So if you want to add some generic college/HS stats, just make sure there is a team at that level that you can use for its ID. That is, if you're comfortable keeping a couple of bogus leagues around, which may or may not come with its own set of issues.

Of course this doesn't help at all with the problem of wanting to import various splits (LH, RH, playoff). So right now I can fix my vets but lose playoff stats, or keep playoff stats but have vets who have misapplied amateur stats.

edit to add:

Actually, I wasn't quite right with the team and level IDs:
-- if I put in a real, active team ID and a level, the game would assign to the correct level for that team regardless of the level in the data file
-- if I put in a non-active non-zero team ID and a level, the game would assign to the level specified. So if I used 41/10, it would be "BR" for team and COL for league. If I used 42/11, it would register "TAM" for team and HS for league. If I used 41/11, it would be BR and HS.
-- if the team ID was zero, it would always register as ML level

[RonCo]

Cuervo has always kicked butt.

[cuervo72]

Heh, thanks Ron. It's just...bothersome that what I thought was a pretty nice way to get around having to manually enter FOBL FA - changing a data file and importing rosters - would mess things up in a seemingly totally unrelated way (albeit one that some folks had become aware of). I mean, this seems like a pretty notable issue but I guess not that many people run into it.

I figured maybe there were some other ways to get around this, which brought up other quirks (I had no idea there were so many remnants of dormant teams* kicking around - they don't get exported in teams.csv - that couldn't otherwise be accessed; might be nice if they could, because you never know). Still can't quite get there, but maybe this info will help someone else.

It also has me wondering why exactly there are two export (well, csv - more if you include mysql, etc) functions. The csv export (players_career_batting.csv) HAS data and designators for splits. Why can't that be re-imported?

* I actually wonder how Markus treats team "0"; my guess is that it is not actually in the data file anywhere, or it would be recognized for stat/roster imports and wouldn't default to a ML league ID (result of a null data value). Might be handy to have one though - I'm considering making a team from the city "Total" with no nickname and no abbreviation just to immediately deactivate it to see how that might show up in HTML reports

[NoOne]

sorry to hear it didn't work... take a week or two off of tackling the problem and start from scratch later... i tend to get tunnel vision and time off helps me see other avenues to explore when i return to the problem.

are the csv and sql dumps equivalent in all ways? the csv dumps i use only require a spreadsheet, so i don't use MySQL. rhetorical in nature, no need to reply.

[RonCo]

Originally Posted by cuervo72

(I had no idea there were so many remnants of dormant teams* kicking around

You just can't get rid of those Nukes.

[StatMan22]

I don't mean to bump an old thread, but I have run into this issue as well (OOTP16). I have a fictional universe and am testing out promotion/relegation-had to import/export players to tweak ratings and fine-tune the talent differences between the levels. Obviously I'd be better off if I'd have tested it all out first but now I have some guy in one of my leagues who will, permanently, be on the leaderboards with 220 games played, 937 at bats, etc when everyone else is doing 162 game seasons.



Maybe this should be in the bug report board, but this is an issue that'd be nice to see fixed.