Column Type Injection: Improve Feature / Add Option / Add Node

[mwiegand]

Hi,

Use Case / Circumstances
When a table is unpivoted or data is written to CSV, Excel, or any sort of type where the Knime data type is getting lost - i.e. the date time format, int vs. long or collection type - it can be failure prone or at least unnecessarily complex to restore the same type:

Column Auto Type Cast: Add option to remove incompliant values

Hi, I am working on a workflow to showcase various approaches to pivot without using a pivot node because the pivot node struggles to cope with large data set. Using the Test Data Generator, which also creates outdated data, I encountered a situation where a value could not be converted to the identified data type. ERROR Column Auto Type Cast 3:1564 Execute failed: Can't convert 'URI: http://rsvimcfngtmtgtc; EXT: ' to URI. In Row0 Column29. Disable quickscan and try again. Using quick sc…

Column Auto Type Cast: Fails to properly convert unknown data type but succeeds when processing string type

Hi, The Column Type Auto Cast seems to struggle converting to the actual data type if the ingested data is of unknown type. I happen to found this alongside these topics: I will upload a sample workflow in a minute. Edit: Here is the sample workflow [image] Best Mike

Furthermore, I occasionally experience a situation where the data type, using table writer and reader, gets lost too resulting in non-native type which is difficult to deal with as well.

Current Integration Status
The now deprecated Column Rename had an option to set the column type.

That option seems to have been passed to the Table Manipulator Node and I am attempting to leverage this to automate the process

The new Table Validator (Reference) has an option to “Try to convert (reject if not compatible)”

Though, that node is (yet) not compatible with complex types like collections as it throws:

Configure failed (IllegalArgumentException): Type cannot be converted, List (Collection of: String) only [Boolean, Double, Int, Long, String] are supported types.

Writing using a Table Writer and reading the data to reparse the data types doesn’t work properly especially when a non-native data type is contained within the data.

Suggestion

1. Table Validator (Reference)
   For the existing functionality in the Table Validator (Reference) I’d like to propose the add the remaining data types (which are many).
2. Table Reader
   This requires a better support to handle non-native data types to at least read them as strings because with non-native Knime kind of rejects to work (for apparent reasons). Ideally, though, non-native data types should be able to get converted as well. If an incompliant cell was found, it requires the option to convert it to missing / null or fail entirely.
3. Add Node “Inject Column Type” or “Inject Column Spec”
   Complementary to the Extract Table Spec, having an Inject Table Spec would be great. Though, it would require the actual column type to be added as a column as well (see topic 81812).

Best
Mike