# Reading from .csv file to table

**URL:** <https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614>\
**Category:** Ignition\
**Tags:** scripting\
**Created:** [September 11, 2018, 12:54am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614 "2018-09-11T00:54:21Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![greg1](https://avatars.discourse-cdn.com/v4/letter/g/91b2a8/32.png) [@greg1](https://forum.inductiveautomation.com/u/greg1)\
**Post date:** [September 11, 2018, 12:54am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/1 "2018-09-11T00:54:21Z")

</div>

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.

```auto
# 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

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 11, 2018, 1:08pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/2 "2018-09-11T13:08:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\
**Post date:** [September 11, 2018, 2:06pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/3 "2018-09-11T14:06:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![samngeru](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/samngeru/32/6007_2.png) [@samngeru](https://forum.inductiveautomation.com/u/samngeru)\
**Post date:** [September 12, 2018, 7:06am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/4 "2018-09-12T07:06:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 12, 2018, 12:05pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/5 "2018-09-12T12:05:20Z")

</div>

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.

```auto
# 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)

```

---

<div class="post-metadata">

**Author:** ![greg1](https://avatars.discourse-cdn.com/v4/letter/g/91b2a8/32.png) [@greg1](https://forum.inductiveautomation.com/u/greg1)\
**Post date:** [September 13, 2018, 12:29am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/6 "2018-09-13T00:29:58Z")

</div>

Thanks guys I’ll give it a crack tomorrow.

---

<div class="post-metadata">

**Author:** ![greg1](https://avatars.discourse-cdn.com/v4/letter/g/91b2a8/32.png) [@greg1](https://forum.inductiveautomation.com/u/greg1)\
**Post date:** [September 14, 2018, 2:26am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/7 "2018-09-14T02:26:02Z")

</div>

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’"""

---

<div class="post-metadata">

**Author:** ![greg1](https://avatars.discourse-cdn.com/v4/letter/g/91b2a8/32.png) [@greg1](https://forum.inductiveautomation.com/u/greg1)\
**Post date:** [September 14, 2018, 2:26am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/8 "2018-09-14T02:26:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [September 14, 2018, 9:20am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/9 "2018-09-14T09:20:15Z")

</div>

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

```auto
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. 🙂

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

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [September 14, 2018, 1:10pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/10 "2018-09-14T13:10:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 14, 2018, 2:48pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/11 "2018-09-14T14:48:45Z")

</div>

> [@greg1](#):
>
> Still not sure how to add my code to be able to read it properly in this forum

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:

> **[Select the programming language used in code blocks](https://meta.discourse.org/t/select-the-programming-language-used-in-code-blocks/19247)**
>
> When using triple backticks to enclose code blocks, Discourse will attempt to autodetect the language using highlight.js. If this does not do what you expect, you can type a language name after the first set of backticks: E.g. \`\`\` ruby 3.times...

---

<div class="post-metadata">

**Author:** ![greg1](https://avatars.discourse-cdn.com/v4/letter/g/91b2a8/32.png) [@greg1](https://forum.inductiveautomation.com/u/greg1)\
**Post date:** [September 16, 2018, 8:29pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/12 "2018-09-16T20:29:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 29, 2020, 12:48pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/13 "2020-09-29T12:48:50Z")

</div>

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

Error

```auto
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

```auto
# 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)

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [September 29, 2020, 1:27pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/14 "2020-09-29T13:27:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![thiamweekhoo](https://avatars.discourse-cdn.com/v4/letter/t/e47c2d/32.png) [@thiamweekhoo](https://forum.inductiveautomation.com/u/thiamweekhoo)\
**Post date:** [June 24, 2021, 9:15am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/15 "2021-06-24T09:15:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 24, 2021, 9:42am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/16 "2021-06-24T09:42:36Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![thiamweekhoo](https://avatars.discourse-cdn.com/v4/letter/t/e47c2d/32.png) [@thiamweekhoo](https://forum.inductiveautomation.com/u/thiamweekhoo)\
**Post date:** [June 24, 2021, 10:14am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/17 "2021-06-24T10:14:23Z")

</div>

Thanks, Sir.

---

<div class="post-metadata">

**Author:** ![thiamweekhoo](https://avatars.discourse-cdn.com/v4/letter/t/e47c2d/32.png) [@thiamweekhoo](https://forum.inductiveautomation.com/u/thiamweekhoo)\
**Post date:** [June 24, 2021, 12:34pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/18 "2021-06-24T12:34:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [June 24, 2021, 12:52pm UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/19 "2021-06-24T12:52:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![thiamweekhoo](https://avatars.discourse-cdn.com/v4/letter/t/e47c2d/32.png) [@thiamweekhoo](https://forum.inductiveautomation.com/u/thiamweekhoo)\
**Post date:** [June 25, 2021, 8:50am UTC](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614/21 "2021-06-25T08:50:33Z")

</div>

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 ?

[Next page](https://forum.inductiveautomation.com/t/reading-from-csv-file-to-table/19614.md?page=2)