Force Column Types (or Set column Types)

Hello Knimers,

I have a situation here, I need to read more than 50 excel files, all of them are equal in terms of columns, sheet names, and I’m able to read them via looping, however sometimes (not always) happens that for example one column is recognized by the Excel reader as String and some others as Number, ergo at the end of the loop the column type changes to the “nominal type”, I have tried several options and the one that helps me to allow all the excel reading and looping without errors is:

*NOT enforcing types in excel reader and allowing the loop to change table specs and formats, that way all excel files are read. (but sometimes have the nominal result in few columns) what I do to deal with this failure is setting a “rename node” to just change the nominal to string but seems that between executions the rename “forgets” to change the type and it fails again, so I go to the rename and just switch the type

  • If I use the enforce types sometimes the reader fails, as seems that the node is not capable to force to one specific type.

I’m wondering if there is any way that after the looping, I can somehow set to specific columns to be “string” and later on I will deal with changing formats to what I need.

Thanks in advance for your support!

Hi @GQRanalytics , is there any reason you are reading 1 Excel file at a time? The Excel Reader can open all the files at once with the “Files in folder” option.

If you read all the files, then you will get 1 dataset, and you can configure the column types for the entire dataset.

The reason why it changes during some iteration is probably because of the data. Knime tries to guess the column types when it first reads a file based on the data in the column. For example, if 1 file has strings and numbers in Column A, the column will be “detected” as a String column, but if in another file that column has numbers only, it will be “detected” as Integer.

Since you cannot manually modify/enforce the column types during the iterations, you could look into reading all the files at once at one dataset, and then you will be able to modify the column types for the whole dataset.

1 Like

Well, the main reason its because I didnt know that can be done!! I’ve been kniming arround more than 5 years and never heard about this option, I will try this and get back to mark the solution if it works!!

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