Home | Webstore
Latest News: OOTP Update 20.6 - OOTP 20 Available - FHM 6 Available

FHM 6 Available Now!

  

Go Back   OOTP Developments Forums > Out of the Park Baseball 20 > OOTP Mods > OOTP Mods - Database Tools

OOTP Mods - Database Tools Do you need to take a dump? SQL gurus welcome

Reply
 
Thread Tools
Old 01-09-2015, 05:29 PM   #1
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Historical Importer 3.0 Plus

Updated 4/11/15:
Now fully supports Spritze's latest database.(28,291 players)
Fixed: 36 duplicate player names. Added middle initial/name.
Expanded formulas to accomodate up to 30,000 players.

Previous updates:
1.Added the import to team feature using text file introduced in ootp16. It's now possible to create entire team rosters. Just import the correct players and if their in the master.csv, Enter the team name plus year and they'll import to the correct team.
2.Added all Team IDs in ootp16 to vlookup table. You'll have to add your own teams and their correct ID to the vlookup sheet for fictional lgs or historical minor lg. teams.
3. Added the year to the end of the team names in vlookup to make sure players go to the correct team.
Make sure you add the year to the team name when assigning players to teams else it won't auto add the team id.

Download links 7zip file:
Historical 3.0 Plus OOTP Default Database

3.0 For Spritze's Database

Last yr. I released Historical Importer 2.0 for ootp that automated the creation of text files that ootp can use to auto import historical players.
3.0 Plus does the same and has more features. Works with all versions of ootp that allows you to import data via text file as well as Spritze's latest database here:
Spritze

Note: Import to team only works in ootp 16. Previous versions did not have this feature.

