{"record":{"id":"dafe50d34c65e710","repo":"OpenRefine/OpenRefine","slug":"value-is-not-compatible-with-column-type-typ","errorCode":null,"errorMessage":"${value} is not compatible with column type :${type}","messagePattern":"(.+?) is not compatible with column type :(.+?)","errorType":"exception","errorClass":"SqlExporterException","httpStatus":null,"severity":"error","filePath":"main/src/com/google/refine/exporters/sql/SqlInsertBuilder.java","lineNumber":137,"sourceCode":"                    } else {\n                        rowValue.append(\"'\" + val.getText().replace(\"'\", \"''\") + \"'\");\n\n                    }\n\n                } else if (type.equals(SqlData.SQL_TYPE_INT) || type.equals(SqlData.SQL_TYPE_INTEGER)\n                        || type.equals(SqlData.SQL_TYPE_NUMERIC)) {// Numeric Types : INT, NUMERIC\n\n                    if ((val.getText() == null || val.getText().isEmpty())) {\n\n                        handleNullField(allowNullChkBox, defaultValue, nullValueNull, val.getColumnName(), rowValue, false);\n\n                    } else {// value not null\n\n                        if (type.equals(SqlData.SQL_TYPE_NUMERIC)) {// test if number is numeric (decimal(p,s) number is\n                            // valid)\n\n                            if (!NumberUtils.isCreatable(val.getText())) {\n                                throw new SqlExporterException(\n                                        val.getText() + \" is not compatible with column type :\" + type);\n                            }\n                        } else {\n\n                            try { // number should be an integer\n                                Integer.parseInt(val.getText());\n                            } catch (NumberFormatException nfe) {\n                                throw new SqlExporterException(\n                                        val.getText() + \" is not compatible with column type :\" + type);\n                            }\n\n                        }\n\n                        rowValue.append(val.getText());\n\n                    }\n\n                } else if (type.equals(SqlData.SQL_TYPE_DATE) || type.equals(SqlData.SQL_TYPE_TIMESTAMP)) {","sourceCodeStart":119,"sourceCodeEnd":155,"githubUrl":"https://github.com/OpenRefine/OpenRefine/blob/a946177e049f3b0644261661db36cbb0c81ccf8a/main/src/com/google/refine/exporters/sql/SqlInsertBuilder.java#L119-L155","documentation":"SqlInsertBuilder.getInsertSQL throws SqlExporterException when a non-null cell value belongs to a column whose type is NUMERIC but the cell text is not a creatable number (checked with commons-lang NumberUtils.isCreatable). Valid SQL for a numeric column requires a numeric literal, so non-numeric text aborts the INSERT generation.","triggerScenarios":"Exporting rows to SQL where a column's type mapping is 'NUMERIC/DECIMAL' and a cell contains text like 'N/A', '1,234', '12a', or an empty-but-non-null string.","commonSituations":"Mixed-type columns where the user manually set the type to numeric but some rows hold free text; locale-formatted numbers ('1.234,56'); cells containing thousands separators or currency symbols.","solutions":["Fix or transform the offending cell values to valid numbers (e.g. use GREL toNumeric or remove separators) before exporting.","Change the column type in SQL export options to VARCHAR/TEXT so values are quoted.","Use the exporter's null/error handling options (e.g. 'convert errors to null' / on-error null) so bad cells become NULL instead of failing.","Filter out rows with invalid numeric values before export."],"exampleFix":"// before: cell '1,234' with column type NUMERIC -> throws\n// after (GREL on the column)\nvalue.replace(\",\", \"\").toNumber()","handlingStrategy":"validation","validationCode":"// before export\nif (\"NUMERIC\".equals(colType) && value != null && !NumberUtils.isCreatable(value.toString())) {\n    throw new IllegalArgumentException(\"Cell '\" + value + \"' not numeric for column \" + colName);\n}","typeGuard":"boolean isNumeric(String s) {\n    return s != null && org.apache.commons.lang3.math.NumberUtils.isCreatable(s.trim());\n}","tryCatchPattern":"try { insertBuilder.getInsertSQL(); } catch (SqlExporterException e) { if (e.getMessage().contains(\"is not compatible with column type\")) { logBadRowAndSkip(); } else { throw e; } }","preventionTips":["Normalize numeric columns with GREL toNumeric before exporting.","Match column SQL types to actual cell content (use NUMERIC only when all values are numeric).","Enable convert-errors-to-null handling for mixed columns."],"tags":["sql-exporter","numeric-conversion","type-mismatch","java"],"backgroundTag":"invalid-argument-format","analyzedSha":"a946177e049f3b0644261661db36cbb0c81ccf8a","analyzedAt":"2026-09-08T10:21:27.735Z","contentChangedAt":"2026-09-08T10:21:27.735Z","schemaVersion":2},"datasetVersion":"2026-09-17T15:17:12.973Z"}