I have 1000s of tags from UDTs that have some information coming from PLC and some information that is fairly static, but I want to be able to change the value based on data from an excel sheet.
Ignition servers are dark with no connection to internet or any other site (safety critical system)
To be clear about process we have another Off site database that has all the data along with QA checks etc. When a change is made this database uses an excel macro file to hold the data. This excel sheet is then brought to the ignition network and we then need to load any change data into ignition.
My question are
-
Can you push data from excel into a memory tag without having Ignition grab that data itself (what we don't want is a change being made to the excel file and ignition grabbing data from that change without us telling it to from excel -- also don't want something like a change to the excel file -name etc- to break the connection) . I don't know of a way to do this in this fashion,
-
I believe the best way would be to have DB tables setup on the ignition server and have the Excel macro push data into the SQL DB server. My question on this route is if we have 1000s of tags linked to the SQL DB for tag values with 99% of the time those values not changing what kind of performance hit are we taking for this function. Maybe I just answered my own question -- set these tags Execution mode to event driven and have a SCADA button to rescan these DB values so even if the read is a huge SQL load it only happens when we tell it to.
That brings to mind if anyone has done this or could give some ideas on if we do option 2. Event driven change could we then for QA check display a list of all values that actually changed?
Not looking for full detail resolution / solve for this but more for general ideas and thoughts.
If you have other ideas that I am not even considering here please share.
Modern Ignition can script completely custom reading and writing of Excel files using the Apache POI libraries. You don't say whether you are using Perspective or Vision, but that just impacts how you get the file into your algorithm. After that you have total control over what you read from the spreadsheet and where you put those values.
Related prior topics:
https://forum.inductiveautomation.com/search?q=Apache%20POI%20Excel%20order%3Alatest
mainly for QA reasons we want to PUSH the data from excel into ignition and not have ignition read from the excel file. The excel file will only exist on the ignition server for the time to make the change and then be deleted. A new file would be brought at later date to make other changes then deleted. If excel file is somehow left and someone makes a change to it we don't want that data getting into ignition. Only when certain conditions are met do we want excel to push that data into ignition.
The other day I came across this "CSV Tag Write Tool" on the Ignition Exchange:
Not sure if is exactly what you are looking for but perhaps it will be helpful for updating the Ignition from a CSV. Note, I think this would entail Ignition getting the data from the file (not the file writing to Ignition).
1 Like
Ah yes, safety critical Excel sheets 
I would consider your option 2 reasonable. Thousands of rows (as long as it's truly thousands, and not hundreds of thousands) is child's play for any decently specced DB. And putting the data into a DB abstracts Ignition and whatever process generates these Excel sheets away from each other, which is a good thing if you ever decide to change either end. Cough, cough.
4 Likes
I think you completely missed what I was suggesting. I am not suggesting placing an Excel file in a static place in the Ignition gateway to be read. Let's revisit and answer your OP questions:
-
No, there's no way for Excel to write directly to Ignition tags. You could make a Webdev API to support external writes to tags. Brace yourself for a bunch of code in both Ignition and Excel if you go that route.
-
Yes, you can make a DB connection from Excel to push updates, and have Ignition monitor the DB to write to tags. Brace your self for a bunch of code or configuration in Ignition and code in Excel.
My suggestion would work like this:
*) In either Perspective with a file upload component, or Vision with an Open File browser operation, initiated by a suitably authenticated operator, open a file from the client side.
*) In the associated script, directly access the file content with the Apache POI libraries. In Perspective, this means not placing the file bytes in a gateway local file. In Vision, the file would be client-local the whole time.
*) From either source, use the Apache POI API to access the sheets and cells of the spreadsheet, using some pre-defined layout, and write the retrieved values directly to the appropriate tags. Or perhaps to "staging" tags with a followup review-and-approve User Interface.
With this approach, updates to the tags are initiated by a human, from a client-side spreadsheet file. No code is required in the Excel file. The code in Ignition need not make any database calls, just move spreadsheet cell values to tags.
2 Likes
Side Note:
Not having any code in the Excel file makes it possible to avoid having an actual copy of Excel in a workstation within your isolated, production network. If you must open such a file on that network, LibreOffice will do a beautiful job, but won't run any Excel macros or VBA. Which is good. And you can avoid the engineering malpractice of installing any copy of Windows itself on any machine connected to that network.
2 Likes
Thanks for the ideas. I think we have moved to either
A) Have the Excel push the data into the PLC (we are already doing this and this function will not change) - Then Ignition reads those PLC tags (at a slow rate and or triggered by user undetermined at this time) Value change script on those tags then updates DB appropriately.
B) Excel push data directly into the DB using SQL in excel and tags in ignition read form the DB.
Which method we use will depend on memory load on PLC.