Get data from Excel file
From SpinetiX Support Wiki
- 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
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.
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.
Save a workbook to CSV or text format
- 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:
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:
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.
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