Exporting and Importing a CSV
A CSV file, (comma separated values) is one of the most simple structured formats used for exporting and importing datasets. It is a convenient and flexible way to edit and share data across applications. Ignition has a built-in function to convert a dataset to CSV data called system.dataset.toCSV. You can even convert the contents of a CSV to a script and move it to an Ignition component, such as a Power Table.
This section contains examples for exporting and importing data to a CSV as well as converting the contents of a CSV to a script.
The examples on this page make use of Vision components, and run in the Vision Client scope.
Exporting Data to a CSV​
You can export a dataset from a query or table to a CSV file.
Identify the dataset that you want to export to a CSV file. In this case, we can generate some data on a Power Table and export that data. Drag a Power Table component on to your window and toggle its TestData property to generate some data.
Drag a Button component on the window, and double click on the Button to open the Component Scripting window.
Next, let's add our script on the Button component's actionPerformed event. Select the actionPerformed event, and click on the Script Editor tab.
Copy the contents from one of the examples below, and paste the contents to the Script Editor. Notice the system.dataset.toCSV scripting function is used in the first example to export the dataset to a CSV file:
Python - Hard Coded Filepath# Create a variable that references our Power Table. You could modify this part
# of the example to point to a different component in the window.
component = event.source.parent.getComponent('Power Table')
# Use system.dataset.toCSV to turn the dataset into a CSV string
csv = system.dataset.toCSV(component.data)
# Write to local file system. Note the "r" character right before the directory path.
# This denotes a raw string literal, meaning we ignore escape sequences (like the "/")
system.file.writeFile(r"C:\myExports\myExport.csv", csv)
Instead of hardcoding the path as we did in the above example, we could ask the user to select a directory on the local system with system.file.saveFile:
Python - User Selected Directory# Create a variable that references our Power Table. You could modify this part
# of the example to point to a different component in the window.
component = event.source.parent.getComponent('Power Table')
# Use system.dataset.toCSV to turn the dataset into a CSV string.
csv = system.dataset.toCSV(component.data)
# Use system.file.saveFile to have the user find a directory to write to.
filePath = system.file.saveFile("myExport.csv", "csv", "Comma Separated Values")
# We can check the value of filePath to make sure the user picked a path before
# attempting to write.
if filePath:
system.file.writeFile(filePath, csv)To test your scripts, put the Designer in Preview Mode, and press the Button. Open your myExport.csv file and check your data.
Importing Data from a CSV​
There are several ways to import data from a CSV file. First, we could use system.file.readFileAsString to read the entire file as a string. Note, that this will read the file as is, meaning \n can be used to denote new lines.
# Ask the user to find the CSV in the local file system.
path = system.file.openFile("csv")
# Use readFileAsString to read the contents of the file as a string.
# This string will be the parameter we pass to fromCSV below
stringData = system.file.readFileAsString(path)
# Split stringData into a List of strings, delimited by the new line character
stringData = stringData.split("\n")
# Iterate through the list, and do something with each line
for i in range(len(stringData)):
# We're printing the row here, but you could do something more useful with the data.
print stringData[i]
Alternatively, Python's CSV Library could be use to read in the contents of a CSV. In some cases, this is the easier approach, as the reader object is ready to be iterated over. Note, that this approach does read in each row as a List of strings:
# 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")
# 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.
file = open(path)
csvData = csv.reader(file)
# Iterate through the reader object, and do something with each line
for row in csvData:
# We're printing the row here, but you could do something more useful with the data.
print row
# Close the file once we're done with it
file.close()
Converting the Data into a Dataset​
Once you've read in the contents of a CSV into a script, you may wish to move it elsewhere. It is not uncommon to move the data to a Power Table, or other components on the screen. The main difficulty with this is converting the CSV data into a dataset so that it fits into the Power Table component's Data property. We have a couple of approaches listed below.
Calling the system.dataset.fromCSV Function​
The system.dataset.fromCSV function can take a string and convert it to a dataset. Note, that the function expects a very specific format:
#NAMES
Col 1,Col 2,Col 3
#TYPES
I,str,D
#ROWS,6
44,Test Row 2,1.8713151369491254
86,Test Row 3,97.4913421614675
0,Test Row 8,20.39722542161364
25,Test Row 4,20.39722542444222
33,Test Row 5,20.39722542232323
62,Test Row 6,20.39722542111999
Example​
Create a Text file on your local system named "example.csv."
Open the Text file, copy the contents of the "CSV file Content" box above, and paste the contents to the file. Save the changes to the example.csv file.
Let's move the contents of a CSV file to a Power Table. Add a Power Table and a Button component to your window. Double click on the Button component, and paste the following code into the Script Editor of the actionPerformed event:
Python - Using system.dataset.fromCSV()# Ask the user to find the CSV in the local file system.
path = system.file.openFile("csv")
# Use readFileAsString to read the contents of the file as a string.
stringData = system.file.readFileAsString(path)
# Convert the string into a dataset
data = system.dataset.fromCSV(stringData)
# Pass the dataset to the data property on the Power Table.
event.source.parent.getComponent('Power Table').data = data
To execute your script, put the Designer into Preview Mode, and press the Button. A window will open for you for you to navigate and choose your CSV file, then click Open.
Your data will be displayed in the Power Table as shown below.
Calling the csv.reader Function​
As mentioned, system.dataset.fromCSV() requires a specific format, which may not match the format of your file. In this case, we can use Python's CSV Library to parse the file and convert it to a dataset.
Col 1,Col 2,Col 3
44,Test Row 2,1.8713151369491254
86,Test Row 3,97.4913421614675
0,Test Row 8,20.39722542161364
25,Test Row 4,20.39722542444222
33,Test Row 5,20.39722542232323
62,Test Row 6,20.39722542111999
Here is the code to import the above CSV data.
# 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")
# 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.
file = open(path)
csvData = csv.reader(file)
# 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
# Close the file
file.close()