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?
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
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.
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:
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.
