Copying an Excel file to a table's dataset

[Sayantan_Chatterjee]

Hi Jordan
If in the excel sheet one column consist of integer and string both kind of data, its giving error. Here column 3 is giving error.

[JordanCClark]

@lrose

Make sure that your work book columns do not have mixed data types, (i.e. String, Double). In the work book you provided the column for peel thickness row 6 was the value 0.191., row 7 was 0.210. and row 8 was "Test". Those are strings and the conversion to dataset will fail because columns can only contain one type. If it needs to be mixed, then they should all be strings.

What happens of you set the cell formatting to text?

[Sayantan_Chatterjee]

Yes. I have made the cell format in text but it doesn't help

[lrose]

What is the error?

[Sayantan_Chatterjee]

Traceback (most recent call last):
File "", line 135, in
File "module:ExcelData", line 91, in excelToDataSet
TypeError: Unable to convert row 2, column 5 to type class java.lang.String

[John_Wick]

Thank you for amazing Script. It help a lot.
There is something I want to ask.
The script is working perfect in some excels files.
But It can't read some rows of one of my excel file.
It show me Error_ExpressionEval.
I remove those rows and it can read and add the data into tag.
When I added the removed rows again, it is not working.
Is it because of my excel file? or are there limit in formulatype(Cell value) ?
I will waiting for you reply.

[JordanCClark]

If you can supply a sample Excel file that doesnt work, i can take a look at it.

[jlandwerlen]

It could be related to the data in the cell. From my experience, the data type is determined by the first row... so if it sees an integer then that is what it expects from there on out. If it then sees a string, it will barf.

[JordanCClark]

You have mixed datatypes in the first column. A2 is a String which is different than the rest. After setting it to the same datatype, it worked as it should.

[Robbe_Meyvis]

Hi @JordanCClark !
Everyone is refering to your example so it must be good!

However, I always keep getting an error and I can't figure it out, neither find anything useful about it...

When I use an excel like this:

I can read it with you python code.

However, when I try something like this:

or this:

I keep getting this error:

Error running action 'dom.onClick' on Page/onsite/p3@D/root/CoordinateContainer/Button_2: Traceback (most recent call last): File "function:runAction", line 9, in runAction File "module:ExcelRead", line 22, in excelToDataSet at org.apache.commons.compress.archivers.zip.ZipArchiveInputStream.skipRemainderOfArchive(ZipArchiveInputStream.java:1378) at org.apache.commons.compress.archivers.zip.ZipArchiveInputStream.getNextZipEntry(ZipArchiveInputStream.java:760) at org.apache.poi.openxml4j.util.ZipArchiveThresholdInputStream.getNextEntry(ZipArchiveThresholdInputStream.java:141) at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.(ZipInputStreamZipEntrySource.java:49) at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:106) at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:307) at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:134) at jdk.internal.reflect.GeneratedMethodAccessor73.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.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:341) at org.apache.poi.ss.usermodel.WorkbookFactory.createXSSFWorkbook(WorkbookFactory.java:316) at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:234) at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:200) at jdk.internal.reflect.GeneratedMethodAccessor71.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.io.IOException: java.io.IOException: Truncated ZIP file

This is how I call it via a button in Perspective:

def runAction(self, event):
	import ExcelRead
	fName = 'C:/excel/file5.xlsx'
	self.parent.custom.excelds = ExcelRead.excelToDataSet(fName, True)

I have your code in the project library.
Line 22:

(used a screenshot so you can see the linenumbers)

Do you have any idea why I keep getting the "Truncated ZIP file" error?

[pturmel]

File too large? Default limit is 10MB:

Perspective - File Upload | Ignition User Manual

[JordanCClark]

Doesn't appear to be using file upload.

@Robbe_Meyvis Refer to this portion of the thread.

Copying an Excel file to a table's dataset

Hi Jordan Thanks for the code. Could you please update the code so it get file bytes instead of file path. I need this because in perspective we only can upload file to server and deal with file stream.

[Robbe_Meyvis]

It's just a few kilobytes. Just to test and get it working now.

[Robbe_Meyvis]

@JordanCClark I'm not uploading the file, just reading it from the C drive.
So I don't think i need the ByteArrayInputStream?
It was also working with very simple files...

I tried it anyway, but couldn't really figure out how to get the ByteArrayInputStream from my Excel file though...

[JordanCClark]

Can you post a sample Excel file, as a sanity check?

Also are you using Perspective, or Perspecive Workstation? Perspective, as a rule, doesn't let you interact with the local file system, since it's browser based.

[Robbe_Meyvis]

file.xlsx (8.4 KB)
file2.xlsx (8.7 KB)

file.xlsx does NOT work.
file2.xlsx DOES work.

I am currently testing from the designer.
I have this event script on a button:

I have the function in the project library:

I'm writing to a custom property "excelds":

[JordanCClark]

Since I get a completly different error, (FIle not found), I'm going to assume you are running the designer on the same machine as the gateway. In the wild, most installs aren't like this.

You will need to use the upload component.

View with appropriate scripting attached.
test_Excel_Upload_2024-06-25_1355.zip (22.2 KB)

[Robbe_Meyvis]

Thank you so much for your effort!

I tried it but it's really driving me crazy.

