CSV import error 1406 when loading boolean data into MySQL BIT column

[WhisperingWind]

I’m getting a data too long error when trying to import a CSV file into my MySQL database through Workbench. The problem happens with a BIT column that should store boolean values.

My table structure:

CREATE TABLE PRODUCTS (
    PRODUCT_ID INT UNSIGNED AUTO_INCREMENT NOT NULL,
    PRODUCT_NAME VARCHAR(255) NOT NULL,
    IS_ACTIVE BIT(1) NOT NULL,
    PRIMARY KEY (PRODUCT_ID)
) ENGINE = InnoDB;

Sample CSV data:

PRODUCT_ID;PRODUCT_NAME;IS_ACTIVE
156;Sample Product;b'1'

I’ve tried different formats for the boolean field like ‘true’, ‘1’, 1, and b’1’ but none work. I also switched off STRICT mode but still get error 1406. I’m using the table import wizard in Workbench (right click table > import records from external file). What format should I use for BIT columns in CSV imports?

[mikezhang]

I encountered a similar issue previously with CSV imports into MySQL. The key detail is to ensure that you’re using unquoted numeric values for the BIT column. In your CSV file, replace b’1’ with simply ‘1’ and ‘0’ for the active states. Additionally, verify that your delimiter aligns with what you’ve set in the import wizard, as mismatches here can also cause errors. If problems persist, switching the BIT column to TINYINT during the import process can be a useful workaround before reverting it back to BIT.