Importing Historical Data from CSV Files

Hyper Historian is a powerful, 64-bit historian that uses a high compression logging engine to decrease database size. The Hyper Historian logging database is optimized for space usage and does not require any third-party database to store historical data.

 

You can add new historical data from a third-party logger (or data that was collected manually) to a Hyper Historian database, however, by using the BridgeWorX Text File Parser transaction block. Data added to Hyper Historian in this way is available as native Hyper Historian data and OPC HDA data, and you can query it using the Hyper Historian OLE DB interface.

 

The figure below shows you how to use the BridgeWorX Text File Parser transaction block to move data from a CSV or text file into the Hyper Historian database.

 

System Architecture

 

Note: Another method for importing historical data from CSV files is ICONICS' MergeWorX.

MergeWorX, a tool for automatically or manually inserting data into Hyper Historian, uses a plug-in technology to import from various formats of CSV files. It can be used to import historical or log data from databases, other historians, intermittently-connected field devices and other equipment such as PLCs, providing high-resolution recording from these devices and greatly increased reliability of capturing all data, even when network outages occur.

 

In order to transfer data from CSV file into a Hyper Historian database, follow these general steps:

  1. Add the desired OPC tags into the Hyper Historian configuration. These are the tags for which you want to import values from a CSV file.
  2. Create a store procedure in SQL Server to add the values.
  3. Configure BridgeWorX to retrieve data from the CSV file and write the values into Hyper Historian using the stored procedure.

Configure Hyper Historian Logging

First, you will need to make sure that the tags you want to import data for are configured and available in Hyper Historian.

For Hyper Historian Configuration instructions, refer to the Introduction to Hyper Historian topic.

Configure SQL Server

Next, you will need to create a stored procedure in the SQL Server where the configuration database for Hyper Historian resides.

  1. Open SQL Server Management Studio by opening the Start menu/screen and entering SQL Server Management Studio into the search box.
  2. Create a new query by selecting the New Query button near the top of the management studio.
  3. Select the Hyper Historian configuration database in the available database drop-down list.

SQL Server Management Studio

  1. Copy the query below and click on the Execute button to create the new stored procedure.

CREATE PROCEDURE InsertCSVDataToHH

@TimeStamp nvarchar(50),

@Value float

AS

SELECT * FROM

OPENQUERY(HH2, 'INSERT INTO

Test.TestCollectorGroup.RawData

(TagName, [TIMESTAMP], VALUE)

VALUES (''TestTag'',''2010-06-15 10:00:00'',

''5'')')

 

Note: Make sure you change the Tag Name to the one actually used. If you are not sure of the tag’s name, you can run a query against the LoggingGroup.CollectorGroup.Tags table. In addition, the table name is made up of LoggingGroup.CollectorGroup.RawData. You can find your logging group and collector group name in your Hyper Historian Configuration.

Configure Transaction in BridgeWorX

Last, you will create a BridgeWorX transaction that will read the data from the CSV file and write it into the Hyper Historian logging database. If you would like to learn more about BridgeWorX, please refer to the application note titled "BridgeWorX – Quick Start" and the associated help files.

  1. In the BridgeWorX configuration, create a new Database Connector by right-clicking on Database Connectors and selecting New Data Connector.
  2. Name the data connector HH_SP and choose the data direction as ONLY WRITE.
  3. On the Database Connection tab, select the Database Login button to establish a link to the database.
  4. In the database connector browser screen, right-click on SQL Server Connections to insert a new connection.
  5. Select your Hyper Historian database in which the stored procedure was created. The figure below illustrates steps 2-5:

Database Connector

  1. Once you can successfully connect to the database, navigate to the Write to Database tab.
  2. Select the Stored Procedure Call from the options, then click the ellipsis button [...] next to the Stored Procedure Name text box to choose the stored procedure you created earlier.

Database Connector: Choosing Stored Procedure

 

Note: The name of that stored procedure was InsertCSVDataToHH if you copied it exactly as shown in the previous section.
  1. Click Apply to save the configuration for the data connector.
  2. Navigate to the Transaction Tools Transaction Diagram in the tree control and right-click on a transaction diagram folder to create a new diagram.
  3. Open the transaction diagram and click the Integration button in the Diagram Tasks list.
  4. Drag the Text File Parser task onto the diagram.
  5. Double-click on the newly-added task and give this task the name CSVImport.
  6. Go to the CSV/Text File Parser Properties tab and select the Parse Text File drop-down list.
  7. Click the More Properties button to select the file that contains the OPC data.
  8. You can click the Test button to check if the contents of the file can be retrieved by BridgeWorX. You can also click the Advanced button to select the data type of the columns.

Figure 5 - Choosing the CSV File to Parse

 

Note: The CSV file should have date and time in YYYY-MM-DD hh:mm:ss.000 format. If the data is in any other format, it needs to be converted into this format or the stored procedure needs to be updated to accommodate conversion to YYYY-MM-DD hh:mm:ss.000 format.
  1. Open the Return Values Mapping tab and enter HH_SP in the Select Database Connector textbox.
  2. In the grid, bind the data column (from the CSV file) containing the Timestamp to the @TimeStamp parameter. Also, bind the data column containing the tag values to @Value parameter.

Figure 6 - Data Connector Parameter Binding

  1. Save and close the transaction diagram.
  2. In the BridgWorX configurator, create a new Transaction by selecting the active configuration and then create a new transaction. Name the transaction "CSVImport" and link it to use the "CSVImport" transaction diagram created earlier.

Importing and Viewing Imported Data

Now that you have configured everything, you are ready to import the data. Importing the data is as easy as running the BridgeWorX transaction you created in the previous section.

 

To view the inserted data, you can run the SQL Query below:

SELECT *

FROM

OPENQUERY (HH2, 'select * From

Test.TestCollectorGroup.RawData Where TagName

= ''TestTag''')

ORDER BY [timestamp] DESC

 

Figure 7 - SQL Query to View Data in Hyper Historian Database

 

For more information on how to use the Hyper Historian SQL Query engine, please refer to the SQL Query Engine Quick Start topic.

 

See also:

Importing and Exporting Hyper Historian Configurations