KNIME 4.3 - New Excel Reader is buggy with flow variables based configuration

[ddamasceno]

If you set the Sheet Name with an incoming flow variable, the returned data will be from the correct sheet but the column names will be those of the selected sheet in the UI.
You got the same problem when passing Column/From To/From by flow variables.

bugs-excel-knime-4.3.knar (21.7 KB)

[SimonS]

Hi @ddamasceno,

thanks for reporting this, I was able to reproduce it. The technical explanation of the bug is that the table spec is created and saved when the preview in the dialog is created. Unless the path to the file that should be read is not changed, the spec is not re-calculated during node execution. We definitely need to fix this and it needs also be re-calculated if a config is changed that affects the spec.

A workaround that hopefully works for you - although not pretty and nice at all - is to also control the path via flow variable. During the configuration of the dialog, this path needs to be different than during the execution. This leads to re-calculation of the spec. Take a look at the String to Path (Variable) node, it might be helpful for you.

Could look e.g. like this:

Let me know if you have any issues or questions regarding that workaround. I will let you know once this bug is fixed.

Best,
Simon

Edit: The issue has been fixed with a new option and the workaround is not required anymore, see below post.

[ddamasceno]

Thanks @SimonS for this quick answer !
I’ll try your quick fix and let you know if it doesn’t work.
Best
D.

[ddamasceno]

Hi @SimonS

I’ve tried your workaround but it doesn’t really help.

While trying your solution, I discovered another unexpected behaviour :

When the excel file path changes, some UI settings are reset to default.
So if you set, in the UI, “Sheet selection” to “Select sheet with name”, once you change the file path it comes back to “Select first sheet with data”.

I understand that is it related to a “tight coupling” between all these parameters and the preview (maybe a “refresh button” is unavoidable ?)

D.

[SimonS]

@ddamasceno

I’ve tried your workaround but it doesn’t really help.

Why does it not help?

@ddamasceno

When the excel file path changes, some UI settings are reset to default.
So if you set, in the UI, “Sheet selection” to “Select sheet with name”, once you change the file path it comes back to “Select first sheet with data”.

Agree, that could be improved. We’ll look into it. (It was actually the same behavior for the old node, the sheet selection was reset when the file was changed.)

-Simon

[ddamasceno]

@SimonS

@ddamasceno

I’ve tried your workaround but it doesn’t really help.

Why does it not help?

Sorry, it actually does the job but only once.
Once the file path has been used, there is no way of changing anything without changing the path again and that’s, in my use case, not a viable solution.

@SimonS

@ddamasceno

When the excel file path changes, some UI settings are reset to default.
So if you set, in the UI, “Sheet selection” to “Select sheet with name”, once you change the file path it comes back to “Select first sheet with data”.

Agree, that could be improved. We’ll look into it. (It was actually the same behavior for the old node, the sheet selection was reset when the file was changed.)

I would not say that it is exactly the same :
In the old node, you just have a reset of the drop-down menu, but here, in the new node, you have the “sheet_selection” variable that changes from “NAME” to “FIRST”, so you are loosing your sheet choice policy.

Best and thanks for helping !

D.

[SimonS]

@ddamasceno

Sorry, it actually does the job but only once.
Once the file path has been used, there is no way of changing anything without changing the path again and that’s, in my use case, not a viable solution.

Once the node is configured (with a “false” path), it should work, doesn’t it? If you need to re-configure the node, you would have to set the variable path controlling the node to a “false” one first, that’s true and for sure annoying. I just can’t think of another workaround for now.

@ddamasceno

I would not say that it is exactly the same :
In the old node, you just have a reset of the drop-down menu, but here, in the new node, you have the “sheet_selection” variable that changes from “NAME” to “FIRST”, so you are loosing your sheet choice policy.

Totally agree with that one. Are you controlling this settings via flow variables? If yes, what is the use case? (Did you actually look into the xml file to get the strings that are used to control this setting? )

