Hi all,
We are working on importing data from a different system into our Enterprise Historian.
That particular system can generate CSV files for us in the following format:
DateTime,Value
I have code written and debugged, I am just wondering if there is a more efficient way of doing this.
The CSV files can have up to 150,000 lines. The code I am using parses and inserts it in under 5 seconds.
But it just seems like a clunky way of doing this.
Our Historians partition by month, so this is why I am going the scripting route instead of just inserting direct to the database.
import csv
path = system.file.openFile("csv")
reader = csv.reader(open(path, 'rb'))
def gen_chunks(reader, chunksize=1000):
chunk = []
for i, line in enumerate(reader):
if (i % chunksize == 0 and i > 0):
yield chunk
del chunk[:]
chunk.append(line)
yield chunk
prov = 'EntHistory'
selectedPath = event.source.parent.getComponent('tagPath').text.split(']')[1]
x = 0
fullDS = []
for chunk in gen_chunks(reader, chunksize=1000):
vals = []
paths = []
quals = []
ts = []
#Delete the first row of the CSV as it is just headers
if x < 1:
del chunk[0]
for row in chunk:
paths.append(selectedPath)
d = system.date.parse(row[0],"MM-dd-yyyy HH:mm:ss")
gd = system.date.format(d,"yyyy-MM-dd HH:mm:ss")
vals.append(float(row[1]))
quals.append(192)
ts.append(gd)
x=+1
system.tag.storeTagHistory(prov,'default',paths,vals,quals,ts)
#Put DS in a table on the screen just for debugging and confirmation of values
hdr = ['tag','val','qual','date']
fullDS = zip(paths,vals,quals,ts)
event.source.parent.getComponent('Power Table 3').data=system.dataset.toDataSet(hdr,fullDS)
The code looks pretty good to me. You might get some improvement by using a more efficient generator function to read the file, then split that generator into chunks:
import csv
import itertools
def readFile(filename):
with open(filename, "rb") as csvfile:
datareader = csv.reader(csvfile)
next(datareader) # skip the header row
for row in datareader:
yield row
def chunks(iterable, size=1000):
iterator = iter(iterable)
for first in iterator:
yield itertools.chain([first], itertools.islice(iterator, size - 1))
prov = 'EntHistory'
path = system.file.openFile("csv")
selectedPath = event.source.parent.getComponent('tagPath').text.split(']')[1]
fullDS = []
for chunk in chunks(readFile(path)):
vals = []
paths = []
quals = []
ts = []
for row in chunk:
paths.append(selectedPath)
d = system.date.parse(row[0],"MM-dd-yyyy HH:mm:ss")
gd = system.date.format(d,"yyyy-MM-dd HH:mm:ss")
vals.append(float(row[1]))
quals.append(192)
ts.append(gd)
system.tag.storeTagHistory(prov,'default',paths,vals,quals,ts)
Just for anyone else testing the code above,
add
from __future__ import with_statement
at the top of Pauls code.
1 Like
Good catch. I think it’s not required in 8.0, since we’re up to Jython 2.7, but I haven’t actually tested.
1 Like
How can i substitute the ‘size’ in def chunks(iterable, size=1000) with ths csv rows number ?
Apologies for trying to revive such an old topic, but I’m trying to achieve something very similar, and I would be delighted if I could get this to work. I think I have all but one line figured out, and that line is:
selectedPath = event.source.parent.getComponent('tagPath').text.split(']')[1]
@MMaynard, please feel free to answer as well if you’d like since this was in your original code, but what is that line referring to and doing?
It appears to be looking at another component in the window called tagPath
, but what is that component, and how is it getting the information required to build the selectedPath
?
The imports we were doing had a CSV file for each tag.
The tagPath was just a text field that we put the path to tag in Ignition that we were importing history for from the CSV.
1 Like
Ah! Perfect; that is exactly what I was missing. Thank you very much!
1 Like