How do I get Lahman's dbase into excel to edit?

LetsPlayTwo · 04-18-2002, 02:16 PM

I have Lahman's stats. But some download wrong if a player had been traded in a year (I've been told it takes the stats from each team and adds them up to get a total, then adds the total throwing it all out of whack. I was told by Billr taht I can manipulate the stats if I have excel - which I do. I am not a cumputer genius, so the trick for me is how do I get the Lahman dbase into Excel to edit. THEN once done editing what do I do from there? Save the dbase somewhere? Then redownload specific teams I want? I need a little hand holding here? Any help is appreciated!

blmeanie · 04-18-2002, 02:41 PM

When you are in Excel, click open, then change the file type to *.* (all files). Find the directory that you saved the data files to, they should be there as .csv files. You can open them in excel but the batting and pitching files are too big (if I remember correctly) for excel to handle, and will open on 65000 rows or so. After opening and making changes, you will need to save back to type csv (it should default to it).

I believe that Scottvib has been working on some of the corrections you described. I believe he will be uploading the files somewhere when he is done.

Scott Vibert · 04-18-2002, 03:30 PM

I am almost done. Acutally most of it is done.. just a few more players to finish getting bio info for, unfortunately work/life got in the way a bit or I would have had it posted for all on Tuesday.

Scott Vibert · 04-18-2002, 03:31 PM

Also You can't import the pitching.csv into Excel, its too big, you'll have to bring it into Word (or at least I had too). Anyways I'll make my 'fixed' version by this weekend (which was my original plan anyway.

)

LetsPlayTwo · 04-18-2002, 03:34 PM

Thanks, I'll give it a try. I never thought I'd get help like that from a Sox fan being that I am a Yanks fan (BIGGEST Thurman Munson fan ever!).

Seriously - Thank you!

IatricSB · 04-18-2002, 03:41 PM

Even if the file wasn't too big for Excel, I don't think you can edit with Excel. When I tried to do it with version 4, it saved the file with a bunch of extra "quotes" around fields and the file was unable to be imported by OOTP3. I'm assuming it is the same with OOTP4 and v4.5, but I haven't tried it.

Scott Vibert · 04-18-2002, 03:43 PM

<blockquote>quote:<hr />Originally posted by LetsPlayTwo:
I never thought I'd get help like that from a Sox fan being that I am a Yanks fan (BIGGEST Thurman Munson fan ever!).

Seriously - Thank you!<hr /></blockquote>That's ok I have no problems helping the misguided. <img border="0" title="" alt="[Wink]" src="wink.gif" />

LetsPlayTwo · 04-18-2002, 03:55 PM

Scottvib,
What will your 'fixed' version consist of just so I know. Will it have each Player's combined stats for the year from all teams played on? Or will it have Jack Lamabe's stats for the just the games he played as a Cardinal when I download the '67 Cardinals?

Thank you so much for having taken the time to do this! I was just about to start once I found out how to do it! You've saved me a lot of work!

Scott Vibert · 04-18-2002, 04:08 PM

I simply removed the lines for TOT stats, which caused the statistical inaccuracies... so you'll see only one copy of the players stats and the teams they played for noted in the normal fashion: STL, CIN.

Additionally I went through and filled in biographical information for quite a few modern players who were missing the information. (All players who debuted from 1998 onwards for example).

LetsPlayTwo · 04-18-2002, 04:17 PM

ScottVib,
You lost me on the fact that you just removed the totalspart of the stats. I have the batting stats opened up right now in WORD and all it has for each player is a bunch of numbers and commas. Fortunately I can also look into ootp4 and see what these numbers equate to by checking the player's 'real life stats.' But nowhere do I see an area for totals. Again I'm in the batting.csv file opened in WORD.

Scott Vibert · 04-18-2002, 04:23 PM

You'll notice the player has a three letter team ID field, for the pitchers with the messed up stats, they have team ID for each team they were on, as well as one labeled TOT, with their totals.

OOTP already aggregated stats for each team, so by adding the TOT field for the pitchers, the game thought there was another team and ended up doubling the players stats.

