I have a CSV file I need to do a couple things with,
Is this a one time thing?
If so, you might be better off inserting it directly into your database (using MySQL Workbench, etc.) rather than trying to use the dataset to mess with dates.
You can then use SQL to delete/move unwanted data.
There are a few differnt sites that can help you with that import. http://www.mysqltutorial.org/import-csv-file-mysql-table/
However, if you will be regularly opening, modifying, and importing many csv files, your method is probably more appropriate.
@Stuart No, its a monthly thing, I think I have the dataset part figured out though using the map function.
path = system.file.openFile("csv")
if path != None:
csvfile = system.file.readFileAsString(path)
rawfile = csvfile.splitlines()
rawfile = rawfile[52:-1]
def row_func(string):
return string.split(",")
formatted_file = map(row_func, rawfile)
print formatted_file
headers = ["Date","Pressure","Temperature"]
data = system.dataset.toDataSet(headers, formatted_file)
print data
Now just to find out how to filter by date, and drop it into my DB.
Rather than reading the entire file into the dataset I would use .next() to read the file line by line, evaluate if the date is within your parameters, and if so append to a dataset. After the file is parsed, convert the dataset to PyDataSet and use a for loop to run system.db.runPrepUpdate for each record. Here are some good resources:
Actually this was intriguing so I poked around and will share my results. I created a csv file with the following data:
date,pressure,temp
2018-01-01 12:00:00,10,70
2018-01-08 12:00:00,12,82
2018-01-15 12:00:00,8,75
2018-01-22 12:00:00,15,80.66666667
2018-01-29 12:00:00,45,83.16666667
2018-02-03 12:00:00,16,85.66666667
2018-02-10 12:00:00,34,88.16666667
2018-02-14 12:00:00,36.71428571,90.66666667
2018-02-20 12:00:00,40.89285714,93.16666667
2018-03-01 12:00:00,45.07142857,95.66666667
2018-03-14 12:00:00,49.25,98.16666667
2018-03-21 12:00:00,53.42857143,100.6666667
2018-04-01 12:00:00,57.60714286,103.1666667
2018-04-04 12:00:00,61.78571429,105.6666667
2018-04-09 12:00:00,65.96428571,108.1666667
Then I dropped a Date Range Component onto a window and added a button with the following code:
import csv
startDate = event.source.parent.getComponent('Date Range').startDate
endDate = event.source.parent.getComponent('Date Range').endDate
path = "C:/projects/misc/example.csv"
reader = csv.DictReader(open(path))
for row in reader:
date = system.date.parse(row["date"], 'yyyy-MM-dd')
if system.date.isBetween(date, startDate, endDate):
print row
system.db.runPrepUpdate("INSERT INTO csv_data (date, pressure, temp) VALUES (?,?,?)", [date, row["pressure"], row["temp"]], "test")
I believe this accomplishes all of your objectives, right?
It should, I’ll give it a try. Thanks @ethomason
@ethomason
I think its getting closer,
The error I am having now is
Traceback (most recent call last):
File "<event:actionPerformed>", line 17, in <module>
KeyError: Temperature[°C]
Ignition v7.9.5 (b2017111615)
Java: Oracle Corporation 1.8.0_161
This is the code I am using.
import csv
startDate = event.source.parent.getComponent('start_date').date
endDate = event.source.parent.getComponent('end_date').date
path = system.file.openFile("csv")
if path != None:
csvfile = system.file.readFileAsString(path)
rawfile = csvfile.splitlines()
rawfile = rawfile[51:-1]
reader = csv.DictReader(rawfile)
print reader
for row in reader:
date = system.date.parse(row["Date/time"], 'yyyy/MM/dd HH:mm:ss')
pressure = float(row["Pressure[cmH2O]"])
temperature = float(row["Temperature[°C]"])
if system.date.isBetween(date, startDate, endDate):
print row
system.db.runPrepUpdate("INSERT INTO diver_n9147_testing (t_stamp, pressure, temperature) VALUES (?,?,?)", [date, pressure, temperature], "Payette")
What do I need to do to get it to allow the degress symbol? or at least ignore it?
I attached the csv file I am working with.
sws_n9147_180402093818_N9147.csv (37.9 KB)
On Line 17 change
temperature = float(row["Temperature[°C]"])
to
temperature = float(row["Temperature[\xb0C]"])
I also recommend indenting everything after Line 8. If the user cancels out of the “Open File” dialog box it will cause an error.
New Error:
Traceback (most recent call last):
File "<event:actionPerformed>", line 18, in <module>
KeyError: Temperature[�C]
Ignition v7.9.5 (b2017111615)
Java: Oracle Corporation 1.8.0_161
I tried adding
csvfile = csvfile.replace(“Temperature[\xb0C]”, “Temperature[C]”)
After line 8, and changing the key, but it gave me the same key error for Temperature[C].
Are the fields always the same names and count?
You can do float(row[5]) where 5 would be the column number instead of the column name.
Interesting. Both my solution and this work for me on 7.9.7. If I have time I’ll fire up a 7.9.5 designer and check it out
I tested with 7.9.5 and it worked as expected. Here’s what I’m using on a Buttons actionPerformed script:
import csv
startDate = event.source.parent.getComponent('start_date').date
endDate = event.source.parent.getComponent('end_date').date
path = system.file.openFile("csv")
if path != None:
csvfile = system.file.readFileAsString(path)
csvfile = csvfile.replace("Temperature[\xb0C]", "Temperature[C]")
rawfile = csvfile.splitlines()
rawfile = rawfile[51:-1]
reader = csv.DictReader(rawfile)
print reader
for row in reader:
date = system.date.parse(row["Date/time"], 'yyyy/MM/dd HH:mm:ss')
pressure = float(row["Pressure[cmH2O]"])
temperature = float(row["Temperature[C]"])
if system.date.isBetween(date, startDate, endDate):
print row
Where is your code running?
On a button action preformed script, I tried copy and paste with your code and it repeated the error.
Could it be that I am running the script from a Mac computer?
Traceback (most recent call last):
File "<event:actionPerformed>", line 18, in <module>
KeyError: Temperature[C]
Ignition v7.9.5 (b2017111615)
Java: Oracle Corporation 1.8.0_161
The Ignition server is on a Linux server.
I tried this but got the same key error
temperature = float(row[3])
Traceback (most recent call last):
File "<event:actionPerformed>", line 18, in <module>
KeyError: 3
Ignition v7.9.5 (b2017111615)
Java: Oracle Corporation 1.8.0_161
An interesting find here, if i do a print row after line 15, the Temperature column shows as
'Temperature[\ufffdC]'
So I copied that as the key, and it gave this error
Traceback (most recent call last):
File "<buffer>", line 19, in <module>
KeyError: 'Temperature[\\ufffdC]'
Even though the code is
temperature = float(row['Temperature[\ufffdC]'])
I believe i had it, I had to add a u in front of the column name and that worked. Thanks for your help!
Final script:
import csv
startDate = event.source.parent.getComponent('start_date').date
endDate = event.source.parent.getComponent('end_date').date
path = system.file.openFile("csv")
if path != None:
csvfile = system.file.readFileAsString(path)
rawfile = csvfile.splitlines()
rawfile = rawfile[51:-1]
reader = csv.DictReader(rawfile)
for row in reader:
date = system.date.parse(row['Date/time'], 'yyyy/MM/dd HH:mm:ss')
pressure = float(row['Pressure[cmH2O]'])
temperature = float(row[u'Temperature[\ufffdC]'])
if system.date.isBetween(date, startDate, endDate):
system.db.runPrepUpdate("INSERT INTO diver_n9147_testing (t_stamp, pressure, temperature) VALUES (?,?,?)", [date, pressure, temperature], "Payette")
Any idea what the ‘u’ indicates?
specifies that the string inside the quotes is in unicode instead of a raw text string.