Cut and Paste to Excel (difficulties with decimal separator)

I want to copy cells via clipboard from KNIME to Excel. (I am aware of the Excel nodes for writing Excel sheets, but they don’t allow ad hoc data extraction like cut and paste) When copying numbers I have trouble with the setting of the decimal separator because I am using German language settings (which I can’t change) in Excel. Is there a way to change the language setting of a renderer to ease cut and paste to Excel?

Currently I am circumventing this problem by converting double numbers to String and replace the “.” to “,” using Sting Replacer. Is there an easier solution?

I not, the user experience could be improved by having

  • a “MS Office Cut&Paste” renderer which looks up the language setting of the Office installation for the different data types or
  • by putting data in an additional format into the clipboard (e.g. xlClipboardFormatBIFF12).

Hi,

You can use “String Manipulation” node to convert the numbers to string and change the “.” (dot character) to “,” (comma character) by applying this expression:
replaceChars(string($column1$), ".", ",")
Where “column1” is the column containing your numbers with “.” as decimal separator.

Best,
Armin

2 Likes

Thanks,

This reduces the number of nodes from two to one which simplifies the workflow. Sadly the “String Manipulation” node still only changes/adds one column (as the “String Replacer” node). Therefore for each column where the decimal separator shall be converted an additional node has to be added to the workflow. Am I missing something here?

Regards,
Andreas

I can suggest two solutions for the case:


1- If you have so many columns:
You can use “Column List Loop Start” to loop over the columns to which you want to apply the expression.
In that case you need to use the “currentColumnName” variable (which is produced by the “Column List Loop Start” node) in the expression and maybe as the column to be replaced.
But using this variable in expressions to act as the column is a bit tricky since the column names must be enclosed in "$"s (Dollar signs) like $column1$. To solve this issue, you can create the whole expression in a “String Manipulation (Variable)” node:
join("replaceChars(string($",$${ScurrentColumnName}$$, "$), \".\", \",\")")
And then assign the variable to the corresponding option (“expression”) in the “Flow Variable” tab of the configuration window of the main “String Manipulation” node. (And the “currentColumnName” variable to the “replaced_column” option)
Untitled

2- If you have a few columns:
You can also use a “Column Expressions” node and add new expressions for each column using the same expression customized for each column (by changing the column name in the expression).

Best,
Armin

5 Likes