Using system.dataset.fromCSV in a Gateway timer script

Matthew_Karee · January 30, 2025, 4:40pm

I have machine data that I am storing in a CSV file, and I want to pull this data from the CSV file into a dataset tag for live analysis. I am stuck with Ignition Edge for now, so querying a database is not an option.

I'm using this method (along with system.file.readFileAsString):

Here is the CSV file I am trying to read:
Book8.csv (2.1 KB)

Here is my Gateway script:

path1 = '/home/dev/unsecured/Book8.csv'
dataString = system.file.readFileAsString(path1)
logger = system.util.getLogger("testlogger")
logger.info(dataString)
data1 = system.dataset.fromCSV(dataString)

And here is my Gateway log:

Here's the expanded error message:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 5, in ValueError: CSV invalid format: expected #NAMES on line 1

at org.python.core.Py.ValueError(Py.java:334)

at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSV(DatasetUtilities.java:693)

at jdk.internal.reflect.GeneratedMethodAccessor44151.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:208)

at org.python.core.PyObject.__call__(PyObject.java:461)

at org.python.core.PyObject.__call__(PyObject.java:465)

at org.python.pycode._pyx1357.f$0(:5)

at org.python.pycode._pyx1357.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyCode.call(PyCode.java:18)

at org.python.core.Py.runCode(Py.java:1687)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:800)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:819)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:748)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:800)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:90)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.python.core.PyException: ValueError: CSV invalid format: expected #NAMES on line 1

... 25 common frames omitted

Any idea what the issue is here? I suspect it's due to how I have my CSV file formatted, but I thought I had matched the required formatting for system.dataset.fromCSV

pturmel · January 30, 2025, 4:42pm

Check the documentation for system.dataset.fromCSV(). It requires a specific format with extra header lines. Any old CSV will not work. The error says you didn't get it right.

Matthew_Karee · January 30, 2025, 4:44pm

If you look at the file I attached, I do have those header lines in there.

#NAMES
cycle_number,date,time,AM/PM,act_time,act_energy,peak_power,abs_position,collapse_distance,pressure,mode,downspeed
#TYPES
I,str,str,str,I,I,D,D,D,I,str,D  
#ROWS,32
7449,1/30/2025,9:52:07,AM,765,442,47,1.9376,0.0345,45,E,1.5
7450,1/30/2025,9:52:53,AM,770,444,47.5,1.9375,0.035,45,E,1.5
7451,1/30/2025,9:55:11,AM,765,440,42,1.9372,0.0341,45,E,1.5
7452,1/30/2025,9:56:10,AM,775,440,48.5,1.9369,0.034,45,E,1.5
7453,1/30/2025,9:58:33,AM,760,443,44.5,1.9374,0.0346,45,E,1.5
7454,1/30/2025,9:59:22,AM,775,442,47.5,1.9374,0.0344,45,E,1.5
7455,1/30/2025,10:01:27,AM,765,443,47.5,1.9369,0.0344,45,E,1.5
7456,1/30/2025,10:02:59,AM,760,441,48,1.9369,0.0341,45,E,1.5
7457,1/30/2025,10:05:40,AM,795,442,50.5,1.9361,0.0337,45,E,1.5
7458,1/30/2025,10:06:11,AM,765,442,45.5,1.9376,0.0351,45,E,1.5
7459,1/30/2025,10:07:55,AM,785,442,47.5,1.9371,0.0344,45,E,1.5
7460,1/30/2025,10:09:59,AM,760,440,47,1.9374,0.0345,45,E,1.5
7461,1/30/2025,10:14:39,AM,760,442,47.5,1.9377,0.035,45,E,1.5
7462,1/30/2025,10:15:55,AM,775,441,46.5,1.9373,0.0342,45,E,1.5
7463,1/30/2025,10:19:49,AM,780,442,48,1.9367,0.0345,45,E,1.5
7464,1/30/2025,10:21:18,AM,770,442,42.5,1.9372,0.0344,45,E,1.5
7465,1/30/2025,10:22:19,AM,790,441,45.5,1.9365,0.0339,45,E,1.5
7466,1/30/2025,10:23:46,AM,750,440,45.5,1.9371,0.0343,45,E,1.5
7467,1/30/2025,10:24:42,AM,770,441,44.5,1.9373,0.0349,45,E,1.5
7468,1/30/2025,10:27:21,AM,760,442,49,1.937,0.0347,45,E,1.5
7469,1/30/2025,10:30:11,AM,760,443,47.5,1.9378,0.0352,45,E,1.5
7470,1/30/2025,10:31:26,AM,760,444,48,1.9367,0.0344,45,E,1.5
7471,1/30/2025,10:32:51,AM,750,443,46,1.9366,0.0342,45,E,1.5
7472,1/30/2025,10:34:13,AM,760,440,45,1.9364,0.0339,45,E,1.5
7473,1/30/2025,10:36:26,AM,780,443,48,1.9369,0.0343,45,E,1.5
7474,1/30/2025,10:40:02,AM,760,442,47.5,1.9376,0.0348,45,E,1.5
7475,1/30/2025,10:52:52,AM,775,441,45,1.9381,0.0349,45,E,1.5
7476,1/30/2025,10:55:09,AM,755,444,51,1.9389,0.0355,45,E,1.5
7477,1/30/2025,10:57:07,AM,790,443,40.5,1.9384,0.0351,45,E,1.5
7478,1/30/2025,10:58:26,AM,780,441,42.5,1.9381,0.0349,45,E,1.5
7479,1/30/2025,10:58:59,AM,785,441,42.5,1.9379,0.0343,45,E,1.5
7480,1/30/2025,11:01:07,AM,785,440,49.5,1.9379,0.035,45,E,1.5

