19.1EXCEL IN & EXCEL OUT

The database export and import to EXCEL (EXCEL IN and EXCEL OUT) allow users to modify Tags and Blocks in EXCEL using copy, paste, edit and other EXCEL tools.

The EXCEL OUT function is used to transfer Tag and Block configuration information to an EXCEL Spreadsheet.  EXCEL OUT exports all the Tags and Blocks for one SCADA Node to an EXCEL workbook. After exporting the SCADA Node's Tag database, you can modify the Tags and Blocks by editing the entries or creating additional records (rows).

EXCEL IN imports the modified Tag database back into any SCADA Node in any WebAccess Project.

The most common use of EXCEL is to copy and paste recurring values to a group of tags (for example, copy DEGF as the EUNIT for all Temperature Tags).

The recommended method of using an EXCEL spreadsheet to edit tags is to first configure tags (and or Blocks) using the standard WebAccess ASP pages.  These will act as "templates" that you can export to EXCEL (EXCEL OUT).

Users can add new Tags and Blocks by copying existing tags or blocks, inserting the copied rows, then editing the copy to make unique tag and block names.  The address must also be changed to match the actual address in the field device.

Note also that each EXCEL worksheet has a limit of 65,536 rows (i.e. tags). If your database is large, and any one tag-type exceeds 65,000 (e.g. analog tags exceed 65,000) on one SCADA Node, you might consider working on the Access database directly.

EXCEL IN and EXCEL OUT export and import to the PROJECT NODE.  You can initiate EXCEL import/output from any client, but the EXCEL spreadsheet will be created on the Project Node.  If you are working from a client, you can download the EXCEL workbook (spreadsheet) using your web browser. To download the EXCEL spreadsheet (workbook) to a client, open a Web Browser and entering the IP address of the Project Node with the broadweb/excel path and the name of the Excel workbook you created.  For example, to download from the Live Demo the bwCfgExport.XLS, type:

http://64.55.156.4/broadweb/excel/bwCfgExport.XLS

If you are working from a Client, and would like to copy the modified EXCEL workbook back to the Project Node, you will need access to a shared folder on the project node or FTP should be enabled on the project node with privileges to write to the ftp server. Then, you could use EXCEL IN, browse to the shared folder or FTP server's folder and import the EXCEL spreadsheet using EXCEL IN.  EXCEL 97 and EXCEL 2000 are supported by the EXCEL import functions.

EXCEL IN requires the Project Name and SCADA Node name match the target project and node. You must rename the Project and SCADA Node columns in all the sheets (BwAnalog, BwDiscrete, BwAlarmAnalog, etc.) if you are copying a node to a new node name or project. 

When importing tags to a SCADA Node, the Comport and Device specified must already exist in the target  Node Configuration, EXCEL IN imports only Tags and Blocks, it does not create SCADA Nodes, comports or devices.

CAUTION: BACKUP your PROJECT BEFORE using EXCEL IN! Importing a database to a WebAccess project will overwrite the configuration data in the original project's SCADA Node. Before you import data to an existing database, be sure you backup your database so that you have the option to restore the database to its original form if required.

 

When you export, you can define any name and path for your EXCEL Workbook (i.e. spreadsheet).  WebAccess will create a new Workbook with spread sheets named: BwAnalog, BwDiscrete, BwText, BwCalcAnalog, BwCalcDiscrete, BwAcc. BwAlarmAnalog and BwAlarmDiscrete.   The utility also creates three blank spreadsheets, sheet1, sheet2, and sheet3 that you can use for any purpose.

BwAnalog - contains all Analog type IO Tags, Analog Block Tags (Parameters) and Constant Tags. Analog tags have real numbers as their value (e.g. 100.1,  0.0, 1955).  These are distinguished by the "Tag Type" column:

     Tag Type     Description

3                  Constant Tag - analog type
6                  IO Tag - analog type
b                  Block Tag Parameter - analog type

Constant Tags usually have a blank address fields.

BwDiscrete - contains all Discrete type IO Tags, Discrete Block Tags (Parameters) and Discrete Constant Tags. Discrete tags have integers as their value (0 to 7).  Digital tags (ON / OFF, True/False, 0/1) are considered discrete.  These are distinguished by the "Tag Type" column:

     Tag Type     Description

4                  Constant Tag - discrete type
7                  IO Tag - discrete type
b                  Block Tag Parameter  - discrete type

Constant Tags usually have a blank address fields.

 

BwText - contains all Text type IO Tags, Text Block Tags (Parameters) and Text Constant Tags. Text tags have text strings as their value (e.g. ASCII strings).       Tag Type     Description

5                  Constant Tag - text type
8                  IO Tag - text type
b                  Block Tag Parameter  - text type

Constant Tags usually have a blank address fields.

 

BwCalcAnalog - contains all Analog-type Calculation Tags. Analog tags have real numbers as their output value.

BwCalcDiscrete - contains all Discrete-type Calculation Tags. Discrete tags have integers as their output value (0 to 7).  Digital tags (ON / OFF, True/False, 0/1) are considered discrete. 

BwAcc - contains all Accumulation Tags.

BwAlarmAnalog - contains all Alarms for Analog-type tags. The corresponding tag must exist in one of the BwAnalog, BwCalcAnalog or BwAcc worksheets.

BwAlarmDiscrete  - contains all Alarms for Discrete-type Tags. The corresponding tag must exist in either the BwDiscrete or BwCalcDiscrete worksheets.

If you use the default path and filename for EXCEL OUT, WebAccess copies the previous EXCEL spreadsheet that was exported to the backup folder on the Project Node.

To simplify the process of configuring a WebAccess database, you can use EXCEL IN to export the Tag database to EXCEL, edit the exported file and import it back to any WebAccess project.

Importing data can only be done if there are valid worksheet names with valid column names. Typically, data is first exported from WebAccess to an external spreadsheet to create the appropriate worksheets and columns. The data can then be imported back into WebAccess.