Import Loop XLS Reader Data Types

Hey Guys!

I'm relatively new to knime and I'm trying to make a loop to import XLS-files from one folder. The xls-files are all based on the same "mastertable" (wich is also imported, with dummie-data) but they don't necessarily have all the columns of the mastertable. Within columns with the same name, you can only find data of one typ (e.g. integer) or missing data.

My Problem are the data types of the columns. When imported,  2 columns get types assigned automatically (one integer, one string) but the others just have a '?' as data type.

The node "column auto type cast" doesn't seem to help, because it doesn't change anything (Node seems to work, but input = output).

In the node "column rename" I can only change them to 'string', but not to integer or double. Also, I can only change the columns one by one, isn't there a node to detect the data type of a column?

The only way so far I found would be changing all the columns to string with the column rename node and then use the "string to number" node once for the columns with integer and once for double. But this seems quite complicated for such an easy step to do and all 3 the nodes need to be adjusted, if a new column is added.

I attached my workflow, maybe there is something wrong with the whole flow.

Thanks a lot for helping!

 

Here are the Warning messages in the console, maybe this helps solving it:

 

WARN: String to Number 2:308 Values in 2798 cells could not be parsed

WARN: String to Number 2:308 Problems occured, see Console messages

Hi Sabi,

if you are a beginner, this is by the way no beginner workflow!

I think you tried quite some things, so I will just ask some sanity questions... maybe we find something.

In the loop end node, did you activate the options to allow variable column type and table specifcations?

In the xls reader, did you read the columnnames?

In the String to number, did you adjust the 1000 and decimal separator? Same for the Column Auto Type Cast?

The Column Rename is not the correct node here, it is really mainly for renaming.

Best, Iris 

Hi Iris,

Thanks for the help :) I actually copied most of this  loop from the forum :)

In the loop end node, both options are allowed.

In the xls-reader I have a kind of "dummie-table" with all possible columns and dummie values. Here knime finds the right data types for each column, but not in the loop end node.

In the String to number, I did. In the Column Auto Type Cast I can't seem to find this option. Also I just found out, the Column Auto Type Cast Node only checks on columns which allready have a column type. Those with missing type (?) aren't considered.

I know the column rename is not a good solution, but it's the only node I found beeing able to change data types from missing (?) to anyting else.

I think one problem with my workflow might be, that the Tables I import are mostly empty. As I said, the columns aren't the same all the time. But in the final Table, there are values in each colum, but sometimes only about 10 values, while I import about 1 000 rows. In columns with no empty cells, a data type is found.