pturmel · January 30, 2025, 4:46pm

Look for a unicode BOM at the start. That will mess it up, IIRC. Consider creating a sample dataset and exporting it, and seeing if that will import successfully.

Matthew_Karee · January 30, 2025, 5:10pm

Hmm I tried saving the CSV file with just 'UTF-8' encoding instead of 'UTF-8 with BOM', and I'm now getting a different error in the Gateway log:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 5, in at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source) at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source) at java.base/java.lang.ClassLoader.loadClass(Unknown Source) at java.base/java.lang.Class.forName0(Native Method) at java.base/java.lang.Class.forName(Unknown Source) at com.inductiveautomation.ignition.common.xmlserialization.ClassNameResolver.classForNameImpl(ClassNameResolver.java:196) at com.inductiveautomation.ignition.common.xmlserialization.ClassNameResolver.classForName(ClassNameResolver.java:169) at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSVJava(DatasetUtilities.java:741) at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSV(DatasetUtilities.java:691) at jdk.internal.reflect.GeneratedMethodAccessor44151.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.ClassNotFoundException: java.lang.ClassNotFoundException: D

at org.python.core.Py.JavaError(Py.java:547)

at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSV(DatasetUtilities.java:697)

at jdk.internal.reflect.GeneratedMethodAccessor44151.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:208)

at org.python.core.PyObject.__call__(PyObject.java:461)

at org.python.core.PyObject.__call__(PyObject.java:465)

at org.python.pycode._pyx1653.f$0(:5)

at org.python.pycode._pyx1653.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyCode.call(PyCode.java:18)

at org.python.core.Py.runCode(Py.java:1687)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:800)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:819)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:748)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:800)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:90)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.python.core.PyException: java.lang.ClassNotFoundException: java.lang.ClassNotFoundException: D

... 25 common frames omitted

Caused by: java.lang.ClassNotFoundException: D

at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)

at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)

at java.base/java.lang.ClassLoader.loadClass(Unknown Source)

at java.base/java.lang.Class.forName0(Native Method)

at java.base/java.lang.Class.forName(Unknown Source)

at com.inductiveautomation.ignition.common.xmlserialization.ClassNameResolver.classForNameImpl(ClassNameResolver.java:196)

at com.inductiveautomation.ignition.common.xmlserialization.ClassNameResolver.classForName(ClassNameResolver.java:169)

