I am trying to upload a series of csv files to a mySQL database, where each csv file will be stored in separate tables in the database. So far I have developed a simple loop using "List files", "TableRow To Variable Loop Start", "CSV Reader", "URL to File Path (Variable)" (to generate SQL table name from csv file name), "Database Writer", and "Variable Loop End".
As it is a loop designed to process many files, I want to rely on the automatic SQL Types functionality in Database Writer to create SQL table columns of the appropriate type, to match the KNIME column types.
This works fine, except when the same column name appears in more than one file, with different types. For example:
- column 'MYDATA1' in file1.csv contains integer values
- column 'MYDATA1' in file2.csv contains string values.
In this case, Database Writer processes file1 correctly, but fails on file2.
WARN DatabaseWriterConnection Error while adding row #1 (R0), reason: Incorrect integer value: 'thisIsAStringValue' for column 'MYDATA1' at row 1
Although the KNIME column type for 'MYDATA1' in file2 is correctly assigned as String, the Database Writer node is creating an integer SQL column. It appears to be "remembering" the SQL Type for column 'MYDATA1' from the previous iteration on file1.
Is there a way to force Database Writer to flush the SQL Types and regenerate correctly each iteration? Or is this a bug maybe?