Bug #87788 Special characters in first field name when creating table using CSV file
Submitted: 18 Sep 2017 7:56 Modified: 19 Sep 2017 13:29
Reporter: Hassan Naqvi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:6.3.9 OS:Microsoft Windows (Microsoft Windows 7 Enterprise Service Pack 1)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[18 Sep 2017 7:56] Hassan Naqvi
Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\hassan.naqvi\AppData\Roaming\MySQL\Workbench\log\wb.log]

I got this error while importing data from CSV files. But that's not the bug rather its the solution to 'The Bug' mentioned below.

UnicodeDecodeError: 'ascii' codec can't decode byte 0xef in position 1: ordinal not in range(128)
ERROR: Import data file: 'ascii' codec can't decode byte 0xef in position 1: ordinal not in range(128)

SOLUTION TO ABOVE ERROR: 
- Alter table.
- Delete name of first field in the table.
- Re-typed the same name again applied the change.
- Done, when I imported the file no error!

The Bug:
When table is created using csv file '0xef' is added to the beginning of first field name. Now, when that field name is used in any query it shows and error 'unknown column name'.

How to repeat:
Create a new table by importing data from a (UTF-8) csv file.
[18 Sep 2017 12:32] Chiranjeevi Battula
Hello Hassan,

Thank you for the bug report.
I could not repeat the issue at our end using with MySQL workbench 6.3.9 versions.
Could you please provide repeatable test case (exact steps, sample CSV file, screenshot etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[19 Sep 2017 11:13] Hassan Naqvi
sample csv

Attachment: test1.csv (application/vnd.ms-excel, text), 100 bytes.

[19 Sep 2017 11:13] Hassan Naqvi
sample csv

Attachment: test2.csv (application/vnd.ms-excel, text), 102 bytes.

[19 Sep 2017 11:13] Hassan Naqvi
sample csv

Attachment: test3.csv (application/vnd.ms-excel, text), 105 bytes.

[19 Sep 2017 11:39] Hassan Naqvi
Steps to recreate:
=================

First File:
==========

- Right click on schema name and select Table Data Import Wizard

- Enter CSV file path (test1.csv) or select using Browse button. Press Next

- On the next screen, Select Create New Table. I didn't change given table name.

- Next, Next, Next, Finished.

Second File:
=========== 

- Right-clicked on newly created table's name and selected Table Data Import Wizard from popup menu.

- Enter CSV file path (test2.csv) or select using Browse button. Press Next

- On the next screen, Select Use existing table (test1). I didn't change given table name.

- Next, Next, Next, oh no! There is an error!!! (...Finished.)

Temporary Solution:
==================

- Right click table name (test1), select Alter table from popup menu. 

- Select first column in the table (id), select the entire content of form field column name (just above collation form field). IMPORTANT! Either double click to select entire content or use Select All from popup menu or use Ctrl+a when cursor is inside the column name form field. Selecting only 'i' and 'd' won't work.

- Delete and rewrite same field name again -- 'id' and Apply

- Repeat 'Second File' steps and there will be no error this time.
[19 Sep 2017 12:04] Hassan Naqvi
Before trying to import second file for the first time, try this...

Copy and paste both select statements in Workbench.

SELECT `id` FROM `test1`; <<< Error Code: 1054. Unknown column 'id' in 'field list'

SELECT `´╗┐id` FROM `test1`; <<< This works.
[19 Sep 2017 13:29] Chiranjeevi Battula
Hello Hassan,

Thank you for the feedback and testcase.
Verified this behavior on MySQL Workbench in 6.3.9 version.

Thanks,
Chiranjeevi.
[19 Sep 2017 13:30] Chiranjeevi Battula
Screenshot

Attachment: Bug_87788.JPG (image/jpeg, text), 81.19 KiB.

[20 Mar 22:36] Samsudeen Naina
I am using the community version 8.0 of mySQL Workbench.
This error is still showing up (after import of a csv file, the first field name has special unreadable characters.
Thanks.
[27 Mar 12:14] Tom Anderson
I also see this bug in 8.0.23.

To reproduce, open a database and right click "Tables". Select "Table Data Import Wizard". Choose a csv file that is saved in UTF-8 format.

The first column will not have the quotation marks stripped and there will be one of those weird character format conversion glitches at the beginning.

To avoid this error, I need to resave the csv file in ANSI format. 

This is unacceptable because it came from a UTF-8 source. This is 2021. Support UTF-8 for CSV imports!

Can't believe people marked this as S3 non-critical. It is fundamentally broken.
[27 Mar 12:20] Tom Anderson
The Import Data Page for the UTF-8 file "test-2.csv"

Attachment: Screenshot 2021-03-27 224721.png (image/png, text), 23.84 KiB.

[11 May 14:32] Jay Thaker
Before loading the .csv in MYSQL, re-save as your CSV file in comma delimiter(.csv) format only and not any other .csv format and then import the csv you data will load 100% without errors or any special characters. Hope this solves the problem.