Archive for January 2013
Turning Excel spreadsheets into searchable databases in five minutes
Note: This is cross-posted from Lee’s data journalism blog and includes references to the Blox content management system, which is what Lee newspapers use. Reporters at Lee newspapers can read my blog over there by clicking here.
Here’s a scenario I run into all the time: A government agency sends along a spreadsheet of data to go along with a story one of our reporters is working on. And you want to post the spreadsheet(s) online with the story in a reader-friendly way.
One way is to create a sortable database with the information. There are a couple of awesome options on the table put out by other news organizations. One is TableSetter, published by ProPublica, which we’ve used in the past. The other one is TableStacker, which is a spin off of TableSetter. We have also used this before.
The only problem is making these tables typically take a little bit of leg work. And of course, you’re on deadline.
Here’s one quick and dirty option:
1. First open the spreadsheet in Excel or Google Docs and highlight the fields you want to make into the sortable database.
2. Then go to this wonderful website called Mr. Data Converter and paste the spreadsheet information into the top box. Then select the output as “HTML.”
3. We will use a service called DataTables to create the sortable databases. It’s a great and easy jQuery plugin to create the sortable tables.
4. Now create an HTML asset in Blox and paste in this DataTables template below:
Note: I’ve added some CSS styling to make the tables look better.
<html>
<head>
<link rel="stylesheet" type="text/css" href="http://wcfcourier.com/app/special/data_tables/media/css/demo_page.css">
<link rel="stylesheet" type="text/css" href="http://wcfcourier.com/app/special/data_tables/media/css/demo_table.css">
<style>
table {
font-size: 12px;
font-family: Arial, Helvetica, sans-serif;
float: left
}
table th, table td {
text-align: center;
}
th, td {
padding-top: 10px;
padding-bottom: 10px;
font-size: 14px;
}
label {
width: 100%;
text-align: left;
}
table th {
font-weight: bold;
}
table thead th {
vertical-align: middle;
}
label, input, button, select, textarea {
line-height: 30px;
}
input, textarea, select, .uneditable-input {
height: 25px;
line-height: 25px;
}
select {
width: 100px;
}
.dataTables_length {
padding-left: 10px;
}
.dataTables_filter {
padding-right: 10px;
}
</style>
<script type="text/javascript" language="javascript" src="http://wcfcourier.com/app/special/data_tables/media/js/jquery.js"></script>
<script type="text/javascript" language="javascript" src="http://wcfcourier.com/app/special/data_tables/media/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
$('#five_year').dataTable({
"iDisplayLength": 25
});
});
</script>
</head>
<body>
<--- Enter HTML table here --->
</body>
</html>
– This will link the page to the necessary CSS spreadsheets and Javascript files to get the DataTable working. The other option is go to the DataTable’s website and download the files yourself and post them on your own server, then link to those files instead of the ones hosted by WCFCourier.com.
5. Where you see the text “<— Enter HTML table here —>,” paste in your HTML table from Mr. Data Converter.
6. The last thing you will need to do is create an “id” for the table and link that “id” to the DataTable’s plugin. In the example above, the “id” is “five_year.” It is noted in this line of code in the DataTable template:
<script type="text/javascript" charset="utf-8">
$(document).ready(function() {
$('#five_year').dataTable({
"iDisplayLength": 25
});
});
</script>
– The header on your HTML table that you post into the template will look like so:
<table id="five_year" style="width: 620px;">
<thead>
<tr>
<th class="NAME-cell">NAME</th>
<th class="2008 Enrollment-cell">2008 Enrollment</th>
<th class="2012 Enrollment-cell">2012 Enrollment</th>
<th class="Increase/Decrease-cell">Increase/Decrease</th>
<th class="Percent Increase/Decrease-cell">Percent Increase/Decrease</th>
</tr>
</thead>
– Here’s an live example of two sortable tables. The first table has an “id” of “five_year.” The second has an “id” of “one_year.” The full code for the two tables is available here.
– As an alternative, you can use a jQuery plugin called TableSorter (not to be confused with the TableSorter project mentioned above). The process of creating the table is very similar.
7. That’s it! Of course, DataTables provides several customization options that are worth looking into if you want to make your table look fancier.
Chris Essig 