XLS Formatter (apply)

Hello,

I am having issues using the XLS Formatter (apply) node on KNIME server. I was able to get this node working on my local workspace using a custom URL to define the input and output files relative to my workflow. I then used the same logic when deploying the same workflow on server, but I get the following error:

“The stated output path is not a valid file path. null”

I find this to be odd because in the same workflow I use an excel writer with the same path and it executes no problem.

In general I am wondering if there is a way to use a file download widget that outputs an excel file with the changes made using continental XLS formatting nodes?

Are you using flow variables to provide the file path?

I’ve not used the xls formatter and don’t have access to server but I’m guessing this may have something to do with the fact that Excel Writer uses the newer File Path variables but perhaps xls formatter just uses string flow variable for the file name? Maybe that causes it to behave differently on the server to on your local machine?

@DW14,

I believe @takbb might be correct. There are a few new nodes that can translate between string type and path type (string to path, path to string, URL to File Path, …). Dependent on which node you are using you may need to translate the type.

Can you test this and let us know if it works for you?
-Stephen

At first I used flow variables and noticed that the node would only accept string type paths. Although, I still got an error despite changing the File Path Variable to a String Variable. To avoid this issue I just manually entered the file path in the xls formatter node and still got the same issue on server but it still works on my local workspace. I also tried custom URLs and mountpoint type paths and get this issue. The problem is only with the output path but not the input path. Maybe the problem is with the file itself and not the path.

Please let me know if you have any ideas!

Does the path exist on the server?
Are you writing to the workflow repository or somewhere else on the server?

-Stephen

I am writing to somewhere else on the server. I also noticed I get the following warnings on Local and server:

Local:
Screen Shot 2021-04-07 at 11.04.46 AM

Server:
Screen Shot 2021-04-07 at 11.23.19 AM

I am not sure what it means by remote file?

As a test, are you able to change the output path on the server to an absolute path that definitely exists, just to see that it can then write ok.

My thoughts are that it’s either permissions on the server(although that might throw up a different error) or else the relative path isn’t quite going where you think it is and requires a slight modification.

But if you can get it to definitely write using an absolute path that’s a start, although apologies if you’ve already tried that.

I just tried using a this approach with an absolute path that point at a file saved on my desktop and it worked!

Now the question is: Is it possible to have these files stored on server as I want to avoid pointing to my personal files?

Ok, so I had a problem the other day when trying to output xml using “xml combine and write”, which came about because I couldn’t get the node to work with relative paths. I’m not saying that this is the problem here, but maybe my solution could help in this instance (or maybe not! lol )

What I did was add a couple of nodes into the workflow. “Extract Context Properties” and “Java Edit Variable”. The first of those nodes gets the workflow “context” from which the Java Edit variable can then build a path that is relative to the workflow, but which will actually then be supplied to the subsequent node as an “absolute path”.

I’m not saying this is the proper solution to this, but it might work for you and give you a breathing space to find out what is wrong. I’ll pop an example on the hub in a moment, and link to it.

In the Java Edit variable you modify the java code to give it the file name that you want, e.g. MyOutputFile.xlsx

It then pops two flow variables into the flow:
outputFile
outputFileURL (which is outputFile in a url format beginning file:/ )

You can then choose whichever one of these works as the flow variable for your output file in your subsequent xls formatter. Maybe that will work on the server!

1 Like

Thank you so much! I will give this a try.

1 Like

Take a look at the following workflow:

I’ve highlighted with a red annotation box the bit that is important re my above comment. Not certain it will work, and as said it might be that there is a simpler solution, but this might help you with the immediate problem.

2 Likes

I really appreciate your help! The workflow you posted worked like a charm and I got it working on server now:)

2 Likes

Glad it worked for you and pleased to be able to help.

1 Like