Baseball Hacks

Load Baseball Data into MySQL

Use a Perl script to generate MySQL statements automatically for loading baseball data into MySQL.

Most baseball data on the Web is available as flat files: text files where a tab, a comma, or another character separates each element. For example, here is the AwardsManagers table from the Baseball Archive data:

	"managerID","awardID","yearID","lgID","tie","notes"
	"larusto01m","Mgr of the year",1983,"AL",,
	"lasorto01m","Mgr of the year",1983,"NL",,
	"andersp01m","Mgr of the year",1984,"AL",,
	…

The first line contains the header information (the name of each column). Each subsequent line represents a different manager award for a different league. Text fields are enclosed in quotes and are separated by commas. The fields are (from left to right): ID code, award name, year, league, a field that indicates ties, and notes.

As you can see, it is easy for a person to read these files. Often, it is convenient to create or edit files like these. For example, I show how to create formatted text files like these in “Make Box Scores or Database Tables from Play-by-Play Data with Retrosheet Tools” [Hack #15] . However, it’s much easier to read these files using a database because it lets you search easily for specific lines or summarize the information in a file.

To load a text file like this into a database, you have to define an appropriate table type [Hack #16] . Writing a statement like this can be tedious, especially if you don’t know the length of each field. ...