[SimonS]

Hi @ddamasceno,

just writing to let you know that we have added a option to the Excel Reader in v4.3.1 which can be found in the Advanced Settings tab and is called “Support changing file schemas”. If you check this option, the issue you have encountered is fixed as the data spec will be re-calculated in every execution. So the workaround is not required anymore.

Best,
Simon

[ddamasceno]

Hi Simon,

Great ! I’ll check this asap.
Thanks for the follow-up !

Dimitri

[mvaldes]

Hi all - I’m experiencing something similar. I tried turning on this setting in Advanced Settings, however, my flow variables still aren’t an option as input. Any suggestions?

[FabianK]

@SimonS
I have the same problem with several nodes. The changing file schema should be activated by standard. I have to guess what the right schema is for every node

For example: The “Create Folder” node does not work with a custom created url, the checkbox is greyed out.

[Mark_Ortmann]

@FabianK,

regarding your Create Folder problem. Is this a knime url your trying to work with? In general it is not possible to create folders for “URL file systems”.
If you’re still using the knime protocol may I ask why you’re doing so (did we miss some nodes to make your use-case work)?

[FabianK]

@Mark_Ortmann

I changed the concept of the WF a little bit and this solved the problem. But thanks for the quick reply!

But now I have another problem with the new file handling which is a little bit off topic:
Because several nodes do not support the new file handling right now (like sdf writer etc) it would be great to have a converter node from the new “path” type (local file system, mountpoint etc) to string. The “Path to String” node only redefines the cell type but for example a conversion from a mountpoint path to string will result in an incomplete URL (the mountpoint reference is missing in the string). If I want to save a file to this path I of course get an error.

[SimonS]

Hi @FabianK ,

what exactly do you mean with “the mountpoint reference is missing in the string”? If you are having a Path using “Relative to current mountpoint”, you should get a string starting with “knime://knime.mountpoint/…”. So it will also be relative to the current mountpoint. If you have “Mountpoint” selected as file system, you get a string like “knime://your_mountpoint/…” which always points to exactly the same mountpoint (here a mountpoint with the name “your_mountpoint”).

Best,
Simon

[mvaldes]

@SimonS @Mark_Ortmann Hi - Here’s another screenshot of my flow variables one node before the excel reader. I didn’t change anything except upgrade to R4.3. After the upgrade, my flow variables stopped working. I tried turning on that setting in advanced settings, however, that didn’t resolve my issue. Any suggestions on why I don’t even have the option of selecting a variable as shown in my previous screenshot? Normally I could at the very least select ‘knimespace’.

[SimonS]

Hi @mvaldes,

this has nothing to do with the settings in the Advanced tab. We introduced a new variable type for paths for the new reader nodes, so you would need to convert your string variables to such path variables using the String to Path (Variable) node, see also this post.

Where do you have these variables actually from? The variable names let me assume you are using the legacy File Upload node. If so, you can exchange it with the File Upload Widget node which outputs a path flow variable.

Best,
Simon

[mvaldes]

@SimonS Thanks for your quick response! I integrated my workflow with the webportal so I get the file path from there (screenshot is updated since I added in the new upload widget feeding into the excel reader). Is the new widget webportal compatible?

Also is the new Create Temp Folder the same as the legacy Create Temp Dir node? Do I need to call out a folder location if I’m using the webportal? I see that’s new/different than the temp dir node.

[SimonS]

Hi @mvaldes ,

yes, the File Upload Widget node is compatible with the new file handling. It outputs a path variable that can be used to configure the Excel node.

@mvaldes

Also is the new Create Temp Folder the same as the legacy Create Temp Dir node? Do I need to call out a folder location if I’m using the webportal? I see that’s new/different than the temp dir node.

Yes, that’s right. In your screenshot, the node is configured to create a temp directory within your workflow that will be deleted when you reset the node or close the workflow.

Best,
Simon