I have just started using KNIME platform and have gone through basics and nodes documentation/example.
In my data multiple columns have String data and Integer values. The string is static (“NA”) across columns. I want to convert these columns into Numeric/Integer. But due to presence of “NA” in few cells Column is not detected as Numeric.
I tried String Manipulation to replace, seem this needs to be set for each column. Is there any node which converts Cells with the word “NA” as empty/null?
Using the String to Number node should help you take care of this problem. Take your columns that have been detected as strings, due to the NA cells, and feed them to this node. The NA values will be converted to KNIME missing (?) values automatically when the type conversion happens.
What node(s) are you using to bring your data into KNIME? If you can post an example workflow and some toy data, we should be able to track down the problem.
EDIT: I suspect the error you mention is due to not specifying a decimal and/or thousands separator.
The test file was originally downloaded from Google sheets, when saved using Excel columns are detected as “String”, but still fails in the conversion.
Is there a way to Convert a column to String, when the column header is shows “?” symbol ?
Secondly, if the Conversion error is caused by Comma separator, how to address it?
EDIT: Before playing more with Google sheets as I suggest below, can you instead first try the Column Auto Type Cast node to force a column type onto the [?] unknowns? The Column Rename node may be useful as well, since you can also use it assign types.
OK, it seems like the main problem here is correct detection of column type when reading in Google sheets. So far I haven’t been able to recreate the problem - any Google sheets I create are importing and converting OK - but I am just using some simple examples.
Do you have a Google sheet you can share that exhibits this issue?
With respect to the warning message you’re seeing (Values in X cells could not be parsed…) - this can be safely ignored. The String to Number node is doing the correct conversion - in this case, from “No Data” to “?”.
This is I think a separate issue from the one I discussed above, where reading data from Google sheets causes some unknown column types that need to be dealt with.
“Column Auto Type Cast” node is able to convert the ? columns to String and further I am able to convert them into Integer using “String to Number” node.