Get data from Excel file

This page is related to Data feed & Spreadsheet widgets pages.

Introduction

Microsoft Excel files cannot be opened and read by the HMP, nevertheless, the data inside them can be displayed on the screen using the spreadsheet widgets from Elementi - here is how:

Elementi 2016 (or later) comes with the support for Excel file channel, which allows for the spreadsheet data to be retrieved from the Excel source file and included with the content at the time of publish on the HMP.
For Elementi 2015 and Elementi 3.x users, the alternative is to manually export the Excel files to other text-based formats (CSV, plain-text, XML), as detailed below, and use the resulting file as source of data.

Excel file channel support

Excel file channel

Using this channel requires that the Excel application is installed on the PC running Elementi 2016 or later.

Starting with Elementi 2016, an Excel file can be used as a data source within spreadsheet widgets. The actual data are retrieved and included with the content at the time of publish on the HMP. The different "Data Properties" dialog options used to import the data are detailed on the "Spreadsheet widgets" page.

Both newer and older Excel file formats are supported as data source. OpenOffice Calc files as well.

Notes:

To synchronize further changes within the Excel source file with the player, you need to re-publish the project on the HMP.
See the tutorial about how to display an Excel file.

Excel file export to other formats

For Elementi 2015 and Elementi 3.x users, the alternative is to manually export the Excel file (OpenOffice Calc files as well) to other text-based formats (CSV, plain-text, XML) and use the resulting file as source of data. After this step is completed, the data can be displayed on the screen using the data-driven widgets included in Elementi 2015 or Elementi 3.x.

Note:

Be aware of the special care that must be taken to preserve character encoding of your file for accentuated characters.

Save a workbook to CSV or text format

Saving csv file using UTF-8

Microsoft Excel can naively export data to CSV or plain-text format. Generally, you need to follow these steps:

Open the save dialog, select a location on your local hard drive to save the file, and enter the file name.
Select "CSV (Comma delimited) (*.csv)", respectively "Text (Tab delimited) (*.txt)", as document type.
Click on the Tools dropdown (next to the Save button) and select "Web Options..."
In "Encoding" select "Unicode (UTF-8)" in the "Save this document as" dropdown. This will insure that non-ASCII characters are kept and displayed as expected.
Press OK to close the dialog and save your file.

The instructions above might vary depending on the Excel version - for more detailed instructions, see how to save a workbook to CSV or text format from:

Note:

See also the "Display the content of a spreadsheet file" tutorial for an example on how to export and use data from Excel.

Save a workbook to XML format

Within Microsoft Excel 2003, 2007 or 2010, a table of "flat data" (also referred to as a "list" in Excel 2003) can be exported as a valid XML file using an add-in for Excel.

See how to create an XML data file and XML schema file from worksheet data from:

Live access

For "live" access to data, a separate script is needed to access the data inside the Excel file (using an ODBC connection for example) and to output the result in an acceptable format (csv, text, xml etc). This script will reside on a HTTP server to be accessible by the player.

UTF-8 encoding

In order to preserve special characters (like accents) when exporting the Excel file to CSV, the file must be encoded as UTF-8. There are several methods to do that (some might not work depending on the Excel version used):

One way to change Excel ANSI encoding to UTF-8 is the open the .csv file in Notepad then select File > Save As. Now at the bottom you will see encoding it set to ANSI change it to UTF-8 and save the file as new file

In the "Save as" window (Office button > Save as), there is a "Tools" button at the bottom. Click it and go to "Web Options". On the "Encoding" tab you can pick "Unicode (UTF-8)".

A simple workaround is to use Google Sheets - either paste (values only if you have complex formulas) or import the sheet, then download as CSV with UTF-8 formatting. See also this stackoverflow page and this practitest article.

See this Excel forum thread about CSV and Unicode (or UTF-8) problem proposing as solutions the use of an Excel addin or a 3rd party VB macro (see code below) to export to UTF-8.

VB code:

Sub SaveAsUTF8() 

    Dim fsT, tFileToOpen, tFileToSave As String 

    tFileToOpen = InputBox("Enter the name and location of the file to convert" & vbCrLf & "With full path and filename ie. C:\MyFolder\ConvertMe.Txt") 
    tFileToSave = InputBox("Enter the name and location of the file to save" & vbCrLf & "With full path and filename ie. C:\MyFolder\SavedAsUTF8.Txt") 

    tFileToOpenPath = tFileToOpen 
    tFileToSavePath = tFileToSave 

    Set fsT = CreateObject("ADODB.Stream"): 'Create Stream object
     fsT.Type = 2: 'Specify stream type – we want To save text/string data.
     fsT.Charset = "utf-8": 'Specify charset For the source text data.
 
    fsT.Open: 'Open the stream
     fsT.LoadFromFile tFileToOpenPath: 'And write the file to the object stream

    fsT.SaveToFile tFileToSavePath, 2: 'Save the data to the named path

End Sub