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”.
To successfully retrieve data from a Microsoft Excel data source, the following properties need to be set.
||Name to identify the data source.|
||Location of Excel file.|
Retrieve the data either as a standard or wide result.
NB. WideQuery is deprecated - use Standard
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:
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:
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]