XLS Reader Treats Numeric Column as String ??

My XLS reader node is incorrectly interpreting a numeric (double) column as a string column.  I have done everything I can to find the cell with a non-numeric value in it.  I tried to search the Forum and did not find a relavant post.

Taking the string column and uisng string to number (double) node generates no errors (other than missing values for null cells) and converts the column to double.

Taking the string column, getting the length of the string, then Stipping the white spaces from front with String Manipulator and re-deterimining length gives the same values before and after stripping.

Trying to Rename the column with the Rename node, I do not have a choice of double, only String or the ? Nominal Value.

Does anyone have any ideas what I am missing here?  It obviously totally messes up the functioning of the pipeline.

Thanks

Mark

You are not pushing the column header into the first row of the table by accident are you. In xls reader use the setting for column header in 1st row.
Simon.

Nice thought but no - I have that box set and the Column header is there as a header.  It also works for another similar file just fine properly treating it as a numeric column - I expect it is a stray "hidden" character somewhere

I would assume a hidden character somewhere will turn the cell into a missing value when using the string to number node. That may narrow down its location.

simon.

Thanks for the idea - Nice try again but the missing value cells are the same before and after conversion.

The only option I can think of, is in excel, select the column, go to formatting and choose numerical format and resave the worksheet. Then reload into knime.

or copying the column contents to notepad, then copying back from notepad to excel to remove any zero length characters. Resave excel worksheet.

simon.

1 Like

Hi Andrew, 

Can you upload a file which demonstrates the problem?

Regards, Aaron