Friday, August 12, 2016

Import Historical Data from CSV to Ignition's Tag Historian

Ignition's Tag Historian™ makes it easier than ever to store and use historical data. Simply by selecting a check box on a tag, historical data is stored in an efficient format in any SQL database (MySQL, Microsoft SQL Server, Oracle, PostgreSQL, MariaDB, etc). This data is then available for querying through historical bindings, reporting, and scripting.

Are you migrating from a legacy historian package to Ignition? Do you want to import your old historical data into Ignition's Tag Historian (SQL database)?

This is a common question for customers migrating to Ignition. Ignition stores all historical data in a open format in the SQL database. Ignition doesn't log data to a proprietary format. This makes it extremely easy to migrate data from one system to Ignition. The only catch is that you have to be able to export data from your legacy system to a readable format, such as CSV.

In this post, I am going to show you how to take historical data from a CSV file and import it into Ignition's Tag Historian SQL format.

To get started, here is the basic flow to convert data from a legacy system to Ignition:
  1. Export your historical data from the legacy historian into a CSV file. The CSV file must contain the timestamp and the data.
  2. Map tags from the legacy system to tags in Ignition (by their tag paths).
  3. Specify the Ignition Historical Tag Provider (database) to import into.
  4. Specify the Ignition Realtime Tag Provider that the tags exist in.
  5. Specify the date format of the timestamp column in the CSV (to parse the date correctly).
  6. Call the system.tag.storeTagHistory function in Ignition to store the data.
Let's assume you have already exported your historical data to a CSV file. Here is what my CSV file looks like:

t_stamp Amps Temperature HOA
2016-08-12 08:00:00 4 22 0
2016-08-12 08:01:00 18 88 0
...

I want to import the Amps, Temperature, and HOA tags into Ignition. I have created a utility (an Ignition Project) that is built to perform the steps above. You can download this utility here:

http://files.inductiveautomation.com/utilities/SE/CSVtoTagHistoryConverter.proj

Once downloaded, you can import the .proj file in your Ignition Designer. You need to have Ignition 7.8.3+ in order to import this utility. Once you have the designer open simply click on File > Import... from the menu.


Select the downloaded .proj file and click Open. You only need the "Converter" window so just select that one and click Import.


Open the Converter window in the Ignition Designer or Ignition Client. If you are in the Ignition Designer you can go into preview mode to interact with the window.

The first step is to open your CSV file. Click on the "Browse" button and locate your CSV file. Once located click on the "Parse" button. Now we are going to start the import process.


The utility will try to automatically detect the timestamp column. Now we just need to follow these steps:

Step 1: Map Columns

If the utility doesn't parse your timestamp column automatically, you need to click on the "Select Mapping" column to the right of your timestamp column and select "Timestamp". For me, you will notice that the "t_stamp" column is already mapped to "Timestamp".


If you happen to have a column that represents the quality of the historical values (OPC quality where 192 = Good), than map that column to "Quality". If you don't have a quality column don't worry, it is optional.

Now we need to map each tag we want to import to the Ignition tag using the fully qualified tag path. A fully qualified tag path looks like this:

[TagProviderName]Path/To/My/Tag

Ignition comes with a tag provider called "default" and that is typically where these tags exist. I have three tags in Ignition that I want to map to. They are all inside of a folder called "Process". The tags paths look like this:

[default]Process/Amps
[default]Process/Temperature
[default]Process/HOA

Hint: You can right click on your tag in the Ignition Designer and click "Copy Tag Path" to get this path.

For each tag you want to import history on, simply set the mapping to "Tag" and enter in the fully qualified tag path. You will need to double click in the "Mapping Value" column to enter in the path. You also need to specify the data type of the tag (that way Ignition knows the correct data type since we can't rely on the CSV file).


Step 2: Select or Enter In Your Historical Tag Provider

The Historical Tag Provider is the system you want to log the history to. Typically this is the name of the database connection you have created in Ignition. The dropdown list on this screen will list all of your database connections. However, if you are using the Tag History Splitter, you will need to enter in the name of that provider into the text box.

My database connection is called "DB".


Step 3: Enter In Your Tag Provider

The Tag Provider is the name of the realtime tag database your mapped tag is stored in. You can find this out by looking under the "All Providers" folder in the tag browser:


You will notice that my tags are in the "default" tag provider. Type that name into the text box.


Step 4: Enter In Your Timestamp String Format

When we parse the CSV file everything comes in as strings, including the date. The system.tag.storeTagHistory function requries an actual date object. So, the utility has to parse the string timestamp into an actual date object. In order to do that it needs to know the date format. The date formats can be wildly different. Some files may leave out the seconds, some can be in 24 hour format, etc. The typical date format is:

yyyy-MM-dd HH:mm:ss

See this page for more details on the timestamp format. Here are a couple of examples:

2016-08-11 14:20:54 = yyyy-MM-dd HH:mm:ss
2016-08-11 02:20:54 PM = yyyy-MM-dd hh:mm:ss a
8/12/2016 8:00 AM = M/d/yyyy h:mm a

My date format is yyyy-MM-dd HH:mm:ss:


Step 5: Import 

The last step is to press the Import button. The utility parses the CSV and creates the necessary arrays to pass into the system.tag.storeTagHistory function. For more information on that function go here:

https://docs.inductiveautomation.com:8443/display/DOC/system.tag.storeTagHistory

That's it. You should now be able to view that history in an Easy Chart.


If you have any questions please let me know.