Hi All,
while using the write excel node and configure i am not getting output converted into excel. not sure what is the issue, please help
Execution failed, unsupported format as Set, remove the same to execute.
this is the error i am getting, Please help
Please show configure screenshot, it can get solution easily.
Hello @ramnafin2005,
and welcome to KNIME Community!
KNIME column type Set is not supported (List also, maybe some others as well) while writing data to Excel. You can use Collection to String node from Vernalis KNIME Nodes Extension to convert these columns and then successfully include them in output.
Br,
Ivan
Good Day,
I have an Excel Workbook in which I’m attempting to consolidate data of the 30+ tabs into one sheet. I have been successful at the consolidation, however, I have been able to write the output file to Excel because I have several columns that are " Unsupported column type ‘?’". The Excel Writer is asking me to remove the corresponding column(s) but I need the data in these columns. Is there a way that I can convert the unsupported column types to string or some other column type that will allow me to write the output to Excel?
Hi @chimdee , can you show the table you are trying to write to Excel with? What are the current column types that Excel is complaining about? Basically, we need to see if the existing columns can be converted or not.
@bruno29a
Unfortunately, I cannot show the whole table as the data within it is highly sensitive but see below a screen show of the Column Filter node where I would filter out the columns that are labeled with a “?”. As you can see with the highlighted columns some of the columns are the same as other columns that were identified as a String. I can figure out why some columns were able to be identified as a String while others (on a different tab but included in the same excel workbook) were not.
Hi @chimdee , when Knime opens an Excel file, it tries to determine what the types of the columns are.
These columns might have “special characters” that is preventing Knime to determine the type of these columns.
You can play around in your Excel Reader when you are reading from the source. Open the configuration, and look at the Preview section in the Settings tab, and see what columns whose types are not set. You can try to force the Excel Reader to use a type - Right Click on the header of a column, click on Available Renderers, and see if you can force a type there, that is if these columns you highlighted came from your source.
If they were generated by your workflow, you should be able to enforce a type for your generated columns.
Alternatively, you can also change the column type at the end with Column Expression, right before your Excel Writer.
For example:
Input data:
I have 3 Int columns and 1 Date column. I convert them all to String:
And the result is all 4 columns converted to String:
Obviously, in some cases some conversion cannot happen, but most should be able to convert to String, unless you have some binary values in your data.
Here’s the test workflow: Change column type.knwf (6.3 KB)
I have been looking into the columns and the cell format types on each tab and yes, there are some tabs with varying column types. For instance, the column U might be a “custom” type on one tab, but a “text” type on the other tab. My problem I think is that in order to combine all the data in the excel file on one tab, I needed to apply the work flow below AND I needed to check the “Support changing file schema” in the Advanced Settings. When I checked this box in the Excel Reader node I lost my access to the Transformation tab. I am able to alter the Available Renderers in the Advanced Settings tab. Unfortunately, result is still unsupportable column types. I think I just have dirty data 
I fear I may have to clean the “bad apple” tabs individually and then bring them all together again.
Hi @chimdee , indeed it’s an issue if you are trying to combine all these sheets together, but they have different types for the same columns. You may need to manually consolidate these columns - either at the Excel source file itself, or via Knime individually. I’m not sure if you can “programmatically” consolidate them.
@bruno29a sad times
Thanks for brainstorming ideas with me though!
You are most welcome @chimdee , always a pleasure to help, or try to help 
I figured out a work around! The Column Rename node allows you to change the column type from “? -DataType” to “S - StringValue”. From here I can convert the unsupported column types to string value columns and then I can use the string manipulator nodes to convert the columns to dates, doubles, or integers if needed.
Hi @chimdee , yes Column Rename or Column Expression as I showed you can change the type. Column Expression is more powerful (in some cases Column Rename will not change the type properly while Column Expression will), so that’s what I usually use to change the type, while I use Column Rename only if I need to Rename.
I’m happy that Column Rename worked for you.
Hello @chimdee,
you can use Column Auto Type Cast node to convert multiple Non-Native columns to String columns. Additionally there is ticket to have option in Excel Reader to address these issues in such use cases. Stay tuned!
Br,
Ivan
Nice @ipazin , I was not aware of this node, thanks for sharing
Hello @bruno29a,
considering you started recently and self-taught you are aware of lot of things around KNIME and it’s nodes 
Br,
Ivan
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
