I am using Excel Reader (XLS) to read data table from excel file. I have to change the source file (excel file) whenever there is data changes and when I click re-run on the same project flow, sometimes it will fail. The reason being the saved project previously recognized one of the column with data type double, then with the new file, knime recognized it as an integer data type.
My question is, are there ways to specify or lock the data type when the excel reader reads the file? I would like to set the data type as double, so that the flow wont throw me an error whenever I re-run the whole flow with a new data source.
Appreciate any advice.
“Column Auto Type Cast” node may help in this case. Just put it after the the Excel Reader node and check if it produces the same type every time you read the updated file.
If that doesn’t work, you can use this trick to convert the column type to double:
Use “Number to String” node to convert the column type to string and right after that use “String to Number” node and set the type to “Number (double)” in configuration. I think this will work in all cases.
I am aware of the convert column type to double but in my case above, what I am looking for is the way to lock the data type of the columns that excel reader reads. So that whenever I re-run my flow with different data source, I will have an error free flow. For eg, my data set has 10 columns, 2 columns I am sure that it will yield an integer or double, so I would set this 2 columns’ data type to be double and another 8 columns I would set it fixed with string data type.
Do you have any recommendation/advice on this?
What you’re looking for is a feature in “File Reader” node which you can change the column type directly in node configuration by double-clicking on column headers and change the column settings. But in “Excel Reader” you cannot do this.
I think the best method for your case is using the 2 nodes I mentioned earlier (“Number to String” and “String to Number”). In “Number to String” node you can convert all the columns to string and then in “String to Number” node you can choose those 2 columns and change their type to double.
Is there any particular reason that you don’t want to use this method?
Thanks for the clarification on the excel reader column type configuration.
The excel reader will throw me this error whenever the default data types are not the same with the one I initially build the flow.
To prevent this error, simply check the “Disable Preview” in Excel Reader configuration.