Reading an Excel Spreadsheet using the ClearSCADA ODBC Driver

[sbeadle1]

📖 Home  Back  

Description

In order to connect CleasrSCADA to an Excel Spreadsheet, please use the following steps as your guide:

• Create Spreadsheet with column headers and populate rows with appropriate text

• Create ODBC SYSTEM DSN Connection to the Excel File

• Create ClearSCADA objects in order to grab data

Instructions

Create Spreadsheet

In order to work with a data set, we need to create a spreadsheet which will act as our data source.

• Create a spreasheet with the appropriate column headers.  This example uses Commodity and Value as the column headers.

• Populate the spreadsheet with the data you require.

ODBC Connection

We need to make this spreadsheet accessible to ClearSCADA via ODBC. To accomplish this, we need to configure and ODBC SYSTEM DSN connection.

• Open the Control Panel | Administrative Tools | Data Sources (ODBC ) application

• Select SYSTEM DSN | Add 

• Select "Microsft Excel Driver" , select the newest version that supports the *.xlsx file if you are running Excel 2007. Other wise the standard *.xls connection is fine. 

odbc.PNG
 
Ms excel.PNG
 

• Name the Data Source Name to something meaningfull, and Browse to the Workbook Name.

 
  commodity.PNG
 

• Press OK when complete.

 

Create ClearSCADA Objects

Now that we have a Data Souce and Connection, we need to bring that data into ClearSCADA.  A number of objects must be created and linked for this to happen.

• Create the following objects in ClearSCADA:  ODBC Connection, ODBC Query, ODBC Analog Point.

CS Objects.PNG
 

• Configure the Connection and browse to the DSN you created.

 
DSN in CS.PNG
 

• Configure the Query object by selecting the Connection you created and enter a Query:

 
ODBC Query.PNG
 

• Configure the Analog Point by  selecting the Query and Row and Column for the result:

 
Analog Point.PNG
 

Notes

Once all items are In Service, the Excel spread sheet is no longer editable. The open connection to the file from the DSN locks the file from being opened by other applications. To update values in the Spreadsheet, you should connect via another ODBC connection and use the UPDATE or INSERT commands.

To ensure the data always results in the selected Column and Row, you can edit the query on the Point Tab to filter based upon a unique row so that there is only one result returned from the query such as:

 SELECT * FROM [Sheet1$] WHERE Commodity = 'Oil'

Go: Home Back