at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSVJava(DatasetUtilities.java:741)

at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSV(DatasetUtilities.java:691)

... 23 common frames omitted

Matthew_Karee · January 30, 2025, 5:18pm

If I wanted to create a sample dataset, could I create a dataset tag and export from there? Or do you mean to create a dataset in a script?

pturmel · January 30, 2025, 5:19pm

Use a script to export a dataset from wherever you like. Creating it in a memory tag is simple.

Matthew_Karee · January 30, 2025, 10:17pm

Made some progress here by exporting a dataset tag to CSV, reading from the same CSV, then writing to a different dataset tag and it worked:

data = system.tag.readBlocking('[edge]Subassembly_lookup')[0].value
csv = system.dataset.toCSV(dataset = data, showHeaders = True, forExport = True)
filePath = "/home/dev/unsecured/testing2.csv"
system.file.writeFile(filePath, csv)

data2 = system.file.readFileAsString("/home/dev/unsecured/testing2.csv")
csv2 = system.dataset.fromCSV(data2)
system.tag.writeBlocking('[edge]csv_test', csv2)

The exported CSV file looks like this:

"#NAMES"
"Subassembly","Staple Length","Cartridge Color","Cartridge Length","Description","Color","ColorHex"
"#TYPES"
"str","str","str","str","str","clr","str"
"#ROWS","12"
"720341","3.25mm","Orange","60mm","Bed Assembly 3.25/60","color(255,140,0,255)","#FFAC47"
"720311","2.0mm","Gray","60mm","Bed Assembly 2.0/60","color(213,213,213,255)","#D5D5D5"
"720340","2.5mm","White","60mm","Bed Assembly 2.5/60","color(255,255,255,255)","#FFFFFF"
"720342","4.0mm","Purple","60mm","Bed Assembly 4.0/60","color(217,0,217,255)","#CF3BDBE3"
"700437","5.0mm","Black","45mm","Bed Assembly 5.0/45","color(128,128,128,255)","#808080"
"700446","5.0mm","Black","60mm","Bed Assembly 5.0/60 ","color(128,128,128,255)","#808080"
"720331","2.0mm","Gray","30mm","Bed Assembly 2.0/30","color(213,213,213,255)","#D5D5D5"
"700278","2.5mm","White","45mm","Bed Assembly 2.5/45","color(255,255,255,255)","#FFFFFF"
"720327","3.25mm","Orange","45mm","Bed Assembly 3.25/45","color(255,140,0,255)","#FFAC47"
"700331","4.0mm","Purple","45mm","Bed Assembly 4.0/45","color(217,0,217,255)","#CF3BDBE3"
"000000","N/A","N/A","N/A","Engineering","color(255,255,255,255)","#FFFFFF"
"720326","2.5mm","White","45mm","Bed Assembly 2.5/45","color(255,255,255,255)","#FFFFFF"

So it seems like the only difference here is that each entry is in quotes.

So I created a separate CSV file:
Book13.csv (569 Bytes)

Contents of the CSV file:

"#NAMES"
"cycle_number","date","time","AM/PM","act_time","act_energy","peak_power","abs_position","collapse_distance","pressure","mode","downspeed"
"#TYPES"
"I","str","str","str","I","I","D","D","D","I","str","D"            
"#ROWS","4"
"7449","1/30/2025","9:52:07","AM","765","442","47","1.9376","0.0345","45","E","1.5"
"7450","1/30/2025","9:52:53","AM","770","444","47.5","1.9375","0.035","45","E","1.5"
"7451","1/30/2025","9:55:11","AM","765","440","42","1.9372","0.0341","45","E","1.5"
"7452","1/30/2025","9:56:10","AM","775","440","48.5","1.9369","0.034","45","E","1.5"

And am attempting to write this information to a dataset tag:

data2 = system.file.readFileAsString("/home/dev/unsecured/Book13.csv")
csv2 = system.dataset.fromCSV(data2)
system.tag.writeBlocking('[edge]csv_test', csv2)

