Sign in

Data Source - Microsoft Excel

Microsoft Excel

Microsoft Excel is a spreadsheet program developed by Microsoft. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.

To create a connection to the Microsoft Excel Data Source, right-click on the Data Sources group, click “Microsoft”, and then select “Excel Workbook”.

DataSources-Excel.png

To successfully retrieve data from a Microsoft Excel data source, the following properties need to be set.

DataSources-ExcelProperties.png

  • Name (String)
 Name to identify the data source.
  • Excel Workbook File (File location)
 Location of Excel file.
  • Query Type (Dropdown)

Retrieve the data either as a standard or wide result.

NB. WideQuery is deprecated - use Standard
   
Note: To query Excel files, you might be required to download and install the Access Database Engine package. Make sure to install the correct package for your OS.

To retrieve data from an Excel spreadsheet, you need to use SQL queries. For more information on using SQL queries to retrieve data from Excel, refer to Run SQL queries on Excel files

The query you write needs to take into account the structure of the Excel document. For example, if you have an Excel document with data similar to the below, and you want to retrieve values for Temperature 1:

mceclip0.png

then, your Query in Flow will be similar to the below:

  select Value, Quality
  from [Sheet1$]
  where
  [Tagname] = 'Temperature 1'
  and [DateTime] > [PeriodStart]
  and [DateTime] <= [PeriodEnd]

Or, if your Excel document has data in a "wide query" format, where each column contains data for a different tag, similar to the below:

mceclip2.png

then, your Query in Flow will be similar to the below (in this case, you are retrieving values for Temperature 4):

select 
    [Temperature 4]
from [Sheet1$]
where 
    [DateTime] > [PeriodStart] 
    and [DateTime] <= [PeriodEnd]