MySQL Bugs: #87788: Special characters in first field name when creating table using CSV file

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

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.

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.

sample csv

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

sample csv

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

sample csv

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

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.

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.

Hello Hassan,

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

Thanks,
Chiranjeevi.

Screenshot

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

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.

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.

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.

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.