Chris Essig

Walkthroughs, tips and tricks from a data journalist in eastern Iowa

Turning Excel spreadsheets into searchable databases in five minutes

with 20 comments

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.

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

Advertisements

Written by csessig

January 3, 2013 at 4:34 pm

20 Responses

Subscribe to comments with RSS.

  1. Chris – you’re my new favourite persion, I’ve been looking for a solution like this for A-ges. Thanks so much for sharing your workings.

    kungfuelvis

    May 21, 2013 at 7:27 am

  2. i’ve done what you said but where can i view it working?

    john sothern

    August 21, 2013 at 3:39 am

    • Hey John… You should create an HTML file with the code outlined in the walkthrough. You can create an HTML file with Dreamweaver, Text Wrangle, Sublime Text, or any other text editor programs out there. When you’re done, you can open that HTML file in any web browser: Chrome, Firefox, etc.

      Do you have an HTML file created?

      csessig

      September 18, 2013 at 10:32 am

  3. Hello, will this work with Embed Code Element such on a site from Weebly.com? Will it just display table data or also work with functionality. It would be great if you can instruct how to implement in a weebly site.

    THOMAS

    November 23, 2013 at 2:10 am

  4. Hello, any reply to the NOV23 comment?

    THOMAS

    January 22, 2014 at 11:40 pm

    • Hey Thomas, sorry for the delay in responding. I’m not too familiar with the embed code element on Weebly.com. But my guess is if you can create a new HTML document that has the table in it, you can save that HTML document and put it online somewhere. Then you could embed that HTML document.

      For instance, here’s one table I made a few months ago. I saved it as an HTML document on my computer and then uploaded it to the Courier’s server: http://wcfcourier.com/app/special/homeschool.html

      We embedded it on our website like so: http://wcfcourier.com/table-homeschooling-in-iowa/html_58793e56-171b-11e3-9882-0019bb2963f4.html

      The actual embed code was:

      iframe src=”http://wcfcourier.com/app/special/homeschool.html” frameborder=”0″ style=”width:100%; height: 1250px; padding-left: 0px; padding-right: 0px;”>

      Note: I didn’t include the opening bracket because WordPress don’t allow full embed codes to show up in their comments.

      Again, I can only speak generally because I'm not familiar with Weebly.com. If you have any questions or are confused about anything, please let me know. I'll promise I'll get back to you faster!

      Chris

      csessig

      January 23, 2014 at 11:17 am

  5. Hi Chris, thanks so much for your detailed reply. Could I maybe ask you to run a small test? Just open a free webpage at weebly.com and try to use the HMTL Xode box in the weebly system with your code. It will take you 5-10 min, me much much longer. Could you try and let me know if it works? Thanks so much, THOMAS

    THOMAS

    January 23, 2014 at 10:55 pm

    • As far as I can tell, you should be able to paste in that iframe code.

      csessig

      January 24, 2014 at 12:13 pm

  6. Sure I can copy/paste. I don’t know if jQuery works in Weebly (HTML code box), maybe the code needs some modification, that’s why I asked if you could run a little test … you are the EXPERT here 🙂

    THOMAS

    January 25, 2014 at 1:23 am

    • Is it possible to have two search bars for the same table?

      Like if you want to search two separate columns to narrow your results. In your example, if the first search field was just for the school name and the second search field was for a specific number. Could you help write that code?

      Thanks!

      avrahmk

      June 22, 2015 at 10:54 am

  7. Is it possible to have two search bars for the same table?

    Like if you want to search two separate columns to narrow your results. In your example, if the first search field was just for the school name and the second search field was for a specific number. Could you help write that code?

    Thanks!

    avrahmk

    June 22, 2015 at 10:56 am

  8. I had an Excel Data Base. I want to turn this date base into a WordPress Website. I want to design the site like http://pincode.net.in/

    Is it possible to make like the above website. I had the same database which they have. I want to make website to find out PINCODE of India by State Search, District Search….etc.

    Thanks in advance.

    Sathish Gnananandam

    October 20, 2015 at 2:11 am

    • Hello Sathish,

      I missed seeing this comment until now. I apologize. Are you still have troubles?

      Chris

      csessig

      January 28, 2016 at 5:04 pm

  9. I’m stuck on step 4: Now create an HTML asset in Blox….what/where is “blox”??? Help??

    Tina

    September 11, 2016 at 6:19 pm

    • Nevermind, I see now that I missed the sentence in the opening that Blox is a CMS. Do you know how to do this in WordPress?

      Tina

      September 11, 2016 at 6:26 pm

      • I don’t know for sure. How far along are you?

        csessig

        September 11, 2016 at 6:54 pm

      • I have a spreadsheet of info.

        But I found a WordPress plugin that does it easy-peasy. You just paste the public link to the spreadsheet into a post and it immediately generates the searchable directory. Woohoo!

        Works great so far. So nevermind. But thanks!

        tinamama

        September 11, 2016 at 8:53 pm

      • Good to hear!

        csessig

        September 12, 2016 at 6:56 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: