OpenRefine/OpenRefine · error · SqlExporterException

is not compatible with column type

Error message

${value} is not compatible with column type :${type}

What it means

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.

Solutions

1. Fix or transform the offending cell values to valid numbers (e.g. use GREL toNumeric or remove separators) before exporting.
2. Change the column type in SQL export options to VARCHAR/TEXT so values are quoted.
3. 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.
4. Filter out rows with invalid numeric values before export.

Example fix

// before: cell '1,234' with column type NUMERIC -> throws
// after (GREL on the column)
value.replace(",", "").toNumber()

// before export
if ("NUMERIC".equals(colType) && value != null && !NumberUtils.isCreatable(value.toString())) {
    throw new IllegalArgumentException("Cell '" + value + "' not numeric for column " + colName);
}

boolean isNumeric(String s) {
    return s != null && org.apache.commons.lang3.math.NumberUtils.isCreatable(s.trim());
}

try { insertBuilder.getInsertSQL(); } catch (SqlExporterException e) { if (e.getMessage().contains("is not compatible with column type")) { logBadRowAndSkip(); } else { throw e; } }

When it happens

Trigger: 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.

Common situations: 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.

Understand the failure class

Background: "Invalid ... format", "must be in format X", "does not look like a ..." — invalid argument format errors across CLI tools and libraries — this error's family across 17 libraries.

Related errors

• is not compatible with column type
• ****NO COLUMNS SELECTED****
• ****NO OPTIONS PRESENT****
• Null value not allowed for Field
• The object type cannot be converted to a double

AI-assisted analysis of OpenRefine/OpenRefine@a946177e04 (2026-09-08). Data as JSON: /api/errors/dafe50d34c65e710. Report an issue: GitHub.

Appendix: source

Thrown at main/src/com/google/refine/exporters/sql/SqlInsertBuilder.java:137

                    } else {
                        rowValue.append("'" + val.getText().replace("'", "''") + "'");

                    }

                } else if (type.equals(SqlData.SQL_TYPE_INT) || type.equals(SqlData.SQL_TYPE_INTEGER)
                        || type.equals(SqlData.SQL_TYPE_NUMERIC)) {// Numeric Types : INT, NUMERIC

                    if ((val.getText() == null || val.getText().isEmpty())) {

                        handleNullField(allowNullChkBox, defaultValue, nullValueNull, val.getColumnName(), rowValue, false);

                    } else {// value not null

                        if (type.equals(SqlData.SQL_TYPE_NUMERIC)) {// test if number is numeric (decimal(p,s) number is
                            // valid)

                            if (!NumberUtils.isCreatable(val.getText())) {
                                throw new SqlExporterException(
                                        val.getText() + " is not compatible with column type :" + type);
                            }
                        } else {

                            try { // number should be an integer
                                Integer.parseInt(val.getText());
                            } catch (NumberFormatException nfe) {
                                throw new SqlExporterException(
                                        val.getText() + " is not compatible with column type :" + type);
                            }

                        }

                        rowValue.append(val.getText());

                    }

                } else if (type.equals(SqlData.SQL_TYPE_DATE) || type.equals(SqlData.SQL_TYPE_TIMESTAMP)) {

View on GitHub (pinned to a946177e04)