Importing Spreadsheet Data to a Citadel 5 Database

At present, the only way to copy data to Citadel with specific timestamps is through the SRVR Write Input Queue VI. Because this VI can be used only by a VI-based server, National Instruments provides the Write Spreadsheet to Database VI-Based Server specifically for the task of copying spreadsheet data to Citadel. This document explains how to use this server, where to learn more about VI-based servers, and how to set up an example that copies a spreadsheet to the database.

Introduction

The LabVIEW Datalogging and Supervisory Control (DSC) Module 7.x uses the Citadel 5 database to store data. However, sometimes the Citadel database is not available and the data needs to be stored in a spreadsheet and later placed in the database. One example is if you use the FieldPoint FP-20xx Real-Time modules as remote dataloggers. Because FP-20xx modules cannot log to the Citadel database, the data is logged to a spreadsheet. This document discusses how to copy the data from this spreadsheet into the Citadel database. For more information about logging data to a spreadsheet and transferring logged data from an FP-20xx module to a computer, refer to the Related Links section. You also can find these documents on the NI Developer Zone.

LabVIEW provides many VIs that both read and write to the database as well as perform trending and alarming. However, the only way to write data with a specific timestamp, other than the current time, is through a VI-based server. Because of this, a VI-based server was written specifically to copy data from a spreadsheet file to the Citadel database. The Setting Up the Write Spreadsheet section in this document contains directions to access the server. If you decide to use one of the following file formats, you do not need to edit or modify the server. You can use the server as a tool to copy your data to Citadel.

The server provides the following functionality:

• The server can copy timestamp information as well as quality information from .csv files to the Citadel database. The file can use any type of delimiter and the rows can be in any one of the following formats or similar formats:
  • Timestamp, data for first tag, data for second tag, data for the n tag, and so on.
  • Timestamp for first tag, timestamp for second tag, timestamp for the n tag, data for first tag, data for second tag, data for the n tag, and so on.
  • Timestamp, data for first tag, data for second tag, data for the n tag, quality for first tag, quality for second tag, quality for the n tag, and so on.
  • Timestamp for first tag, data for first tag, quality for first tag, timestamp for n tag, and so on.
• If an error occurs in the server when it imports a specific data point, the data point is ignored and noted in the Copy_Status tag.
• If the word Error is in the spreadsheet instead of a value, the quality is reported as bad for that data point in the database.
• You can use this server as part of a redundant system. For example, if you are logging data from an OPC server to Citadel and then log some of the data to a spreadsheet instead, you can use this server to copy tag data from the spreadsheet in to the database to the same tag name. All that is required is that the tags have the same name.

  Setting Up the Write Spreadsheet to Database Server
  

VI-based servers are similar to OPC servers in many ways. VI-based servers must first be registered before you can use them, they do not have a user interface, and they are primarily controlled through tag VIs and front panel DataSocket connections. Because this VI-based server is already written, there is no need to edit the server. To use the server, complete the following steps. For more information about VI-based servers and how to write them, refer to viserver.pdf, located in the manuals directory of your LabVIEW installation.
To install a VI-based server on your computer, register the server. Once the server is registered, it is automatically launched by the Tag Engine when its tags are accessed. If you no longer want the server installed on your computer, you must unregister the server.

This VI-based server consists of the following VIs:

• Register Spreadsheet to Database Server VI
• Unregister Spreadsheet to Database Server VI
• Write Spreadsheet to Database VI
  
  The server uses the following subVIs:
  • Combine Server Input Items with Item Configuration VI
  • Format String to Number VI
  • NaN to BadStatus Conversion VI
  • Read Output Tags from DB VI
  • Read Server Item Configuration VI
  • SDS Check INI Configuration VI
  • SDS Read Lines From File VI
  • Sort Server Items By Direction VI
  • Write String Tag to DB VI

The Register Spreadsheet to Database Server VI first registers the server in the common configuration database (.ccdb) file so that the Tag Engine can find and launch the server. Second, you use this VI to enter information regarding importing the file, such as the location of the timestamp, the tag names to copy the data to, and in which file columns the data is located.

Use the Unregister Spreadsheet to Database Server VI only if you want to completely remove the server from the computer. When you run this VI, it removes all of the .ccdb configuration for this server so that the server will not appear in the Tag Engine.

The Write Spreadsheet to Database VI is the actual VI-based server. You do not need to run this VI. The VI is automatically launched by the Tag Engine when the tags are accessed.


To set up the server, complete the following steps:

1. Download and unzip the write_spreadsheet.zip file.

2. Mass-compile the VIs in the version of LabVIEW you are using to make sure all VIs are saved in the current version of LabVIEW.

3. Run the Register Spreadsheet to Database Server VI. The VI should look similar to the following figure.



Note: If you plan to use the default server configuration of this example, leave the front panel controls in their default values.

4. In the Item Information field, enter the names of the tags into which you want to import the data, their data type, and their file column, starting with 0 as the left-most column.

5. Select the delimiter used in the file.

