Reading from .csv file to table

Is there a way to open a file and transfer it directly into a table on a timed script every 1 minute or so.
I have a constantly updating .csv file and need to import the values so I can use them as temperature readings in my project. I am using the following script to access the file which works but I need an automatic system that is constantly updating.

# Import Python's built-in csv library.
import csv

# Ask the user to find the CSV in the local file system.
path = system.file.openFile("csv","C:\Users\Owner\Google Drive\Dobsons\Programming\Backup.Recorder Data Mar 2017.csv")

#Create a reader object that will iterate over the lines of a CSV.
# We're using Python's built-in open() function to open the file.
csvData = csv.reader(open(path))
 
# Create a List of strings to use as a header for the dataset. Note that the number
# of headers must match the number of columns in the CSV, otherwise an error will occur.
# The simplest approach would be to use next() to read the first line in the file, and
# store that at the header.
header = csvData.next()
 
# Create a dataset with the header and the rest of our CSV.
dataset = system.dataset.toDataSet(header ,list(csvData))
 
# Store it into the table.
event.source.parent.getComponent('Power Table').data = dataset

Can you not set up a connection to your temperature sensors with tags and add history to them to log the data in the db? Then just query the data with a table.

You could run this is a gateway timer script that writes to a dataset tag, then the power table can be bound to that tag.

By the way, you can add triple back quotes to the start and end of your code snippets to make them readable on this forum.

3 Likes

I would recommend having a table in your db or dataset tag that you constantly update via a gateway timer script.

You would open up the .csv file, parse it, update the db table or dataset tag using the parsed data and then close the .csv file.

Then it’s just a matter of binding the Power Table to either a dataset tag or your db table.

While I agree that logging it to a database is the best endgame, the problem is (judging from the filename and the OP saying so) that the csv is appended to and not a new file for every reading. You would then need to filter out whatever lines were already logged to avoid duplicates. Let’s start with his original request and go from there.

I modified your script to grab the latest file in the directory and store it an a dataset tag.

# Import libraries
import os, glob, csv

# Set pathName. Note the asterisk.
pathName ='C:\Users\Owner\Google Drive\Dobsons\Programming\Backup.Recorder*'

fileList = glob.glob(pathName)
lastFile = max(fileList, key = os.path.getmtime)

# Create a reader object that will iterate over the lines of a CSV.
csvData = csv.reader(open(lastFile))

# Get headers 
header = csvData.next()

# Create a dataset with the header and the rest of our CSV.
dataset = system.dataset.toDataSet(header ,list(csvData))

# Store it into the tag.
system.tag.write('[default]path/to/dataset/tag', dataset)
2 Likes

Thanks guys I’ll give it a crack tomorrow.

Hi Guys,

I got most of the code working with a bit from each.
I now have an issue changing the folder I access the data from dynamically.
As below I have been able to write the Year and Day into the correct format but now need to work it into the folder address.
Any Ideas?

“”"Date = system.date.now()
Year = system.date.format(Date, “yyyy”)
Day = system.date.format(Date, “MMMdd”)
system.tag.write(‘CDTest’, Year)
system.tag.write(‘CETest’, Day)

path = ‘C:\Users\Owner\Google Drive\Dobsons\Programming\Backup\Record1\“CDTest”\“CETest”.csv’"""

Still not sure how to add my code to be able to read it properly in this forum sorry

You already defined a strings for year and day. Try:

path = 'C:\Users\Owner\Google Drive\Dobsons\Programming\Backup\Record1\' + Year + '\' + Day + '.csv'

As an aside, the pythonic way is to use forward slashes to separate folders, even in Windows. :slight_smile:

path = 'C:/Users/Owner/Google Drive/Dobsons/Programming/Backup/Record1/' + Year + '/' + Day + '.csv'

use the single back quote between the left tab key and the number 1 key, three in front and three after

Triple-backquotes on a line before and a line after the code section. The marker before the code block can optionally indicate which language is involved, in case autodetection fails. This forum is built on Discourse, which has a page on the details, with links to even more details:

1 Like

Brilliant. Thanks a lot for your help guys. Punched it in first thing this morning and works like a dream. Cheers Jordan

When I try to run the script below I get this error

Error

Traceback (most recent call last):

  File "<event:mouseClicked>", line 14, in <module>

_csv.Error: line contains NULL byte



Ignition v7.9.5 (b2017111615)
Java: Oracle Corporation 1.8.0_211

Script

# Import libraries
import os, glob, csv

# Set pathName. Note the asterisk.
pathName ='C:\Users\jshaffer\Desktop\PARTS ROOM 2019-2020_r1*'

fileList = glob.glob(pathName)
lastFile = max(fileList, key = os.path.getmtime)

# Create a reader object that will iterate over the lines of a CSV.
csvData = csv.reader(open(lastFile))

# Get headers 
header = csvData.next()

# Create a dataset with the header and the rest of our CSV.
dataset = system.dataset.toDataSet(header ,list(csvData))

# Store it into the tag.
system.tag.write('[default]PartsRoomDataset', dataset)

Your raw data contains a character that the csv module can’t handle. You’ll have to remove it.

Hello, my question is can I import the .csv file to the Table Row 1 instead of the header.
I am using the power table. The header data get replaced every time.
FYI, my csv file does not contain header name.
I do not want the data to overwrite my table header.

You will need to define the headers manually, instead of using the file. Example:

header = ['Col1', 'Col2', 'Col3']

Thanks, Sir.

Hello, may I know can I use the above script at client event Tag Change script ?
My objective is when my tag change, I should import the dataset from .csv to a Table.
My table is in the template (template name is SummaryReport) at directory Templates/Report1/
I then place the template to a window (window name is Report) with directory /PopUp Windows/Summary_Report/Data1/
Referring to below, am I missing something ?

csvData = csv.reader(open(Path))
header = [‘Col1’, ‘Col2’, ‘Col3’, ‘Col4’, ‘Col5’, ‘Col6’]
dataset = system.dataset.toDataSet(header,list(csvData))
window = system.gui.getWindow(‘PopUp Windows/Summary_Report/Data1’).getRootContainer()
file = window.rootContainer.getComponent(‘Table’)
event.source.parent.getComponent(file).data = dataset

I don’t think client event scripts don’t have access to the windows. Write the data to a dataset tag, then bind the table to the tag.

when i read from the .csv file, is it correct to have the value as string ? following your suggestion, read from .csv to dataset tag, then bind the table to the tag. I noticed my table column type is string. I would need to total up the values of each row on the table, any idea ?