1

Importing CSV file, I have tried switching SQL mode to not use STRICT. Also tried '1', 1, true and b'1' values with no success. Always 1406 error. What should I change?

Table definition:

CREATE TABLE TB1
(
    ID INT UNSIGNED AUTO_INCREMENT NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    ENABLED BIT(1) NOT NULL,
    CONSTRAINT PK_FEATURES PRIMARY KEY (ID)
) ENGINE = InnoDB;

Enabled is the field I have issue with

I am using import withard icon when you put mouse pointer over table name, and then "import records from external file"

CSV file:

ID;NAME;ENABLED
244;Test;b'1'
ElSherpa
  • 11
  • 2
  • 1
    Please add table definition and sample csv file and explain what you mean by import - are you using an import wizard in workbench? – P.Salmon Dec 27 '20 at 11:52
  • I assume that the comma at the end of ENABLED BIT(1) NOT NULL, is a typo? Also an auto_increment colimn must be defined as the primary key.AND there is an empty row in the csv file after the title row. – P.Salmon Dec 27 '20 at 12:46
  • Yes you are right. They were typos – ElSherpa Dec 27 '20 at 13:31
  • This is the closest answer I could find: https://stackoverflow.com/questions/15683809/load-data-from-csv-inside-bit-field-in-mysql, together with the [docs](https://dev.mysql.com/doc/refman/8.0/en/load-data.html), it leads to `load data infile 'test.csv' into table tb1 FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (ID,NAME,@enabled) set enabled=CAST(CONV(MID(@enabled, 3, LENGTH(@enabled)-3), 2, 10) AS UNSIGNED);` – Luuk Dec 27 '20 at 14:27
  • Thank you Luuk. I have seen there is no way to use wizard for BIT type. I am using Workbench 8.0.22 – ElSherpa Dec 27 '20 at 18:18

0 Answers0