Baseball Hacks

Move Data from a Database to Excel

Get data out of your database and into Microsoft Excel for easy analysis.

In “Get and Install MySQL” [Hack #8] , I listed a bunch of reasons why databases can be better than spreadsheets: database fields are strongly typed, database changes are transactional, database queries can be used to select data, database joins can be used to merge data, database relationships can be defined between data, and database file sizes can be very large. For storing and fetching data, I also think databases are better.

However, for analyzing and inspecting data, I prefer a spreadsheet. You can do a lot of things easily with spreadsheets that you can’t do easily with a database—for instance, plot charts, create pivot tables, and do statistical calculations. In this hack, I’ll show you a few ways to get data out of a database and into Microsoft Excel.

Select the Right Data for a Spreadsheet

Spreadsheets are very powerful, but they also have some limitations. Here are a few suggestions on how to select and format your data to make the most useful spreadsheet:

Table sizes

Make sure there are fewer than 255 columns and 65,534 rows in the table. The limit is 65,535 rows, but you need to save one for column headers!

Counts versus ratios

In general, I think it is better to export counts than averages. Here’s an example that illustrates why this is.

Suppose you want to calculate team batting averages from individual player statistics. Would it be better to export hits (H) and ...