By deleting all entries for team TOT, you fix this problem, and the players will import with the correct stats.

LetsPlayTwo · 04-18-2002, 04:56 PM

Okay ScottVib,
Let's take it slow. I understand what you are saying about removing the TOTS. But I read a suggestion from you to open the batting and pitching in WORD because Excel can't handle it. That's what I did. I don't know if my WORD program is displaying something different than yours is, but I don't anaything anywhere where each player has his stats broken down with TEAM A, TEAM B, TEAM C, TOTALS. I see the year, the player's name then numbers and commas next to that. Am I doing something wrong? I am only doing this editing for select teams so that is why I am anxious - so I can get playing my historical showdown between all of the World Series Champs.

Also this happens for the batting as well does it not? referring to the '87 Twins again - Don Baylor and Mark Salas have way more ABs in ootp than their (real life) '87 Twins stats show. Just wondering if you knew.

Markmeister · 04-18-2002, 06:13 PM

Actually, there is a way to edit the pitching and batting files in Excel. Just open the files up in Word, cut up into smaller pieces (i.e. batting1, batting2, etc.), edit how you want in Excel, and re-piece back together.

Hey, I didn't say it was easy. <img border="0" title="" alt="[Wink]" src="wink.gif" /> I actually used this method to edit the files in the database program that is part of Microsoft Works. Limited to only 32,000 records so I cut the files up so I could resort and add back in the 1871-1875 stats. Forgot about deleting the TOT lines so I need to fix that though.

By the way, I think I have partially figured out how to adjust Iatric's Modified DB to work w/ Lahman 4.5. For the batting DB - need to add a Total Bases column after Hits (column #8) and add a formula for total bases to that column. The pitching file is easier - just move the games pitched column two columns to the left (needs to be immediately after the league abreviation and not after the losses column). I haven't looked at the fielding file yet.

statfreak · 04-18-2002, 06:21 PM

<blockquote>quote:<hr />Originally posted by Markmeister:
Actually, there is a way to edit the pitching and batting files in Excel. Just open the files up in Word, cut up into smaller pieces (i.e. batting1, batting2, etc.), edit how you want in Excel, and re-piece back together.

Hey, I didn't say it was easy. <img border="0" title="" alt="[Wink]" src="wink.gif" /> I actually used this method to edit the files in the database program that is part of Microsoft Works. Limited to only 32,000 records so I cut the files up so I could resort and add back in the 1871-1875 stats. Forgot about deleting the TOT lines so I need to fix that though.

By the way, I think I have partially figured out how to adjust Iatric's Modified DB to work w/ Lahman 4.5. For the batting DB - need to add a Total Bases column after Hits (column #8) and add a formula for total bases to that column. The pitching file is easier - just move the games pitched column two columns to the left (needs to be immediately after the league abreviation and not after the losses column). I haven't looked at the fielding file yet.<hr /></blockquote>I have taken the batting and pitching files from the Lahman 4.5 database and replaced a player's rookie year stats with their career averages. You can download the modified batting and pitching files from my website. Please note these files may only be used for importing rookies into an historic league.

<a href="http://www.statfrk.com/OOTP.html" target="_blank">http://www.statfrk.com/OOTP.html</a>

statfreak · 04-18-2002, 06:24 PM

I had no problem opening the pitching.csv in excel. Simply opened excel, then File - Open changed file type to all and opened the pitching.csv from the Lahman 4.5 database.

I had to use Quattro Pro to open the batting and fielding csv's. Then broke them up into smaller files and opened in excel with no problem.

I was also able to save as a .csv file from excel without any problems as well.

lpoulas · 04-20-2002, 07:47 AM

Guys, I tried to follow this but I am stuck.

The only way I can get the files to load into OOTP4 is if the have a file type of "Microsoft Excel Comma Separated Values File".

Now, I can't do this from word. It only lets me save it as a text file which OOTP doesn't read. I can't do it from Access either.

The only way I can save a file as this type is from excel - but as it was pointed out excel doesn't have enough rows to handle all of the data.

I just want to manipulate the Lahman DB and then import it into OOTP4 AND I CAN'T DO IT!!!!

