XLS Formatter (apply)

[DW14]

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?

[takbb]

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?

[srauner]

@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

[DW14]

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!

[srauner]

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

-Stephen

[DW14]

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

Local:

Server:

I am not sure what it means by remote file?

[takbb]

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.

[DW14]

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?

[takbb]

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!

[DW14]

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

[takbb]

Take a look at the following workflow:

Flow Variable As Output Filename for XLS Formatter – takbb

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.

[DW14]

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

[takbb]

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

[Anjo]

Hi,

we are facing the exact same issue with the path and path for the XLS formatter. I also tried the workflow provided by “takkb” (making the required changes of course - e.g. file name, including the KNIME Server Connector) but nothing works.

The workflow provided by takbb creates the file correctly on the indicated folder on the KNIME Server but fails to open the file located on the server in the XLS Formatter. It seems to try and acces the file on the local machine though.

Both workflow work fine on the local machine.

Would be nice if you could point me out how you solved the problem on the server.

Many thanks in advance

[Anjo]

Another strange behaviour: when you run the workflow on the server and navigate inte XLS Formatter (apply) node manually to the designated file then the path is displayed with errors:

[takbb]

Hi @Anjo, unfortunately I don’t have a Knime server to try things out on, but it looks like somewhere the file location is being passed as an absolute path (starting c:) which is odd.

Would you be able to post your workflow,

In my sample workflow both the input and output files are set by variables where the path is being derived from the Context Properties for the flow.

Your node doesn’t seem to be doing that because there is no message to that effect on the config dialog, so I would think that this is probably where your problem is.

[Anjo]

Hi @takbb,

many thanks for your fast reply. Yes ineed the file location seems to be passed in as an absolute path or rather a mixture of absolute and montpoint-relative path (I tried several path options and this is just one example). The strange thing here is that I was trying to manually navigate to that file by clicking on the browse button (just for testing). Might be due to the fact that I was running the Workflow as " a new job on the server"(?).

Otherwise I was using your workflow passing in the path as flow variable and just modifying the Java Edit Variable node (and adding the KNIME Server Connector nodes for server access).

The red underlined part is actually the only part I changed in your workflow:

The resulting output on the server:

The XLS Formatter fails with this input as well:

Here is the config:

With the Variables set as you indicated (also tried the url same result):

When I use the path in the File reader:

This is just one of the several options I tried. Another Example (as job on the server):

• File Reader
  

  

• XLS Formatter
  

I can’t sent my actually workflow because that particular part is just the component of a much larger workflow but the process is similar and I also tried the extract property part.

Currently I try to get your workflow run on the server but I failed so far. I think if this particular workflow works I wil be able to translate it to my workflow.

I can send you the “FlowVariableAsOutputFilenameXLSFormatter” with the modifications I made if you give me your okay.

I tried several things and it is getting a bit messy, nevertheless I hope I explained it clearly enough to get further input because at the moment I am a bit lost.

Many thanks again.

Best Andreas

[takbb]

Hi @Anjo,

I think I may have spotted one problem. There is an error in your change to the java edit variable. You’ve lost the \ between the path and the filename.

The original code was

java.nio.file.Path path = java.nio.file.Paths.get(
			v_contextworkflowabsolutepath+"\\data\\"+outputfileName);

In your Java Edit Variable, I can see you’ve removed the “data” folder but you need to change it to:

java.nio.file.Path path = java.nio.file.Paths.get(
			v_contextworkflowabsolutepath+"\\"+outputfileName);

You can see in your screenshot of the config for the XLS Formatter (apply) node that there is no backspace toward the end of the Input File and Output File locations.

[Edit: Also, in the demo workflow, if you are not using the workflow’s data folder, you would also need to modify the preceding String to Path (Variable) node as this is defining the path as being relative to the Workflow’s data folder.

You would need to adjust that config to being just the Workflow folder…

]

Give that a try and see if there is an improvement.
If still problems then yes feel free to export the modified XLSFormatter workflow and upload it here and I can take a look. I am going to be out for quite a bit of today though, but I’ll look at it as soon as I can.

Hope that helps.

[takbb]

Hi @Anjo , I have created a new workflow and uploaded to the hub here:

Instead of the additional nodes for picking up the context and folders, it makes use of a component I wrote “Open File or Folder”. This component can be passed the name of a file (or an absolute file path) and it will generate all of the required variables (path, string, url) to work with the different file handling nodes.

So then it is simply a case of Excel Writer and XLS Formatter (apply) being passed different variables . Take a look at the demo workflow and if any questions then please ask.

Flow Variable As Output Filename for XLS Formatter – takbb

[Anjo]

Hallo @takbb,

first of many thanks for your fast reply and helping me out… again actually… that is really really great!.

I corrected it and tried the File Reader to test it:

And the config of the XLS formatter:

I don’t have direct access to the Server but a colleague confirmed that the " /tmp/FlowVariableAsOutputFilenameXLSFormatter254348963334316138/FlowVariableAsOutputFilenameXLSFormatter" exists but does not contain a file.

I can see that the file is generated in the Server section of the KNIME Explorer:

When I click on the file to get the absoulte path, I get the following:
knime://knime-server/Users/xxxx/KNIME_Examples/testoutput.xlsx

But manually including it in the XLS Formatter fails:

Including the previously observed “Info: remote output…” message

The File reader Works:

So yes it seems to have something to do with the path “structure” but maybe also with how the XLS formater handles path strings(?).

The config of the “String to Path” node connected to the KNIME Server Connector has only the “KNIME Server” option:

And now corrected output from the Java Edit Variable is

Maybe it also helps to now the actual folder path on the linux machine to the file:

/home/knime/knime_server/workflow_repository/workflows/Users/xxx/KNIME_Examples

Again many thanks @takbb . I already wish you a nice weekend.

Beste A