Write to Excel Template Variable Issue

Ok so currently it does not support paths. That makes sense, we drove ourselves crazy trying to figure out why it wasn’t working.

What about adding the ability to add an input port so I can connect SMB to the node? Would that be a possible add on?

Hi @languy22,
yes i was build around a simple default file selection.
Will check what i can do… but will take a bit to figure out how to get the node working with the path components.

In the meantime you could try to map the smb path to a network drive as a workaround - which should enable you to use the network path instead :slight_smile:

But would mapping the smb path work while the workflow is running on the knime server?

Hi @languy22,
should also be possible to do on servers :thinking:
However most likely a bit tricky depending on your infrastructure…
Will try to get it working as soon as possible


Any update on this?

Hi @languy22,

yes and no :see_no_evil:
I think I got it to work - however I do not have a server to test it on…but it should work in theory :see_no_evil:
I added a “Write To Excel Template (with Path)” node to 4.5 and 4.6, maybe you could give it a quick test?
(will most likely showup tomorrow after the regular community extension refresh)

In the meantime I’ll look for a solution on how to test it on my side…maybe I’ll find a SMB server somewhere :thinking:

@languy22 did you manage to checkout the update yet? :slight_smile:
Would be interessted if the change worked for SMB

Sorry not yet. I’ve been working with some Power BI stuff and haven’t had a chance to jump back into knime. I’ll update and try running it tomorrow and if it looks ok I’ll push it to our server and see what it does.

I’ll let you know at the latest early next week.

1 Like

I was able to try out the new node, and I get this error.

ERROR Write To Excel Template (with Path) 3:67 Execute failed: Reason: No file system connection available. Execute connector node.

Even though I have the connector note ran and executed.

1 Like

I also pulled the KNIME Log for more information if you need it. See below about specific Error

2022-11-11 15:37:11,852 : ERROR : KNIME-Worker-45-Write To Excel Template (with Path) 3:67 :  : Node : Write To Excel Template (with Path) : 3:67 : Execute failed: Reason: No file system connection available. Execute connector node.
org.knime.core.node.InvalidSettingsException: Reason: No file system connection available. Execute connector node.
	at org.AF.ExcelUtilities.WriteToExcelTemplateWithPath.WriteToExcelTemplateWithPathNodeModel.execute(WriteToExcelTemplateWithPathNodeModel.java:471)
	at org.knime.core.node.NodeModel.executeModel(NodeModel.java:549)
	at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1267)
	at org.knime.core.node.Node.execute(Node.java:1041)
	at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
	at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
	at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:201)
	at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:117)
	at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:367)
	at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:221)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
	at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.lang.IllegalStateException: No file system connection available. Execute connector node.
	at org.knime.filehandling.core.defaultnodesettings.filechooser.FileChooserPathAccessor.lambda$0(FileChooserPathAccessor.java:139)
	at java.base/java.util.Optional.orElseThrow(Unknown Source)
	at org.knime.filehandling.core.defaultnodesettings.filechooser.FileChooserPathAccessor.getConnection(FileChooserPathAccessor.java:138)
	at org.knime.filehandling.core.defaultnodesettings.filechooser.FileChooserPathAccessor.initializeFileSystem(FileChooserPathAccessor.java:145)
	at org.knime.filehandling.core.defaultnodesettings.filechooser.FileChooserPathAccessor.getOutputPath(FileChooserPathAccessor.java:162)
	at org.knime.filehandling.core.defaultnodesettings.filechooser.FileChooserPathAccessor.getRootPath(FileChooserPathAccessor.java:293)
	at org.knime.filehandling.core.defaultnodesettings.filechooser.FileChooserPathAccessor.getFSPaths(FileChooserPathAccessor.java:202)
	at org.AF.ExcelUtilities.WriteToExcelTemplateWithPath.WriteToExcelTemplateWithPathNodeModel.execute(WriteToExcelTemplateWithPathNodeModel.java:296)
	... 13 more
1 Like

Hi @languy22,

thanks for testing - I think I found the issue.
At least it now works with sFTP as file input (still have no SMB to test :frowning: )

I pushed the update which will hopefully be available in the new 30-40min.
Could you check again from your side?

Hello @AnotherFraudUser

I tested it out, it seems to make it farther, but it errors out when it tries to write null data. At least that is what it looks like to me. This is the error I get. I know the Knime Excel writer has an option that asks you what to do with blank data.

ERROR Write To Excel Template (with Path) 0:67       Execute failed: Reason: Error while writing null


1 Like

Hi @languy22,

Thanks. Does it work without nulls?
E.g. a simple table with a few values?

Also did you get this error while wrtiting into the template file or when you are in the new file mode? :thinking:

Also if you can share a concrete example that would be great - else I will try with nulls of different column types :thinking:

I am using create a new file mode. I also tried template mode and it throws the same error.

I tested with a table with no nulls, and it throws the same error.

I tried using a different file in the “Template File Selection” area that was a simple table and it worked. So, it seems the issue is on that side not the new data being written.

I tried the SMB and the data I manipulate to add to the new file, and it all works great.

1 Like

What I just noticed is that when I try to use different files in the “Template File Selection” area that the “sheet name” is not chaning. The file I have pulled in now does not have any sheet named “SalesOrders” and the drop down shows nothing. I don’t think it is importing the file correctly.

EDIT: if I use get sheet by index instead of get sheet by name it all works ok.

1 Like

Hi @languy22,

thanks for the input!
So even your previous file works now? :partying_face:
Will fix the sheet name lookup soon (most likely it does not pull it through the connection yet)
That should be an easy fix :slight_smile:

So far. I am trying to do a final run through to valadate everything but our oracle database is having issues so I don’t know if I will get it done today. Once I have validated everything, I will let you know. Thanks for working on this. It’s appreciated.

1 Like

Great thanks :+1: - will plan to fix the sheet names issue as possible.

I think I found another issue. It seems not to be writing any of the data in the new file it creates. It creates the new file but both the new file and the old file both have 20686 rows of data while the knime stream coming into the node has 20907. So it’s not adding the new data.

EDIT: Ran it again and now it did add all the new data weird. But I did notice that a column that is coming into the node as Local date is being outputted at general in excel. That means excel doesn’t know what to do with it and it messes up all of the pivot tables in the excel workbook.

1 Like

Great - will try to test also from my side if I see anything strange with the adding of new rows :thinking:
For the local date I will check, might be that I did not add a localDate to date conversion yet (only the old date knime format)