Bug #95415 Table Data Import Wizard fails on UTF-8 encoded file with BOM
Submitted: 19 May 8:05 Modified: 27 May 10:08
Reporter: Georgi Sotirov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:8.0.16 OS:Microsoft Windows (Windows 10 Home)
Assigned to: CPU Architecture:x86

[19 May 8:05] Georgi Sotirov
Description:
In MySQL Workbench 8.0.16 the Table Data Import Wizard from the context menu of a table cannot parse a UTF-8 encoded file with BOM (Byte Order Mark). Error "Can't analyze file. Please, try to change encoding type. If that doesn't help, maybe the file is not: csv, or the file is empty.". There is no such problem if the file is UTF-8 encoded without BOM.

How to repeat:
1. Crete a CSV file like the attached cities-BOM.csv with a CSV editor like Microsoft Excel 365.
2. In a test database create a table like this:

CREATE TABLE cities (
  id    INT(4)      NOT NULL,
  name  VARCHAR(64) NOT NULL,

  PRIMARY KEY (id)
)
ENGINE = InnoDB;

3. Open Workbench, find the table in Navigator, right click and select Table Data Import Wizard.

4. Select the file cities-BOM.csv and click Next. On the next page click Next.
5. When the third wizard page opens you get the error and no columns are displayed.

If you repeat the same with the attached file cities.csv (i.e. without BOM) there is no such problem - the file is parsed properly, columns are detected and data could be imported successfully.

Suggested fix:
Table Data Import Wizard should not fail on CSV files encoded in UTF-8 with BOM.
[19 May 8:06] Georgi Sotirov
UTF-8 encoded CSV with BOM

Attachment: cities-BOM.csv (application/octet-stream, text), 551 bytes.

[19 May 8:07] Georgi Sotirov
UTF-8 encoded CSV without BOM

Attachment: cities.csv (application/octet-stream, text), 548 bytes.

[19 May 8:08] Georgi Sotirov
Wizard error

Attachment: wb_8016_data_import_error-utf8-bom.png (image/png, text), 23.65 KiB.

[20 May 5:57] Umesh Shastry
Hello Georgi Sotirov,

Thank you for the report and feedback.
Verified as described with WB 8.0.16 on Win10.

Thanks,
Umesh
[27 May 10:08] Georgi Sotirov
This seems to be an old problem. It was reported more than 4 years ago for Workbench (see bug 74914). Today, I also tried LOAD DATA INFILE and I confirm the same problem, which apparently also was reported long ago (see bugs 4960 and 10573).

mysql> LOAD DATA INFILE '/var/mysql/files/cities.csv' INTO TABLE cities FIELDS TERMINATED BY ';';
Query OK, 27 rows affected (0.05 sec)
Records: 27  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/var/mysql/files/cities-BOM.csv' INTO TABLE cities FIELDS TERMINATED BY ';';
ERROR 1366 (HY000): Incorrect integer value: '2700' for column 'id' at row 1
[5 Jun 12:12] Rami Amiry
I have the same issue after upgrading workbench to 8.0.16. 
Is there any fix or workaround for this issue?