Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I wanted to import CSV files into MySQL database using dynamic schema, as I will have to deal with large amount of files.
However, there are errors when the column length is more than 200 or when the column type is text.
Is there a way to fixed or modified this? while using dynamic schema
I can import data from other database (MSSQL -> MySQL, however when I change the source to CSV it seems not work in the same way)
Here is the job design
Error Message : [Table: tbl_inq] column type text
Exception in component tDBOutput_1 (Import_CSV) java.sql.BatchUpdateException: Data truncation: Data too long for column 'biko' at row 14
Error Message: [Table: tbl_mem] column length 1000
Exception in component tDBOutput_1 (Import_CSV) java.sql.BatchUpdateException: Data truncation: Data too long for column 'comment' at row 14
Here's each table schema
Here I tried to increase column length to 500.
However, the overall row sized become too large and the following error occurs.
JavaRow's Code
Dynamic dyn = row1.dynamic_column; for(int i = 0; i < dyn.getColumnCount(); i++){ DynamicMetadata meta = dyn.getColumnMetadata(i); meta.setLength(500); } row3.dynamic_column = dyn;
I think I know the problem now (but no idea about the solution yet...).
I'm using dynamic schema(column), so each column's values (from Raw CSV) are joined together into a row called 'dynamic_column' below.
'Dynamic_column'(row)'s will work if the total number of characters per row is less than 250 characters.
If the total number of characters is more than 250 characters, 'row size is too large' error will occurred.
Raw CSV
Dynamic Row (will work if the total character length is less than 250)
ADA00 - ADAAA - 株式会社○○○○(○○○○) - ○○ ○○ - 104-00○○ - 東京都○○○○ - ○○○○ - 03-○○○○-○○○○ - 03-○○○○-○○○○- info@○○○○ - 月~金曜 09:30~18:00<br>土曜 09:30~15:00、日曜・祝日休み - 1969/12/31 15:00 - 1969/12/31 15:00 - 34200 - 34200 - 弊社は○○○○のタイ旅行専門店です。○○○○○○○○○○○○○○○○○○○○"の日本販売総代理店でもあります。
However, the total row size limit is 65,535 bytes, so the row's total character limit should be working for 250 or more characters as well.
MySQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.
You have to change some columns to TEXT or BLOBs
In this case, it will likely be impossible to import CSV files with more 30 columns, which I think is not realistic in real-life case.
Is there a way to import CSV files with large number of columns(large row size)??