I am reading excel files from SharePoint, but some time the excel file may add some useless column for my flow (just useless for me but useful for someone else)
I want to use the “Transformation” tab to determine the column type, and try to unselect the “any unknown new column” to avoid the error. But it doesn’t work. I understand I can select the “Use new schema” option, but if I select it, I cannot configure the column type in “Excel Reader”.
Hi @cuimaple , you will almost certainly need to use the new schema option for it to be able to read a changing file structure, and then you’d have to handle the fixing of column types in subsequent nodes.
How much setting of type information are you needing to do? Is excel misinterpreting the data?
the most annoying part is: when I use the “Transformation” tab, I can easily transform Date to Date&Time, even the input is like “2025-08-15”. But if I do it in other nodes, I need to determine if the original input is Date or Date&Time, or use complicated expression to realize it. 
Hi @cuimaple, I can understand the frustration. It would be good if KNIME had a dedicated manual transformation node that allows the user to specify the actual transformation required, in a way which is detached from a specific node such as the Excel Reader. The Column Auto Type Cast is ok if it gets it right, but as I’ve found in the past, the sample data isn’t always representative of the required datatype. Some nodes offer some translations, but they won’t work in all circumstances, and can be a pain if, as you have, the column may be of varying datatypes.
It’s one area where I feel that KNIME is lacking a little. It’s fine have an “auto type cast” node, but I would much prefer to be able to manually specify the required resultant datatype, in a consistent way instead of having a variety of inconsistent nodes or techniques for the different transformations.
I’ve built components in the past in an attempt to move in this direction for some datatypes. I wonder if you could share what happens in your particular case.
For example, do you have some spreadsheets with only dates, and others with only datetime, or does a single spreadsheet sometimes contain a mix in one column? And are you always wanting to generate a DateTime (i.e filling in the Dates with 00:00 ?)
I’m assuming that Excel is creating dates or datetimes depending on the data, and you then have to do fiddly transfomations.
Just having a play, and this may be of interest, to possibly cut complexity of a work-around. If after the Excel Reader you used Date&Time to String, using the format mask:
yyyy-MM-dd[ HH:mm[:ss]]
…this would create a String containing either a full datetime, or a date, depending on the original data.
If you then followed this with my Parse Date&Time component, configured as “Year-Month-Day” and set it to generate a “DateTime”, this should turn both dates and datetimes back into DateTimes again, retaining the original times if present, and appending 00:00 where they aren’t.
The Parse Date&Time component is available here:
I do also have an “experimental” transformation component that I have worked on over time for my own use that could possibly be adapted to also handle such a date/datetime conversion, if I have a better understanding of your use case.
1 Like
Wow, very impressive, let me try it
1 Like