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:

	"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. ...

Get Baseball Hacks now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.