With file2.xlsx, your example does work.
With file.xlsx, I'm again getting the same error...

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 3, in runAction File "", line 186, in fromExcelBytes File "", line 211, in processExceltoDataset at org.apache.commons.compress.archivers.zip.ZipArchiveInputStream.readDeflated(ZipArchiveInputStream.java:1098) at org.apache.commons.compress.archivers.zip.ZipArchiveInputStream.read(ZipArchiveInputStream.java:1017) at java.base/java.io.FilterInputStream.read(Unknown Source) at org.apache.poi.openxml4j.util.ZipArchiveThresholdInputStream.read(ZipArchiveThresholdInputStream.java:80) at org.apache.poi.util.IOUtils.toByteArray(IOUtils.java:152) at org.apache.poi.util.IOUtils.toByteArray(IOUtils.java:121) at org.apache.poi.openxml4j.util.ZipArchiveFakeEntry.(ZipArchiveFakeEntry.java:47) at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.(ZipInputStreamZipEntrySource.java:53) at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:106) at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:307) at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:134) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:341) at org.apache.poi.ss.usermodel.WorkbookFactory.createXSSFWorkbook(WorkbookFactory.java:316) at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:234) at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:200) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.io.IOException: java.io.IOException: Truncated ZIP file

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

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

at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:192)

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._pyx10.processExceltoDataset$8(:282)

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

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

at org.python.core.PyBaseCode.call(PyBaseCode.java:306)

at org.python.core.PyFunction.function___call__(PyFunction.java:474)

at org.python.core.PyFunction.call(PyFunction.java:469)

at org.python.core.PyFunction.call(PyFunction.java:459)

at org.python.pycode._pyx10.fromExcelBytes$7(:186)

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

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

at org.python.core.PyBaseCode.call(PyBaseCode.java:306)

at org.python.core.PyBaseCode.call(PyBaseCode.java:141)

at org.python.core.PyFunction.call(PyFunction.java:426)

at org.python.pycode._pyx12.runAction$1(:3)

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

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

at org.python.core.PyBaseCode.call(PyBaseCode.java:306)

at org.python.core.PyFunction.function___call__(PyFunction.java:474)

at org.python.core.PyFunction.call(PyFunction.java:469)

at org.python.core.PyFunction.call(PyFunction.java:464)

at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:847)

at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:829)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:868)

at com.inductiveautomation.ignition.common.script.ScriptManager$ScriptFunctionImpl.invoke(ScriptManager.java:1010)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$AutoRecompilingScriptFunction.invoke(ProjectScriptLifecycle.java:950)

at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:161)

at com.inductiveautomation.perspective.gateway.script.ScriptFunctionHelper.invoke(ScriptFunctionHelper.java:98)

at com.inductiveautomation.perspective.gateway.action.ScriptAction.runAction(ScriptAction.java:80)

at com.inductiveautomation.perspective.gateway.action.ActionDecorator.runAction(ActionDecorator.java:18)

at com.inductiveautomation.perspective.gateway.action.SecuredAction.runAction(SecuredAction.java:44)

at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.lambda$call$0(ActionCollection.java:263)

at com.inductiveautomation.perspective.gateway.api.LoggingContext.mdc(LoggingContext.java:54)

at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:252)

at com.inductiveautomation.perspective.gateway.model.ActionCollection$ActionSequence$ExecuteActionsTask.call(ActionCollection.java:221)

at com.inductiveautomation.perspective.gateway.threading.BlockingTaskQueue$TaskWrapper.run(BlockingTaskQueue.java:154)

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

at java.base/java.util.concurrent.FutureTask.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 com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58)

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

Caused by: org.python.core.PyException: java.io.IOException: java.io.IOException: Truncated ZIP file

... 47 common frames omitted

Caused by: java.io.IOException: Truncated ZIP file

at org.apache.commons.compress.archivers.zip.ZipArchiveInputStream.readDeflated(ZipArchiveInputStream.java:1098)

at org.apache.commons.compress.archivers.zip.ZipArchiveInputStream.read(ZipArchiveInputStream.java:1017)

at java.base/java.io.FilterInputStream.read(Unknown Source)

at org.apache.poi.openxml4j.util.ZipArchiveThresholdInputStream.read(ZipArchiveThresholdInputStream.java:80)

at org.apache.poi.util.IOUtils.toByteArray(IOUtils.java:152)

at org.apache.poi.util.IOUtils.toByteArray(IOUtils.java:121)

at org.apache.poi.openxml4j.util.ZipArchiveFakeEntry.(ZipArchiveFakeEntry.java:47)

at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.(ZipInputStreamZipEntrySource.java:53)

at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:106)

at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:307)

at org.apache.poi.xssf.usermodel.XSSFWorkbookFactory.createWorkbook(XSSFWorkbookFactory.java:134)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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.apache.poi.ss.usermodel.WorkbookFactory.createWorkbook(WorkbookFactory.java:341)

at org.apache.poi.ss.usermodel.WorkbookFactory.createXSSFWorkbook(WorkbookFactory.java:316)

at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:234)

at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:200)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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)

... 44 common frames omitted

Since the goal would be to read files from a fileshare for example, I'll try to test with a gatewayscript instead of Perspective.
I'll let you know if that works!

[JordanCClark]

I can say that both files worked for me.
Which version of Ignition are you using? 8.1.39 (i think) upgraded the POI libraries.

[Robbe_Meyvis]

Using a gateway script gives the same error: truncated ZIP file...

I'm using version 8.1.37 (b2024013011).

I'll look into updating the POI versions. Just don't know yet how to find the current version and how to update.

Which version of the POI libraries are you using?