Unfortunately, I don't have excel so one will need Apache Open Office (it's free) as this is just a simple spreadsheet utility.
The features in 3.0 Plus so far are:
1. Updated to include all players with a lahman ID up to & including 2014 MLB debuts. 18,414 players in all. Fully sortable by name, year, country, state, city. Formulas will auto adjust so that data and links will/should always be correct.
2. Auto creates the correct text format (lahmanID,Year,TeamID) in its own column for use with ootp's historical import from text file feature. Just copy your finished results from that column to any text program and save. No limit on the number of players that can be imported at once.
3. You can create multiple text files on one sheet. No need to add separate sheets. Just clear the columns after you've copied it to your text editor.
4. Auto creation of the players bbref MLB page link. Just click the link to goto the player's bbref page.
5. Search players, teams, team affiliates, etc. on bbref's site. Simply input the players name or team abbreviation in caps (BOS,BAL). You can search teams by year as well.
6. Auto generated player page links can be used internally (using open office's built in Import data from external source) to automate importing any players/teams bbref stats (hitting, fielding, pitching, rosters and more) to a separate sheet in Open Office. Once imported, format them to ootp's specs and save as .txt files for import to ootp.
7. Fully expandable and editable by users. No data is locked or hidden that can't be unlocked and shown as nothing is password protected.
8. All columns that I felt needed them, have help comments and you need to read them. (Hidden by default). just hover your mouse over the cell to see them or unprotect the sheet & right click the column title and click show comment.

Any comments, problems, questions or suggestions. Post them here. If I don't respond within 24hrs. send me a PM please.

Note:
1. Although this is an Open Office file, it may well import to excel, but I highly doubt that the formulas will work so you'll have to get creative with that.
2. All sheets are protected and some columns are hidden. Read the comments if you don't know how to show the columns or unprotect the sheets.

Enjoy.

Last edited by Bigrod; 04-11-2015 at 12:57 AM.
Bigrod is offline   Reply With Quote
8 thanks for this post:
BradC (07-03-2015), daves (05-31-2015), Ike348 (01-09-2015), Jabez54 (01-09-2015), MarkInCincy (01-09-2015), pstrickert (04-25-2015), RoadWarriors84 (10-16-2015), The Game (03-31-2015)
Old 01-09-2015, 05:34 PM   #2
Ike348
All Star Starter
 
Ike348's Avatar
 
Join Date: May 2013
Location: Philadelphia
Posts: 1,245
Thanks: 1,348
Thanked 507x in 337 posts
I get Error(403), from Dropbox. Did you actually share the file, or did you just copy your own personal link?
__________________
Uniforms on my Photobucket

Quote:
Originally Posted by Ike348 View Post
Even in a parallel universe, the Astros don't make the playoffs.
Ike348 is offline   Reply With Quote
Thank you for this post:
Jabez54 (01-09-2015)
Old 01-10-2015, 07:11 AM   #3
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Quote:
Originally Posted by Ike348 View Post
I get Error(403), from Dropbox. Did you actually share the file, or did you just copy your own personal link?
Not sure what's up...everything was fine last night....I'm getting the same error now.
Should work fine now...not sure what happened.

Last edited by Bigrod; 01-10-2015 at 07:30 AM.
Bigrod is offline   Reply With Quote
Thank you for this post:
Jabez54 (01-10-2015)
Old 01-10-2015, 04:51 PM   #4
MarkInCincy
All Star Reserve
 
MarkInCincy's Avatar
 
Join Date: Oct 2012
Location: Covington, KY
Posts: 650
Thanks: 224
Thanked 265x in 182 posts
It just worked for me
__________________
"A baseball fan has the digestive apparatus of a billy goat. He can, and does, devour any set of statistics with insatiable appetite and then nuzzles hungrily for more." - Sportswriter Arthur Daley
"Who says there's an unemployment problem in this country? Just take the five percent unemployed and give them a baseball stat to follow." - Outfielder Andy Van Slyke
MarkInCincy is offline   Reply With Quote
Old 01-10-2015, 05:24 PM   #5
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Quote:
Originally Posted by MarkInCincy View Post
It just worked for me
Great and thanks for letting me know.
Bigrod is offline   Reply With Quote
Old 01-11-2015, 09:02 PM   #6
Ike348
All Star Starter
 
Ike348's Avatar
 
Join Date: May 2013
Location: Philadelphia
Posts: 1,245
Thanks: 1,348
Thanked 507x in 337 posts
Just worked. Thanks!
__________________
Uniforms on my Photobucket

Quote:
Originally Posted by Ike348 View Post
Even in a parallel universe, the Astros don't make the playoffs.
Ike348 is offline   Reply With Quote
Old 01-16-2015, 09:50 AM   #7
Arc Flash
Bat Boy
 
Arc Flash's Avatar
 
Join Date: Nov 2011
Posts: 19
Blog Entries: 1
Thanks: 15
Thanked 5x in 4 posts
import non-historical players?

Thanks Bigrod it works great with Apache Open Office.


I would like to know if this program can be used to import non-historical players from other leagues and if so how would I do it?
Arc Flash is offline   Reply With Quote
Old 01-16-2015, 02:47 PM   #8
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Quote:
Originally Posted by Arc Flash View Post
Thanks Bigrod it works great with Apache Open Office.


I would like to know if this program can be used to import non-historical players from other leagues and if so how would I do it?
Excellent question. Yes it sure can as long as that player is in the master.csv and has a lahmanID. You'll have to mod your master.csv and use it. You can't use the historical.odb file.

1. Give all players without a lahman ID a fake ID and make sure it's not the same as any other player's lahman id(you can use their bbref minors id for this purpose if you want it doesn't matter as long as it's unique). OOTP only imports player's according to their lahmanID irregardless of what other's may say.

2. Make sure those players have a Debut Year in the Debut Column (for those that don't have a lahman ID it would be the year they first appeared on any team in our database) and last yr. played in the Last yr. played column. For players still playing, last yr. is the current yr. (for ootp 15 it would be 2013 and for 16 it would be 2014).

BTW: I add the minor lg. stats every year for ootp and any player born during and after 1986, have their complete real life stats in our db. Only exceptions are the new leagues (DSL, VSL, Mexican as well as Int'l lgs.) that were added in 15. Those players may only have their 2013 stats as I didn't do those.


Ootp's process when importing from a text file, is to first lookup the lahman id in the master, then it looks for the year. It uses the year to import the player's associated data in other files (hitting, pitching, fielding, etc.) according to your import settings. You don't need to do anything with the "other" files.


I didn't include a modded master because once ootp 16 is out, I'm going to rewrite the utility...I got Markus to add a new feature which I can't say anything about until then. It's going to be awesome...I'll be testing it when beta starts and if it works like it's supposed to, I'll include a modded master.csv. Current plans are to have it ready for release same day as ootp16

Last edited by Bigrod; 01-16-2015 at 02:54 PM.
Bigrod is offline   Reply With Quote
Old 01-21-2015, 02:57 PM   #9
The Game
Hall Of Famer
 
The Game's Avatar
 
Join Date: Feb 2012
Location: Inside The Game
Posts: 20,665
Thanks: 9,229
Thanked 3,084x in 2,464 posts
Does this include any Negro league players from their rookie year and not just ones who played MLB?
__________________
Hello darkness, my old friend
I've come to talk with you again
Because a vision softly creeping
Left its seeds while I was sleeping
And the vision that was planted in my brain
Still remains
Within the sound of silence


Congrats Harold Baines

"Stay Strong"
The Game is offline   Reply With Quote
Old 01-22-2015, 12:38 PM   #10
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Quote:
Originally Posted by The Game View Post
Does this include any Negro league players from their rookie year and not just ones who played MLB?
It's currently based on ootp's master.csv. So if the player has a lahman id then yes if not, then no. You can however, mod the master.csv and this utility to include any player you want..,but I'd wait on that as the utility is going to get a major update.
It'll be a new feature and will also add more players. Can't say anymore than that at the moment.
If you have a special request, now's the time to let me know. I'll do my best to include it.
Bigrod is offline   Reply With Quote
Thank you for this post:
The Game (01-23-2015)
Old 01-23-2015, 10:43 PM   #11
MarkInCincy
All Star Reserve
 
MarkInCincy's Avatar
 
Join Date: Oct 2012
Location: Covington, KY
Posts: 650
Thanks: 224
Thanked 265x in 182 posts
Quote:
Originally Posted by Bigrod View Post
It's currently based on ootp's master.csv. So if the player has a lahman id then yes if not, then no. You can however, mod the master.csv and this utility to include any player you want..,but I'd wait on that as the utility is going to get a major update.
It'll be a new feature and will also add more players. Can't say anymore than that at the moment.
If you have a special request, now's the time to let me know. I'll do my best to include it.
Can you say it now????
__________________
"A baseball fan has the digestive apparatus of a billy goat. He can, and does, devour any set of statistics with insatiable appetite and then nuzzles hungrily for more." - Sportswriter Arthur Daley
"Who says there's an unemployment problem in this country? Just take the five percent unemployed and give them a baseball stat to follow." - Outfielder Andy Van Slyke
MarkInCincy is offline   Reply With Quote
Old 01-24-2015, 03:44 AM   #12
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Quote:
Originally Posted by MarkInCincy View Post
Can you say it now????
What the heck. Doubt Markus will fire me..
Got him to add a feature...you'll be able to import historical players directly to teams as long as those teams have a lahman team id. Works only when importing with a text file. Gonna need to test it out as soon as beta starts. If all goes well, should be able to import complete historical rosters. No more messing with moving free agents to teams.
Not sure yet if I'll be able to get it to work with historical minor lg. teams or not. Sure gonna try though.
Plus all the new 2014 players will be added.
Bigrod is offline   Reply With Quote
Old 01-24-2015, 06:35 AM   #13
MarkInCincy
All Star Reserve
 
MarkInCincy's Avatar
 
Join Date: Oct 2012
Location: Covington, KY
Posts: 650
Thanks: 224
Thanked 265x in 182 posts
Quote:
Originally Posted by Bigrod View Post
What the heck. Doubt Markus will fire me..
Got him to add a feature...you'll be able to import historical players directly to teams as long as those teams have a lahman team id. Works only when importing with a text file. Gonna need to test it out as soon as beta starts. If all goes well, should be able to import complete historical rosters. No more messing with moving free agents to teams.
Not sure yet if I'll be able to get it to work with historical minor lg. teams or not. Sure gonna try though.
Plus all the new 2014 players will be added.
So I can import ANY player and put him on ANY team as long as they have a lahman id and are in the historical lg I am importing them to?
__________________
"A baseball fan has the digestive apparatus of a billy goat. He can, and does, devour any set of statistics with insatiable appetite and then nuzzles hungrily for more." - Sportswriter Arthur Daley
"Who says there's an unemployment problem in this country? Just take the five percent unemployed and give them a baseball stat to follow." - Outfielder Andy Van Slyke
MarkInCincy is offline   Reply With Quote
Old 01-24-2015, 02:42 PM   #14
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Quote:
Originally Posted by MarkInCincy View Post
So I can import ANY player and put him on ANY team as long as they have a lahman id and are in the historical lg I am importing them to?
Yes as long as that team is in ootp's historical files. I'll know more for sure once testing starts in a few days.

Last edited by Bigrod; 01-24-2015 at 02:47 PM.
Bigrod is offline   Reply With Quote
Old 02-03-2015, 11:31 PM   #15
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Quote:
Originally Posted by MarkInCincy View Post
So I can import ANY player and put him on ANY team as long as they have a lahman id and are in the historical lg I am importing them to?
Done testing import to team feature for 16 and believe me, you guys are going to absolutely love this.
So the new answer to your question is:
Yes on the players lahman ID.
No they don't have to be in the historical lg. your importing them to.

Team ID is the ID that you see on the edit league structure page or you can get them by exporting the roster files.
So to import a player(s) to a team, you would use:
LahmanID,year,teamID.

As long as the players are in the Master.csv your using, you can import them directly to the team rosters whether it's fictional, historical or anything in between.

Last edited by Bigrod; 02-03-2015 at 11:42 PM.
Bigrod is offline   Reply With Quote
Thank you for this post:
MarkInCincy (02-04-2015)
Old 02-04-2015, 05:35 AM   #16
MarkInCincy
All Star Reserve
 
MarkInCincy's Avatar
 
Join Date: Oct 2012
Location: Covington, KY
Posts: 650
Thanks: 224
Thanked 265x in 182 posts
Quote:
Originally Posted by Bigrod View Post
Done testing import to team feature for 16 and believe me, you guys are going to absolutely love this.
So the new answer to your question is:
Yes on the players lahman ID.
No they don't have to be in the historical lg. your importing them to.

Team ID is the ID that you see on the edit league structure page or you can get them by exporting the roster files.
So to import a player(s) to a team, you would use:
LahmanID,year,teamID.

As long as the players are in the Master.csv your using, you can import them directly to the team rosters whether it's fictional, historical or anything in between.
That is awesomely fantastically super cool. Thanks, your work will make it SO much easier to do some leagues I have had in mind but never did because of the amount of manually moving players to teams and using a non-standard master.csv. As soon as I get my pre-order copy I will be starting my work

BIG THANKS to Bigrod

+100
__________________
"A baseball fan has the digestive apparatus of a billy goat. He can, and does, devour any set of statistics with insatiable appetite and then nuzzles hungrily for more." - Sportswriter Arthur Daley
"Who says there's an unemployment problem in this country? Just take the five percent unemployed and give them a baseball stat to follow." - Outfielder Andy Van Slyke
MarkInCincy is offline   Reply With Quote
Thank you for this post:
Bigrod (02-04-2015)
Old 02-04-2015, 10:20 PM   #17
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Quote:
Originally Posted by MarkInCincy View Post
That is awesomely fantastically super cool. Thanks, your work will make it SO much easier to do some leagues I have had in mind but never did because of the amount of manually moving players to teams and using a non-standard master.csv. As soon as I get my pre-order copy I will be starting my work

BIG THANKS to Bigrod

+100
Thanks...
One thing...when I tested this out I had to add some players to the master.csv (the master.csv only contains players that have debuted in MLB)...cause I was creating historical minors. I also had to rename the historical.odb file as it conflicted with the master.csv.
Renaming or moving the historical.odb file isn't necessary if you just copy all the other files in the stats folder to another folder and set ootp's path to use it.
I'll try to release the updated version when 16 preorder is released so's not to violate my DNR contract or whatever its called.
Bigrod is offline   Reply With Quote
Old 02-05-2015, 12:36 PM   #18
MarkInCincy
All Star Reserve
 
MarkInCincy's Avatar
 
Join Date: Oct 2012
Location: Covington, KY
Posts: 650
Thanks: 224
Thanked 265x in 182 posts
Quote:
Originally Posted by Bigrod View Post
Thanks...
One thing...when I tested this out I had to add some players to the master.csv (the master.csv only contains players that have debuted in MLB)...cause I was creating historical minors. I also had to rename the historical.odb file as it conflicted with the master.csv.
Renaming or moving the historical.odb file isn't necessary if you just copy all the other files in the stats folder to another folder and set ootp's path to use it.
I'll try to release the updated version when 16 preorder is released so's not to violate my DNR contract or whatever its called.
I plan on giving it a shot with Spritzes db when 16 is available (For me they have been called NDA's (Non-Disclosure Agreements) when I have had to sign them
__________________
"A baseball fan has the digestive apparatus of a billy goat. He can, and does, devour any set of statistics with insatiable appetite and then nuzzles hungrily for more." - Sportswriter Arthur Daley
"Who says there's an unemployment problem in this country? Just take the five percent unemployed and give them a baseball stat to follow." - Outfielder Andy Van Slyke
MarkInCincy is offline   Reply With Quote
Thank you for this post:
Bigrod (02-05-2015)
Old 03-07-2015, 10:14 PM   #19
MarkInCincy
All Star Reserve
 
MarkInCincy's Avatar
 
Join Date: Oct 2012
Location: Covington, KY
Posts: 650
Thanks: 224
Thanked 265x in 182 posts
So still the plan to release the next version when OOTP pre-order day arrives (soon but not soon enough)
__________________
"A baseball fan has the digestive apparatus of a billy goat. He can, and does, devour any set of statistics with insatiable appetite and then nuzzles hungrily for more." - Sportswriter Arthur Daley
"Who says there's an unemployment problem in this country? Just take the five percent unemployed and give them a baseball stat to follow." - Outfielder Andy Van Slyke
MarkInCincy is offline   Reply With Quote
Old 03-24-2015, 04:05 PM   #20
Bigrod
OOTP Stats Master
 
Join Date: Mar 2009
Location: Maine
Posts: 2,998
Thanks: 2,344
Thanked 2,050x in 1,179 posts
Within the next week or so, I'll be updating and adding the 2014 debuts and facilitating the import to team so that the correct format auto fills as you add players.
Bigrod is offline   Reply With Quote
Thank you for this post:
MarkInCincy (03-25-2015)
Reply

Bookmarks

Tags
historical import, utility ootp

Thread Tools

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 On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 11:59 AM.

 

Major League Baseball trademarks and copyrights are used with permission of MLB Advanced Media, L.P. Minor League Baseball trademarks and copyrights are used with the permission of Minor League Baseball. All rights reserved.

The Major League Baseball Players Association (www.MLBPLAYERS.com ) is the collective bargaining representative for all professional baseball players of the thirty Major League Baseball teams and serves as the exclusive group licensing agent for commercial and licensing activities involving active Major League baseball players. On behalf of its members, it operates the Players Choice licensing program and the Players Choice Awards, which benefit the needy through the Major League Baseball Players Trust, a charitable foundation established and run entirely by Major League baseball players. Follow: @MLB_Players; @MLBPAClubhouse; @MLBPlayersTrust

Out of the Park Baseball is a registered trademark of Out of the Park Developments GmbH & Co. KG

Google Play is a trademark of Google Inc.

Apple, iPhone, iPod touch and iPad are trademarks of Apple Inc., registered in the U.S. and other countries.

COPYRIGHT © 2017 OUT OF THE PARK DEVELOPMENTS. ALL RIGHTS RESERVED.

 

Powered by vBulletin® Version 3.8.10
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Copyright © 2018 Out of the Park Developments