Summary

• 
  This article describes how to use the new importer function in the Data Inserter Tool. This tool, built primarily for testing purposes, can be used by Application Engineers to validate ION® EEM during the commissioning process and also to prepare baseline time series to plot Trend Analyses against.
  
  

  Keywords

  DataInserter.exe

How To Import Data Using The Data Inserter Tool
1. Identify the Source and Measurement you want to import.
2. Prepare a CSV (comma separated values) file containing the time series data to be imported. See the sample format below.
3. Ensure that data for this source measurement pair does not already exist for the time period specified in the CSV file.
4. Run the Data Inserter tool from D:\IONEEM\bin\DataInserter.exe. The Data Inserter window appears as illustrated in Figure 1.
5. In the Data Inserter, select the Source and Measurement whose data you will be importing.
6. Select the Import tab and define the parameters:
a. The CSV source file name and path. Click the […] button to browse to the file or type in the file name and path with the proper extension.
b. If the date time stamps in the CSV file correspond to the Meters local time, check the Timestamps in Local Time option.
c. If column headers exist in the CSV file, check the File has header row option.
d. Select the appropriate data separator.
e. Identify the column containing the timestamp information.
f. Identify the column containing data values.

7. If importing data for an existing source measurement pair, skip to Step 9.

8. If importing data for a new source measurement pair (SMP), i.e., the SMP is not yet defined in the database:
a. Note the Source and Measurement names.
b. In SQL Enterprise Manager, connect to the database and run the following queries to determine the corresponding Source and Measurement IDs:

• • • • -- What is the MeasurementID?

        SELECT ID, StandardName

        FROM MM_MeasurementMap

        ORDER BY StandardName

        -- What is the SourceID?

        SELECT SourceID, Name

        FROM SRC_Source

        ORDER BY Name

c. With the Measurement ID, ensure that the IsPrimary flag is set to 1. Query the value using the following query, where n is the Measurement ID:

• • • • SELECT *

        FROM RPT_Measurement

        WHERE MeasurementID = n

d. Update the IsPrimary flag if not already set to 1.

• • • • UPDATE RPT_Measurement

        SET IsPrimary = 1

        WHERE MeasurementID = n

9. In the Data Inserter, click the InsertData button.
10. Validate that data was inserted. In IONEEM, create a Trend Analysis that queries this source measurement pair for the data period defined in the CSV file. Does the report show the expected data?
11. Done.

The CSV File

The data file must follow the formatting rules:

· Only one source-measurement pair can be inserted at one time.
· The CSV file may contain data for more than one measurement but only for one source.
· The required date/time format is MM/DD/YYYY hh:mm:sswhere time is expressed in military time (24-hour clock).
· Column headers are optional but highly recommended.

Sample CSV file

Date/Time, Max Temp (degC), Min Temp (degC), Mean Temp (degC)

01/01/2004 00:00:00,4.9,0.8,2.9

01/02/2004 00:00:00,4.2,-2.2,1

01/03/2004 00:00:00,0.5,-5.6,-2.6

01/04/2004 00:00:00,-2.8,-6.5,-4.7

01/05/2004 00:00:00,-1.3,-5,-3.2

01/06/2004 00:00:00,-1,-3.9,-2.5

01/07/2004 00:00:00,6.3,-1.2,2.6

01/08/2004 00:00:00,6.5,1.2,3

..

Data Inserter User Interface

More Information

Refer to the following KB articles for more information about the Data Inserter tool.

How to Create a Time Series of Constant Values

ION® EEM 3.7 Service Pack 3 Release Notes

Notes

Note #1: If the user get the error "Insert Failed: No insert access to Source/Measurement pair.", then go to the Admin Tool under Data Import/Source Measurement Pairs tab in expert mode. Right-click on the source or the source measurement pair and select "Edit Data Provider Access Control". Enable data access for "TestData Inserter".

Note #2: If the user gets a script error mentioning "invalid string input" or "string input", look at the input file. This error can be cause by incorrect formatting or illegal characters. (i.e. a "*" in the file for an out of comms value)
The data may have partially imported and stopped at the offending line, so check the data in the Data Editor in the Admin tool.