However, I keep getting an error in the Gateway log suggesting the class of the last entry (the last item in the 4th line of my CSV file - "D") is not recognizable. I know it is specifically that last entry because I've tried changing it to every possible data type ("str","i","l","f","d", etc.), and that last entry always matches the entry called out in the error message:

Caused by: java.lang.ClassNotFoundException: D"

Caused by: java.lang.ClassNotFoundException: d"

Caused by: java.lang.ClassNotFoundException: str"

Any idea why this would be?

Matthew_Karee · January 30, 2025, 10:21pm

An example of the full error message (when the last entry is set as type "D") in the Gateway, for reference:

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 13, in at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source) at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source) at java.base/java.lang.ClassLoader.loadClass(Unknown Source) at java.base/java.lang.Class.forName0(Native Method) at java.base/java.lang.Class.forName(Unknown Source) at com.inductiveautomation.ignition.common.xmlserialization.ClassNameResolver.classForNameImpl(ClassNameResolver.java:196) at com.inductiveautomation.ignition.common.xmlserialization.ClassNameResolver.classForName(ClassNameResolver.java:169) at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSVJava(DatasetUtilities.java:741) at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSV(DatasetUtilities.java:691) at jdk.internal.reflect.GeneratedMethodAccessor44151.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.ClassNotFoundException: java.lang.ClassNotFoundException: D"

at org.python.core.Py.JavaError(Py.java:547)

at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSV(DatasetUtilities.java:697)

at jdk.internal.reflect.GeneratedMethodAccessor44151.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:208)

at org.python.core.PyObject.__call__(PyObject.java:461)

at org.python.core.PyObject.__call__(PyObject.java:465)

at org.python.pycode._pyx2431.f$0(:14)

at org.python.pycode._pyx2431.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyCode.call(PyCode.java:18)

at org.python.core.Py.runCode(Py.java:1687)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:800)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:819)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:748)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:800)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:90)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: org.python.core.PyException: java.lang.ClassNotFoundException: java.lang.ClassNotFoundException: D"

... 25 common frames omitted

Caused by: java.lang.ClassNotFoundException: D"

at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)

at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)

at java.base/java.lang.ClassLoader.loadClass(Unknown Source)

at java.base/java.lang.Class.forName0(Native Method)

at java.base/java.lang.Class.forName(Unknown Source)

at com.inductiveautomation.ignition.common.xmlserialization.ClassNameResolver.classForNameImpl(ClassNameResolver.java:196)

at com.inductiveautomation.ignition.common.xmlserialization.ClassNameResolver.classForName(ClassNameResolver.java:169)

at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSVJava(DatasetUtilities.java:741)

at com.inductiveautomation.ignition.common.script.builtin.DatasetUtilities.fromCSV(DatasetUtilities.java:691)

... 23 common frames omitted

pturmel · January 31, 2025, 12:45pm

~~No further clue.~~

Actually, I just noticed stray whitespace of some kind at the end of your line 4.

JordanCClark · January 31, 2025, 1:21pm

Your original file has something in front of #NAMES. If I print dataString, you can see it.

And, as Phil already mentioned, there is whitespace after the final 'D' on your datatypes.

JordanCClark · January 31, 2025, 1:39pm

I can confirm the BOM at the start of the file.

If you have control over if the BOM is generated, then that's the place to fix it.

Otherwise, you can test for it and skip it in your dataset conversion.

path1 = '/home/dev/unsecured/Book8.csv'
dataString = system.file.readFileAsString(path1)

#Test for BOM 
if hex(ord(dataString[0])) == '0xfeff':
	ndx = 1
else:
	ndx = 0

logger = system.util.getLogger("testlogger")
logger.info(dataString)
data1 = system.dataset.fromCSV(dataString[ndx:])

MMaynard · January 31, 2025, 1:45pm

Not sure if I am going crazy or not, but that linked CSV file, the D is missing the closing "

JordanCClark · January 31, 2025, 2:27pm

Is that mutually exclusive? In my case, both are true.