String Manipulation

I have a situation where I’m using table names to name excel spreadsheet tabs and some of the names exceed the 31 character limit. I attempted to use the String Manipulation node. The hope was that I could pass the table names to it and remove the underscores. I’m finding that the flow variables are passing the actual expression [removeChars($${Stable}$$,“_”)] to sheet_names rather than the output of the expression. I’m not really sure what the value of that would be, but it seems this node would be markedly improved if it could evaluate the expression and pass that on to another node. The expression does write to a new column, but I only need the expression to evaluate and pass that as a variable to the Excel Writer node sheet_names variable.

Hi @kevinnay snd welcome to the KNIME community.

The String Manipulation node can definitely evaluate expressions so I’m not quite sure what you are doing to have it pass the expression instead.

To modify the value of a flow variable which you are using to name the sheet, you should probably be using the String Manipulation (Variable) node which is the variable equivalent of the String Manipulation node, and this can then be used in the Excel Writer.

If you are continuing to have difficulty, please paste a screenshot of the node configuration so we can help correct it for you.

2 Likes

Further to my previous post,just to clarify, in the above workflow screenshot that you posted, there is no way that the String Manipulation Node can be having any effect whatsoever on the Excel Writer, as it is incapable of modifying flow variables, and the output data port is unused.

So I am really struggling to match that screenshot with your observations

Anyway, the way you would want to do this is as follows, replacing the String Manipulation node as discussed:

As you want to ensure your sheet names are 31 characters or fewer in length, I would suggest additionally wrapping your removeChars function with a substr function to definitely enforce the 31 character limit.

substr(removeChars($${Stable}$$,"_"),0,31)

In the Excel Writer, you would then use the sheetName variable as the name of the output sheet, by specifying this on the Flow Variables tab of the Excel Writer node.

I hope that helps

4 Likes

That’s actually what I did which is why it was puzzling. I didn’t use the substr function because removing the underscores solved the string length issue. I still needed the table name to be used to select the table, so I passed it along the flow variables to the string manipulation node so that I could use it only be used to override the sheet_name value. I expected the expression to evaluate and then pass it along to the Excel Writer node, but what came out was a string value of the expression rather than than new string value and I saw now way to force that without bringing in another node.

I’m going to try the Variable version and hopefully that will solve the matter. Thanks.

FYI, that solved the issue. The non-variable version appears to really be for creating a new attribute, but that attribute doesn’t appear to pass along through the flow variables. The expression only passes the actual expression and not the evaluated expression. The variable version on the other hand, works exactly as expected using that same attribute value, so I just needed to discover the other version which you pointed me to. Thanks again.

Hi @kevinnay , basically the String Manipulation node is for manipulating column values in the data table. The String Manipulation (Variable) node is for modifying flow variables.

I’m still not sure why you are finding that in using one node it is passing along “expressions” rather than the results of the expression. I’ve not seen it do that, and the only way I can think of that would emulate that behaviour would be to enclose the entire expression in double quotes.

I’m glad that you have it working but if you need assistance with the String Manipulation node issue then feel free to post a screenshot of the config so we can help understand what is going on.

1 Like