1

I created a simple .csv file with Excel which looks like this: enter image description here

I also created the same .csv file by using Libre Office.

Both .csv files are looking absolutely the same at TextEdit:

id, questions
1, Hello World

However, when I try to import them to the MySQL database at PhpMyAdmin then some something surprising is happening. The latter file is properly imported to the database. But when I try to import the former file to it then I get the following error:

Error
Static analysis:

5 errors were found during analysis.

A symbol name was expected! A reserved keyword can not be used as a column name without backquotes. (near "int" at position 46)
At least one column definition was expected. (near "int" at position 46)
Unexpected beginning of statement. (near "1" at position 50)
Unexpected beginning of statement. (near "`question`" at position 54)
Unrecognized statement type. (near "varchar" at position 65)
SQL query:

CREATE TABLE IF NOT EXISTS `Sample`.`TABLE 3` ( int(1), `question` varchar(5), `keywords` varchar(5), `answer` varchar(5)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'int(1), `question` varchar(5), `keywords` varchar(5), `answer` varchar(5)) DEFAU' at line 1

If I understand it right, this error occurs because the name of the first column in the .csv file is id which is a reserved word for the database. Nevertheless, this error does not occur at all in the case of the .csv file which is created in Libre Office.

Why is this happening?

  • 1
    You might need to compare your files with a hex editor to understand the difference. Text editors are not good to show non-printing characters. – Máté Juhász Apr 19 '18 at 13:28
1

The idea of @Máté Juhász regarding the hex editor was pretty good.

The .csv file from Excel displays the following content at the hex editor:

Ôªøid,name
1,hello

whereas the .csv file from Libre Office displays the following:

id,name
1,Hello

Obviously, I can use Libre Office for this task as I always did so far; I simply wanted two take advantage of useful functions in Excel.

However, finally, I figured out that I was getting this "bug" in the default .csv encoding of Excel which is CSV UTF-8 (Comma delimited) (.csv) but I am not getting it if I change to the Comma-Separated Values (.csv) encoding.

Lastly, let me note that I agree with @Pimp Juice IT that in the way the .csv file from Excel was encoded was like forgetting to set the name of the first column of the table at the database. But I wanted to find an explanation why is this happening at the level of Excel.

  • I see the SQL statement is generated as part of the process and not something you specify manually. Interesting.... – Pimp Juice IT Apr 19 '18 at 14:22
  • Yes, I simply imported the .csv files to the database. I did not write any query manually. But thanks for your idea. :) – Poete Maudit Apr 19 '18 at 14:33
  • The Byte order mark (BOM) often gets added to the beginning of Unicode files, and it shows up as three characters when viewed as UTF-8. Apparently, MySQL expects non-Unicode encoding. – Jim K Apr 20 '18 at 19:57
  • Yes, this is how it looks like @JimK. – Poete Maudit Apr 23 '18 at 7:56

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.