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
Here we have an Excel table of our staff and their refreshment preferences.
Helm panel at runtime
And here is the same data as a Table in Helm. Now let’s see how we achieved that.
Panel setup in Helm Designer
This panel has only two items:
- a Table control
- an SQL Dataset device
The Dataset does the work of extracting data from the Spreadsheet, and the Table displays it.
Setting up the Table
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
The Dataset device needs two properties set to establish a connection and feed data to the target control:
- Query: this is a standard SQL query. In this example we’ve chosen to take all the data from the TestData Excel table.
- 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
Depending on the data source you select the OLE DB Provider. In this case we’re using ODBC drivers.
ODBC setting the connection
For an Excel file the easiest way to do this is to use the Build button.
Building a connection string
Click the New… button to create a new ODBC data source
Select the Excel driver
Select the Excel (.xls) driver and click Next.
Save the data source file
In this case I have chosen the name TestData.dsn on my Desktop. Click Save to exit.
Click Next to proceed
Click Finish
Of course we’re not finished yet – this is Microsoft
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
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
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…