Handle number read in () brackets by Knime

Dear Team,

I have noticed that Knime read numbers as numbers if that’s how it being formatted in the excel file however if the numbers are formatted using some custom formatting it read the numbers in the following format.

if the number is positive 100.01 = 100
If the number is negative -100.01 = (100)

a) 1 problem is it get rids of the decimals
b) second problem is if I use string to number nodes it won’t transform the strings to number’s directly. I need to use string replacer or sting Manipulation node, but sometimes the numbers of columns are so much that I need to use the column list loop start, column regex, the loop end(column append), column filter and column reorder.

Is there any easy fix to this cause doing this every time in every data manipulation feels a bit cumbersome. May be I need to change some setting in my computer to read the numbers and that should fix this issue?

  1. Are you importing a csv file or an Excel file?
  2. Can you upload a copy of the data and your workflow here so that we can have a look?
1 Like

Excel files. Please find below the test data and new project which I created for reference with the test data

Test data.xlsx (9.5 KB)
KNIME_project.knwf (5 KB)

This is the output that I get when I import the excel file in Knime

Hi @Ankit_smart -

You need to make sure you are importing your column headers properly - otherwise KNIME sets all your column types to String, which is the cause of your problem.

Check the box “Table contains column names in row number:” and refresh your preview. It should work OK then.



although @ScottF solved it wanted to share info that with new, 4.2.0, KNIME version there is String Manipulation (Multi Column) node which can replace “column list loop start, all nodes in between, the loop end(column append), all nodes need to sort output” when same string manipulation is needed on multiple columns :wink:




Thanks Scott, I got your point but I think my issue is more around when I combine lots of file together it has some rows of strings in it which changes the columns format to string from integer. For example, I have attached the test data.

Test data revised.xlsx (10.8 KB)

Thanks a ton. I am downloading it now.

I think my issue is more around when I combine lots of file together it has some rows of strings in it which changes the columns format to string from integer.

Is there a good reason why you’re combining files in Excel with column headers in between batches of data? This really just makes life difficult, and I’d recommend leaving the files separate and concatenating them in KNIME.

Otherwise you’ll first need to filter out the column header rows:

then tell KNIME to replace the “)” with nothing, and to replace the “(” with “-” :

Then convert the strings to integer with the String to Number node:


The reason I am combining the file in Knime is because I have around 30 odd files that I need to combine the data from. I think there is some problem with my data. My test data appears to be working correctly but my actual data is giving me this problem and I can’t share my actual data for obvious reason. I will look into more detail to see this is happening. Thanks for your help on this.

This is a perfect example of a task that KNIME is built to do.


This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.