Any help would be most appreciated.

Thanks
lou

LetsPlayTwo · 04-20-2002, 09:32 AM

I'm with you Ipoulus! If we simply use the Lahman dbase, then too many players are going to have screwed up ratings due to stats being inflated. ScottVib said he is almost finished editing the entire dbase by removing the totals of each player which should make them statistically right on. He also said it will probably be done this weekend. I'm simply waiting patiently.

Scott Vibert · 04-20-2002, 11:27 AM

It's done... I'll be sending it to Henry or someone for hosting shortly.

04-18-2002, 02:16 PM	#1
LetsPlayTwo Minors (Rookie Ball) Join Date: Apr 2002 Location: Pontiac, MI Posts: 28	How do I get Lahman's dbase into excel to edit? I have Lahman's stats. But some download wrong if a player had been traded in a year (I've been told it takes the stats from each team and adds them up to get a total, then adds the total throwing it all out of whack. I was told by Billr taht I can manipulate the stats if I have excel - which I do. I am not a cumputer genius, so the trick for me is how do I get the Lahman dbase into Excel to edit. THEN once done editing what do I do from there? Save the dbase somewhere? Then redownload specific teams I want? I need a little hand holding here? Any help is appreciated!

04-18-2002, 02:41 PM	#2
blmeanie All Star Starter Join Date: Dec 2001 Location: Atlanta Posts: 1,044	When you are in Excel, click open, then change the file type to . (all files). Find the directory that you saved the data files to, they should be there as .csv files. You can open them in excel but the batting and pitching files are too big (if I remember correctly) for excel to handle, and will open on 65000 rows or so. After opening and making changes, you will need to save back to type csv (it should default to it). I believe that Scottvib has been working on some of the corrections you described. I believe he will be uploading the files somewhere when he is done. __________________ blmeanie email me at blmeanie Above link works for some email client programs but not all, email me at blmeanie33@earthlink.net

04-18-2002, 03:41 PM	#6
IatricSB Hall Of Famer Join Date: Dec 2001 Location: California Posts: 3,493	Even if the file wasn't too big for Excel, I don't think you can edit with Excel. When I tried to do it with version 4, it saved the file with a bunch of extra "quotes" around fields and the file was unable to be imported by OOTP3. I'm assuming it is the same with OOTP4 and v4.5, but I haven't tried it. __________________ Life is not a journey to the grave with the intention of arriving safely in a pretty and well preserved body; but rather to skid in broadside, thoroughly used up, totally worn out, and loudly proclaiming, "Wow! What a Ride!" Chicago(N) - Boys of Summer Oakland - 20th Century League Bakersfield - Wild Things Brooklyn - QBA Dodge City - NBSL California - ABC Dodger's Senioriest fan on the OOTP Boards

04-18-2002, 03:43 PM	#7
Scott Vibert Hall Of Famer Join Date: Dec 2001 Location: My Computer Posts: 8,210	</font><blockquote><font size="1" face="Verdana, Arial">quote:</font><hr /><font size="2" face="Verdana, Arial">Originally posted by LetsPlayTwo: <strong>I never thought I'd get help like that from a Sox fan being that I am a Yanks fan (BIGGEST Thurman Munson fan ever!). Seriously - Thank you!</strong></font><hr /></blockquote><font size="2" face="Verdana, Arial">That's ok I have no problems helping the misguided. <img border="0" title="" alt="[Wink]" src="wink.gif" />

04-18-2002, 06:13 PM	#13
Markmeister Major Leagues Join Date: Feb 2002 Location: Kentucky Posts: 463	Actually, there is a way to edit the pitching and batting files in Excel. Just open the files up in Word, cut up into smaller pieces (i.e. batting1, batting2, etc.), edit how you want in Excel, and re-piece back together. Hey, I didn't say it was easy. <img border="0" title="" alt="[Wink]" src="wink.gif" /> I actually used this method to edit the files in the database program that is part of Microsoft Works. Limited to only 32,000 records so I cut the files up so I could resort and add back in the 1871-1875 stats. Forgot about deleting the TOT lines so I need to fix that though. By the way, I think I have partially figured out how to adjust Iatric's Modified DB to work w/ Lahman 4.5. For the batting DB - need to add a Total Bases column after Hits (column #8) and add a formula for total bases to that column. The pitching file is easier - just move the games pitched column two columns to the left (needs to be immediately after the league abreviation and not after the losses column). I haven't looked at the fielding file yet. __________________ It's not that baseball has ever had a shortage of ways to embarrass yourself -- dropping an easy flyball, being the one guy Heathcliff Slocumb actually struck out, realizing you're Bud Selig... Jersey style logos Vintage letter logos Vintage uniforms

04-18-2002, 03:30 PM	#3
Scott Vibert Hall Of Famer Join Date: Dec 2001 Location: My Computer Posts: 8,210	I am almost done. Acutally most of it is done.. just a few more players to finish getting bio info for, unfortunately work/life got in the way a bit or I would have had it posted for all on Tuesday.

04-18-2002, 03:31 PM	#4
Scott Vibert Hall Of Famer Join Date: Dec 2001 Location: My Computer Posts: 8,210	Also You can't import the pitching.csv into Excel, its too big, you'll have to bring it into Word (or at least I had too). Anyways I'll make my 'fixed' version by this weekend (which was my original plan anyway. )

04-18-2002, 03:34 PM	#5
LetsPlayTwo Minors (Rookie Ball) Join Date: Apr 2002 Location: Pontiac, MI Posts: 28	Thanks, I'll give it a try. I never thought I'd get help like that from a Sox fan being that I am a Yanks fan (BIGGEST Thurman Munson fan ever!). Seriously - Thank you!

04-18-2002, 03:55 PM	#8
LetsPlayTwo Minors (Rookie Ball) Join Date: Apr 2002 Location: Pontiac, MI Posts: 28	Scottvib, What will your 'fixed' version consist of just so I know. Will it have each Player's combined stats for the year from all teams played on? Or will it have Jack Lamabe's stats for the just the games he played as a Cardinal when I download the '67 Cardinals? Thank you so much for having taken the time to do this! I was just about to start once I found out how to do it! You've saved me a lot of work!

04-18-2002, 04:08 PM	#9
Scott Vibert Hall Of Famer Join Date: Dec 2001 Location: My Computer Posts: 8,210	I simply removed the lines for TOT stats, which caused the statistical inaccuracies... so you'll see only one copy of the players stats and the teams they played for noted in the normal fashion: STL, CIN. Additionally I went through and filled in biographical information for quite a few modern players who were missing the information. (All players who debuted from 1998 onwards for example).

04-18-2002, 04:17 PM	#10
LetsPlayTwo Minors (Rookie Ball) Join Date: Apr 2002 Location: Pontiac, MI Posts: 28	ScottVib, You lost me on the fact that you just removed the totalspart of the stats. I have the batting stats opened up right now in WORD and all it has for each player is a bunch of numbers and commas. Fortunately I can also look into ootp4 and see what these numbers equate to by checking the player's 'real life stats.' But nowhere do I see an area for totals. Again I'm in the batting.csv file opened in WORD.

04-18-2002, 04:23 PM	#11
Scott Vibert Hall Of Famer Join Date: Dec 2001 Location: My Computer Posts: 8,210	You'll notice the player has a three letter team ID field, for the pitchers with the messed up stats, they have team ID for each team they were on, as well as one labeled TOT, with their totals. OOTP already aggregated stats for each team, so by adding the TOT field for the pitchers, the game thought there was another team and ended up doubling the players stats. By deleting all entries for team TOT, you fix this problem, and the players will import with the correct stats.

04-18-2002, 04:56 PM	#12
LetsPlayTwo Minors (Rookie Ball) Join Date: Apr 2002 Location: Pontiac, MI Posts: 28	Okay ScottVib, Let's take it slow. I understand what you are saying about removing the TOTS. But I read a suggestion from you to open the batting and pitching in WORD because Excel can't handle it. That's what I did. I don't know if my WORD program is displaying something different than yours is, but I don't anaything anywhere where each player has his stats broken down with TEAM A, TEAM B, TEAM C, TOTALS. I see the year, the player's name then numbers and commas next to that. Am I doing something wrong? I am only doing this editing for select teams so that is why I am anxious - so I can get playing my historical showdown between all of the World Series Champs. Also this happens for the batting as well does it not? referring to the '87 Twins again - Don Baylor and Mark Salas have way more ABs in ootp than their (real life) '87 Twins stats show. Just wondering if you knew.

04-18-2002, 06:21 PM	#14
statfreak Hall Of Famer Join Date: Dec 2001 Location: Milwaukee, WI Posts: 2,434	</font><blockquote><font size="1" face="Verdana, Arial">quote:</font><hr /><font size="2" face="Verdana, Arial">Originally posted by Markmeister: <strong>Actually, there is a way to edit the pitching and batting files in Excel. Just open the files up in Word, cut up into smaller pieces (i.e. batting1, batting2, etc.), edit how you want in Excel, and re-piece back together. Hey, I didn't say it was easy. <img border="0" title="" alt="[Wink]" src="wink.gif" /> I actually used this method to edit the files in the database program that is part of Microsoft Works. Limited to only 32,000 records so I cut the files up so I could resort and add back in the 1871-1875 stats. Forgot about deleting the TOT lines so I need to fix that though. By the way, I think I have partially figured out how to adjust Iatric's Modified DB to work w/ Lahman 4.5. For the batting DB - need to add a Total Bases column after Hits (column #8) and add a formula for total bases to that column. The pitching file is easier - just move the games pitched column two columns to the left (needs to be immediately after the league abreviation and not after the losses column). I haven't looked at the fielding file yet.</strong></font><hr /></blockquote><font size="2" face="Verdana, Arial">I have taken the batting and pitching files from the Lahman 4.5 database and replaced a player's rookie year stats with their career averages. You can download the modified batting and pitching files from my website. Please note these files may only be used for importing rookies into an historic league. <a href="http://www.statfrk.com/OOTP.html" target="_blank">http://www.statfrk.com/OOTP.html</a> __________________ Roll out the barrel!

04-18-2002, 06:24 PM	#15
statfreak Hall Of Famer Join Date: Dec 2001 Location: Milwaukee, WI Posts: 2,434	I had no problem opening the pitching.csv in excel. Simply opened excel, then File - Open changed file type to all and opened the pitching.csv from the Lahman 4.5 database. I had to use Quattro Pro to open the batting and fielding csv's. Then broke them up into smaller files and opened in excel with no problem. I was also able to save as a .csv file from excel without any problems as well. __________________ Roll out the barrel!

04-20-2002, 07:47 AM	#16
lpoulas Minors (Single A) Join Date: Apr 2002 Location: Jersey Posts: 75	Guys, I tried to follow this but I am stuck. The only way I can get the files to load into OOTP4 is if the have a file type of "Microsoft Excel Comma Separated Values File". Now, I can't do this from word. It only lets me save it as a text file which OOTP doesn't read. I can't do it from Access either. The only way I can save a file as this type is from excel - but as it was pointed out excel doesn't have enough rows to handle all of the data. I just want to manipulate the Lahman DB and then import it into OOTP4 AND I CAN'T DO IT!!!! Any help would be most appreciated. Thanks lou __________________ Commissioner, <a href="http://www.geocities.com/lpoulas/ATB.html" target="_blank">All Time Baseball</a>

04-20-2002, 09:32 AM	#17
LetsPlayTwo Minors (Rookie Ball) Join Date: Apr 2002 Location: Pontiac, MI Posts: 28	I'm with you Ipoulus! If we simply use the Lahman dbase, then too many players are going to have screwed up ratings due to stats being inflated. ScottVib said he is almost finished editing the entire dbase by removing the totals of each player which should make them statistically right on. He also said it will probably be done this weekend. I'm simply waiting patiently.

04-20-2002, 11:27 AM	#18
Scott Vibert Hall Of Famer Join Date: Dec 2001 Location: My Computer Posts: 8,210	It's done... I'll be sending it to Henry or someone for hosting shortly.