Importing a CSV file into a dataset, filter it by date, then into a postgresql database

I have a CSV file I need to do a couple things with,

  1. Remove the content before and after the start of the data
    (I have this done using splitlines)
  2. Turn the csv file into a dataset I can use date selectors to pick the time range I want to import
    (Having issues with this part currently)
  3. Import the data that has been filtered by date, into my database.
    (I’m unsure how to do this)

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:

Exporting and Importing a CSV

Inserting from dataset to database

1 Like

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")
1 Like

Any idea what the ‘u’ indicates?

specifies that the string inside the quotes is in unicode instead of a raw text string.