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.

Friday, July 29, 2016

Java and High DPI Displays

Are you running into issues with your Ignition Designer (or runtime) being extremely small on a high DPI display?

Your not alone. A lot of our customers are running into this issue. Ignition's Designer and runtime are Java Swing applications. The problem is that Java Swing claims that they are DPI aware, but they're not, so Windows doesn't scale it.

Unfortunately, this is not something that Oracle will fix, however, there is a way around it by modifying the EXE manifests for java.exe and javaw.exe and javaws.exe. 

This fix requires a resource editor and is for Java 8 or higher. I used Resource Hacker to modify the EXE manifests.

Follow these instructions:
  1. Install Resource Hacker
  2. Run Resource Hacker with administrative privileges
  3. Open java.exe from the installed Java 8 JRE (or JDK) path. Example:

    C:\Program Files\Java\jre1.8.0_51\bin\java.exe

    Note: If you have multiple versions of Java 8 installed you will have follow these steps for each one.
  4. Expand folder 24
  5. Expand folder 1
  6. Click on node 0 and you will see XML on the right
  7. Locate the dpiAware setting under application:

    <dpiAware>true</dpiAware>

    and change true to false:

    <dpiAware>false</dpiAware>



  8. Press the Compile Script button at the top
  9. Save your changes through File > Save
  10. Follow the same instructions for javaw.exe and javaws.exe. Make sure to do this for all Java versions (JRE and JDK) you have.
  11. Make sure to use the Native Client Launcher for Ignition. 
That's it. Now you can set your scaling to 150% or 200% in Windows and the Ignition Designer will look much better. You may have to restart your computer for the settings to take affect. 

Note: If you are using the 64-bit JRE make sure you are applying these changes to java.exe in Program Files not Program Files (x86) and vice-versa. 

Also, if you update your JRE to a newer version you must follow these steps for the new java.exe and javaw.exe.

Thursday, July 21, 2016

Template Layout and Resizing

First of all, I want to say sorry for neglecting the blog for a couple of years. Starting this week, I want to post a new blog entry once a week. I have realized that there are a lot of little things that I know that can really make a difference when developing in Ignition. Stay tuned and follow me for new and exciting tips and tricks on Ignition.

This week I want to touch on template's layout and resizing. I find that a lot of people get tripped up with how the layout works and what happens when you resize the template. For this post, I want to explain exactly how it works.

By default, when you drag a template onto the window from the Project Browser, the size of the instance is exactly the same size as the master template (template definition). You can make the size of the template instance on the window larger or smaller but it only changes the size of that instance. Usually people want to make the template instance larger that what was defined.

However, if you modify the size of the master template it will NOT automatically resize the template instance on the window. For that, you must locate the template instance (everywhere it is being used), right click and select "Revert to Master Size."


That will change the size of the template instance on the window to the size of the master template.

Putting a template on the window is the same idea as putting a Vision component on the window. It is very important to note that you are now dealing with 2 different layouts: the layout of the template on the window AND the layout of the components inside of the template. By default, the layout of the template instance on the window will be "Relative - Maintain Aspect Ratio" and the layout of the components inside of the template is "Relative - Stretch."

The layout of the template instance dictates the layout of the components inside of the template. In other words, if the template instance does not resize than the components inside of the template will not resize as well. Also, the template instance can have a different layout than the components inside of the template. For example, you may want the template instance to fill out the entire window using anchored layout but you want the components inside of the template to maintain their aspect ratio.

So here comes the issue that everybody runs into: you set the layout of the components inside of the template to anchored or to maintain the aspect ratio but when you resize the template on the window it ignores that layout and stretches the components.

That happens because a template acts the same way as a "Component Group" by default. That means the layout is stuck to "Relative - Stretch." If you want the template instances to respect your layout settings, you need to set the "Enable Layout" property to true in the template definition.


That little checkbox will allow you to control the layout inside of the template. With that you can now control both layouts: on the window and in the template.

If you have any questions please feel free to email me at travis@inductiveautomation.com. Please stay tuned for more tips and tricks.