When you ask a database for information, the result of the query is a table or “rowset”. People who are good at database management write queries that slice and dice multiple tables, perform calculations on data, and format it all before it ever gets reported back.
I’m not one of the people who are good at database management, so I generally retrieve more data than I need. I slice and dice it with Perl Data::Table.
Once you’ve connected to the database server, Data::Table, can run your query for you:
$sql = qq/SELECT * FROM Persons WHERE Gender="F"/ ; $t = Data::Table::fromSQL($dbh, $sql); # $dbh = DB handler
With the resulting data structure, you can:
- sort on any column
- print HTML
- add or delete rows and columns
- rename, reorder, swap columns
- extract a subtable by matching cells to a formula
(e.g., “DOB”>1978) - extract a subtable by matching a string (regexp)
- clone the table
- run a subroutine on every item in a column
- run a subroutine on every row (e.g., put (cell6 + cell5) into cell8)
- merge rows and cells into new tables
- treat every row as a Perl array (indexed list) or a Perl hash (key-value pairs)
It seems particularly good at dealing with tab-delimited text files exported from Excel.
Data::Table was written by Yingyao Zhou and Guangzhou Zou. You can find it on CPAN.