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