Helm provides tools to let you connect to display and use data from a wide variety of sources. So for example if your traffic system provides you access to their database you can pull in data about upcoming program events and use this to drive on-air graphics. Here we’ll show something a lot simpler: getting data from an Excel spreadsheet and displaying it in a Helm panel.  You can download the example panel here.

Spreadsheet

media_1444650439560.png

Here we have an Excel table of our staff and their refreshment preferences.

Helm panel at runtime

media_1444650535964.png

And here is the same data as a Table in Helm. Now let’s see how we achieved that.

Panel setup in Helm Designer

media_1444650628952.png

This panel has only two items:

  1. a Table control
  2. an SQL Dataset device

The Dataset does the work of extracting data from the Spreadsheet, and the Table displays it.

Setting up the Table

media_1444650876419.png

All you really need to set for this is the Property Link. Just point this to the Dataset : TableData property.

Setting up the SQL Dataset

media_1444651018649.png

The Dataset device needs two properties set to establish a connection and feed data to the target control:

  1. Query: this is a standard SQL query. In this example we’ve chosen to take all the data from the TestData Excel table.
  2. ConnectionString: this is where we set up the ODBC connection to the database (Excel spreadsheet) we’re using.

Setting up ODBC can be a bit tricky at times so let’s see that in a bit more detail.

ODBC set the provider

media_1444651461022.png

Depending on the data source you select the OLE DB Provider. In this case we’re using ODBC drivers.

ODBC setting the connection

media_1444662455432.png

For an Excel file the easiest way to do this is to use the Build button.

Building a connection string

media_1444662626104.png

Click the New… button to create a new ODBC data source

Select the Excel driver

media_1444662746813.png

Select the Excel (.xls) driver and click Next.

Save the data source file

media_1444664398106.png

In this case I have chosen the name TestData.dsn on my Desktop. Click Save to exit.

Click Next to proceed

media_1444664502260.png

Click Finish

media_1444664535439.png

Of course we’re not finished yet – this is Microsoft

media_1444664756396.png

We still have to actually pick the Excel file to connect to… so have patience, click Select Workbook and in the file selector dialog find your Excel file.

Test the connection

media_1444664871273.png

Exit dialogs with OK until you get to here. Now we can use the Test Connection button to make sure our ODBC connection actually works. If you get ‘connection succeded’ then you can click OK and you are good to go. Back to Helm finally…

[ Note: this was by no means an exhaustive guide to ODBC, just a quick set of steps to get something up and running. ODBC is a complex and powerful system that can do many things. But to make the most of it you will have to read up on it. Microsoft have quite a lot of information available on the internet. ]

Now you can run the panel and see Excel data in your Helm Table

media_1444665294015.png

Datasets are just one of the ways to use Tables. In further articles we’ll show how Tables can be used to display server timelines and how you can create your own interfaces using JSON. Stay tuned…