I was wondering about the best approach to pulling data in from csv files with Ignition…
I have multiple different CSV files that need to end up in SQL Server Data Tables. Some files are just simple Data tables and can be accessed with OleDB csv driver and pulled in as data. Some of the others have to be interpreted as they have header sections and a strange format to how the data is stored.
I would like to contain all of this in Ignition but I think this might be a bi beyond it’s functionality…?
How would I do it with Ignition using the SQL Bridge Module?
Other ways that this can be done:
Write a custom app to do this
Use Sql Server Integration services
Use Sql .Net Stored Proc
Write a Ignition module for CSV file data source using the API. (This is possible isn’t it?)
Does Ignition have server side scripting that can be called to handle reading in csv files?
So in your oppinion what would be the best design and most robust approach. I would like to contain all my data collection code in the same product and not have it scattered around all over the place fo support reasons.
I hope to hear some other intereresting approaches for this!
Joe, I do a ton of stuff like this in my apps (parsing files and writing to SQL tables, ripping files and creating new ones, zipping and extracting files, you name it), and I just use python scripting within the standard components.
To read a csv file, it would just be a matter of opening the file, reading it line by line, and builiding a query string based on rules. I think there is a built in csv module in ignition, but writing the code from scratch is so easy and gives me the ultimate flexibility.
Sure, I put everything right in the database, assuming I know what you mean by that.
Here’s a sample script using pure python (you can use the java libraries as well). Just create a text file in a folder (you can figure out what has to be renamed), add this to a button press event, and look at the output. If you uncomment out the sql line, it would put the line into a database.
Of course, I assume you will be manipulating the file significantly (look in the python docs on split, join, and find), but this first step is just to see if you can read the file. It will all fall into place.
Oh, and the ‘r’ will read the file as text, but you can use ‘rb’ if the file is a binary file with a bunch of junk in it.
adir = 'C:\\Import\\'
rFile = open(adir + '/' + 'Test_File.txt', 'r').readlines()
for line in rFile:
print line
#system.db.runUpdateQuery("INSERT INTO TextTable (Line) Values (%s)"%(line))
What I meant with “Inserting directly into the database” is, do you open a database connection from your script and issue Insert SQL commands or call Stored procedures to insert the data read from the csv files into the Database from inside your script?
How are the error handling capabilities of these Python scripts? Can I handle database insert and connection problems elegantly from this Python scripting interface? How would I notify somebody about issues trapped in these server side scripts?
I’m new at this and I had a quick look at the Gateway scripts. I’m surprised that you cant create groups of scripts or even give tag change scripts names to simplify maintenance on a large project. Or am I missing something…
You can use standard Python exception handling techniques to deal with issues when running queries. Ignition maintains a pool of connections to your database, so you don’t need to explicitly open or close connections.
Gateway scripts are admittedly simple from an organization standpoint. They are a relatively recent addition to the system. They weren’t intended for use to create a massive, custom, server-side application. I suspect they will mature over time to accommodate such an idea.
[quote=“BigJoe”]Thanks Step7
I’m new at this and I had a quick look at the Gateway scripts. I’m surprised that you cant create groups of scripts or even give tag change scripts names to simplify maintenance on a large project. Or am I missing something…
[/quote]
What I’ve done in lieu of gateway scripts (for the purpose of ease of development, not because there is something wrong with gateway scripts) is just write an app that runs on the gateway PC. That’s another alternative, and you can always port the code over when it’s solid.
Not sure what you mean by “tag change scripts” or groups of scripts. Have you seen the “Script Modules” area in the designer? It sounds like you have just started poking around the software (and that’s fine), but I know several of us have done large projects and you can definitely write maintainable code.
Take a stab at it, and there are plenty of people here to help out.
I would like to create Folders inside the Gateway script and give these folders nice names. Like “CSV File importing scripts”, “SAP Interface Scripts” etc. I can then create scripts under each of these folders and once gain give them nice names like “Substane Gauge CSV Import script”, “SAP ORDER Import script”. These folders could then group similar scripts together to simplify management. The way the current Gateway Scripts are you can give names to the Timer scripts but you cant give names to your Tag Change scripts. Here you have to hope that the tag name of your trigger is descriptive enough so that someone can identify the script later when searching for specific scripts.
I want something similar to how Transaction groups can be organised.
Carl can you please answer my other questions?
Do I get the Gateway script functionality if I only purchase SQL Bridge Standard Module?
User Notification of script errors? How would a server side script be able to notify a user somewhere that an error has occurred. Would the following approaches be possible or do you have better ways of doing this?
a. Set a tag and write a message to a string tag that can alarm and be indicated on a client
b. Send an email via scripting with the error
c. Writeto a Ignition diagnostics log (AlthoughI have not seen an interface to anything like that?)
[quote=“BigJoe”]Carl can you please answer my other questions?
Do I get the Gateway script functionality if I only purchase SQL Bridge Standard Module?
User Notification of script errors? How would a server side script be able to notify a user somewhere that an error has occurred. Would the following approaches be possible or do you have better ways of doing this?
a. Set a tag and write a message to a string tag that can alarm and be indicated on a client
b. Send an email via scripting with the error
c. Writeto a Ignition diagnostics log (AlthoughI have not seen an interface to anything like that?)[/quote]
Yes. Actually, you don’t strictly need any modules for Gateway scripts to work. (this may change in the future - we reserve the right to require a module for these scripts to work)
Script errors are currently stored in the standard Ignition log. (stored in wrapper.log) a. and b. are possible by using exception handling.
We have purchased some SQL Bridge modules for various sites and are now realizing that we need to use some scripting after the group performs a write (write to generic internal/db tags with the group, then use scripting to choose which OPC tags to write to based on a lookup). This lookup resides in our Oracle database so we use system.db.runPrepQuery() on the gateway script. Will this be feasible on a Gateway with a SQL Bridge module and perhaps a Vision Panel module? There would never be any clients, just a project that can run the gateway scripts. I think my main concern is if the scripts will be able to hit the database given those licenses.
