Query Historian from Excel

#1

I added this as an feature request over on the Ideas site (here, if you’re interested in this too), but I was curious how others worked around this.

Most reports I’ve done can be made to work in the built in reporting module (which is very nice), but every so often I have to create a report that goes beyond what the reporting module could easily handle (or at the very least what I could personally do with it)

In the other packages I’ve used (iFix, FactoryTalk, and Wonderware) they’ve included a plugin for Excel to query their Historian and get values. Ignition doesn’t offer this so querying data that spans more than one automatically generated historical table can be problematic, especially as the date range increases, or the time between auto generated tables decreases.

Has anyone else used excel to handle advanced reporting? If so how did you accomplish handling reading data beyond a single table? Yes full unions would do this, but how did you handle telling it, programmatically, what tables to union?

0 Likes

#2

Obviously this isn’t as easy as a built-in-to-Excel plugin, but I can envision a system where an Excel macro/button triggers an Ignition script that queries the tag historian then dumps the data into a flat .csv (which is then grabbed and fed into Excel). Exactly how to trigger the Ignition script would be the ‘tricky’ part, but simply checking whether a ‘trigger’ file exists at a certain rate would probably suffice.

0 Likes

#3

That is a more automated option than I had thought of so far!

Is the system.tag.queryTagHistory script open source or viewable in any way?

0 Likes

#4

You could use an Excel plugin to query MySQL and therefore query the historian. See here: mysql.com/why-mysql/windows/excel/

Another thing you could do is write a Python script in Ignition that gets all the data you need and generates the excel file that you want. You can use the PA Office Documents Module to create/read/write excel files in Ignition.

Best,

0 Likes

#5

[quote=“nmudge”]You could use an Excel plugin to query MySQL and therefore query the historian. See here: mysql.com/why-mysql/windows/excel/
[/quote]

So the only problem with this is that when you auto generate tables based on time (i.e. every month), then to query data that spans across two months you have to do a union on the two tables and then query the data from that union. As the time span increases or the date for auto generation decreases (say every day for some reason) then the code required to automatically query all those table becomes increasing difficult.

The exporting to excel is certainly a more feasible option though (in my opinion at any rate)

0 Likes

#6

If this is a really big deal, consider switching to a database backend that can take care of partitioning for you, like Oracle or SQL Server. Then leave partitioning disabled in the Ignition Historian configuration. When the backend handles partitioning, clients, including both Ignition and Excel, will just ‘see’ one big table.

0 Likes

#7

That is an excellent idea, especially since most the time MS SQL is specified in the specs anyways. I’d still be very interested in some sort of plugin, but this is definitely a usable solution.

0 Likes

#8

Any news about that since this tread is 2 years old?

I’m also comparing many historical solutions for a project and this is a negative point for Ignition.

0 Likes

#9

Nothing has changed. Phil’s suggestion problem remains the best idea: Query Historian from Excel

0 Likes