XLS Formatter (apply)

[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

[takbb]

Hi @Anjo , I tried out the sample workflow locally, and tried to adjust it to write directly to the workflow folder, and for some reason the Excel Writer itself didn’t want to work for me. If you haven’t already done so, take a look at the new workflow I uploaded (mentioned in my last message which you might not have seen), and see if you can use my “Open File or Folder” component in the workflow to supply the Path variable to the Excel Writer, and the String version of the variable to the XLS Formatter. This component can create a path to the Workflow folder, but it will create it as an absolute native filepath rather than a “local” knime path, and I wonder whether using that in both nodes will help, as it worked for me when I tried it here. I hope that would work on the server too!

[Anjo]

Hey @takbb,

you were right I did not see the new workflow provided… sorry. I will test it as soon as I have a slot on the server (hopefully today) and will get back to you.

Maybe I should also clearify, I do not need to specifically write directly to the workflow folder but rather the workflow-group folder (on the server) and more precise in a temporary location but the workflow folder is also fine (the workflow-group folder would be nice as I can see that the file is created when not directly working on the server machine).

I will give an update when I tested the new workflow.

[Anjo]

Hi,

so I tried the new workflow and I was not able to make it work. I think one of the problem that occured is that the path variable was not created correctly when running the job on the server (which I did not observe before).

First I could not use the config of the “Open File or Folder” Component as it is possible when running the workflow on the local machine.

The only changes I made to the Workflow are the KNIME Server Connector node. There you have to provide the “working direktory” (the config I use works in other workflows but may have an inpact on the path structure… or perhaps not)

So I try to recapitualte what happened:

1. Run the new workflow on the server:

• XLS Formatter config:
  
  → File not found

• Check Excel Writer Config - Flow Variables
  

→ the path variable seems to be missing!
→ there was an artifact in the File path of the Excel writer config that seemed to be active when the path variable is missing causing the Excel writer to be executed without failing

• Check with the “Java Edit” node (no configuration, just to have the output of the component)
  

→ there is no path variable (I checked it locally and there the path variable is created)

• Inside the component everything is executed
  

→ I checked also the Component setup and it seems that the variables are forwarded

• I tried to create a path variable from the available strings with the “String to Path (Variable)” node
  
  → well that did not work… and I don’t now why

Conclusion → Confusion:

• There are some strange things going on and I cannot get my head around it.

I should perhaps mention at that point that the KNIME version is 4.3.2 which is neccessary to be compatible with the server version. Perhaps this creates a problem but I and other colleagues have workflows running without these kind of problems.