6. If the quality information is included in the file, switch the Include Quality to yes and enter the relative location of the quality in the Location of Quality Column. For example, if the quality is one column to the left of the tag, the location is -1. If the quality is two columns to right of the tag, the location is 2.

7. Enter the relative location of the timestamp in the Location of Time Stamp Column. For example, -1 represents one column to the left, 3 represent three columns to the right, and 0 denotes that you must always use the leftmost column as the timestamp. Note that the timestamps in the file must be stored as the number of seconds since 12:00 m, January 1, 1904.

8. Click the Register Server button to register the server. The information is saved to the Spreadsheet to Database.cfg file in the LabVIEW directory and the server is registered in the .ccdb file. When the Tag Engine launches the server, it reads the Spreadsheet to Database.cfg file to get the file format and other information.

9. If you need to clear the tag names and start over, click the Clear Tag Name(s) button.

10. Find the DSCEngine.ini file in the LabVIEW directory and change the following values:

NoShutdownLog=1
UseServerTimestamps=true

If the Tag Engine is already started, you must exit and reload the Tag Engine for this change to take effect. For more information about these settings, refer to the DSC Module Developer Manual or the KnowledgeBase listed in the following links section:

See Also:
KnowledgeBase 2Q67RGHK: Why Do I See a Lot of NaN (Not-a-Number) In My Citadel Database When I Use the Set Tag Attribute.vi?

Setting Up the Example

The attached .zip file contains an example showing how to read spreadsheet data in to Citadel. The following list represents the spreadsheet columns and where the data is placed by the example.

Column	Column Title	Data
Column 1	Timestamp	Timestamp for the data on that row
Column 2	Analog data	Row_String tag
Column 3	Analog data	Sine_Wave_1 tag
Column 4	Analog data	Sine_Wave_2 tag
Column 5	Analog data	Bit_Array tag
Column 6	Discrete data	Discrete_Number tag

Complete the following steps to set up the server and run the example.

1. Download and unzip the Write Spreadsheet to Database VI Server.zip file from the Setting Up the Write Spreadsheet to Database Server section.

2. Download and unzip the w rite_spreadsheet.zip file from the download section.

3. Copy the Spreadsheet to Database.cfg file to the LabVIEW directory, for example, C:\Program Files\National Instruments\LabVIEW 7.0. This file contains the server setup for the sample spreadsheet and will be read by the Register Spreadsheet to Database Server VI when the VI is started.

4. Mass-compile the VIs in the version of LabVIEW you are using to make sure all VIs are saved in the current version of LabVIEW.

5. Run the Register Spreadsheet to Database Server VI. The controls on the front panel should look the same as the ones in the following figure. If they are not, make sure the Spreadsheet to Database.cfg file is located in the LabVIEW directory.

Note the following settings used to import this spreadsheet.

• The tag names with their data type and location are listed in the Item Information section.
• The Delimiter is set to Tab, which is used by the .csv file.
• The Include Quality is set to No because this spreadsheet does not contain quality information about the data.
• The Location of Time Stamp Column is set to 0. This setting stipulates that the timestamp is located in the first column and that it should be used as the timestamp for all of the data in that row.

6. Click the Register Server button to register the server and close the VI. This action saves the new configuration file and registers the tags and the server in the .ccdb file.

7. The server is now set up and will start when any of the tags are accessed by the Tag Engine.

8. Load the Write Spreadsheet to Database.scf file into the Tag Configuration Editor so that the SCADA configuration file is used when the Tag Engine launches.

9. Run the Write Spreadsheet to Database VI and select the Off-Line Data 01.txt file. The VI should look as shown in the following figure.



10. Click the Log Spreadsheet to Database button. The server should start importing the data to the database. The Write Status indicator shows the present status of the server. The following figures show the example during the import and at the end of the import.



11. The data is imported to the database. As long as the file format does not change, you do not need to rerun the Register Spreadsheet to Database Server VI to import additional files. If you want to remove the server from the computer, run the Unregister Spreadsheet to Database Server VI and delete the VIs.

Controlling and Accessing the Server

Because the VI server does not have a user interface, it must be controlled by other means. To control this server, use the File_location and Copy_File_to_Citadel tags. The status of the server can then be read back using the Copy_Status tag. After the server is configured and set up using the Register Spreadsheet to Database Server VI, complete the following steps to control the server:

1. Open the Tag Configuration Editor and add the File_location, Copy_File_to_Citadel, and Copy_Status tags from the Write Spreadsheet to Database server to the Tag Engine.

2. In your program, enter the full pathname of the file to import to the File_location tag.

3. Toggle the Copy_File_to_Citadel tag from false to true. This tag tells the server to import the data from the file. You can then read the import status from the Copy_Status tag. This tag indicates which rows are being copied and when the copying is complete. The tag also indicates if any errors occurred.

4. To import another file of the same format, repeat step 1. To import a file of a different format, repeat step 1 of the previous section, Setting Up the Example.
Related Links:
Logging Data with a FieldPoint FP-20xx to a Spreadsheet File
Transferring Files from a FieldPoint FP-20xx through an FTP Server
LabVIEW Redundant Data Logging Example

Reader Comments | Submit a comment »