Import datas into a table

Hallo is it possible to import datas from an Excel table into a mysql 8.0.35 table? Thanks Wolfgang

Yes, that's possible. You just need to save your Excel sheet to a .csv file. In HeidiSQL, go to Tools > "Import CSV file", select your .csv file and set the control characters (field terminator etc.) which Excel has put into that file.

Hi Ansgar, I didn't know HeidiSQL was so user-friendly. What switch do I need to set?

The message says it basically. There are two places where you need to have that "local infile" option enabled. On the client side, HeidiSQL already does it when you connect to your server. But on the server side the same option is likely still disabled. You can try two different things to solve that:

• set the server option in HeidiSQL before you import your file, through a query:

  SET GLOBAL local_infile=true;

• alternatively, if you have access to your MySQL server, you can activate the option permanently, by putting it in your my.cnf or my.ini file:

[mysqld]
local_infile=1

After saving the file, restart the MySQL server. This is a permanent solution, so it's probably the preferred one.

Hallo, I created one column with 10 rows "Row1........Row10" in an Excel file and then saved it as Test.csv (MS-DOS). I then tried to import this CSV file. I now get a new error message. What is my mistake here?

I guess Excel exports new lines as \r\n - not as \n.

But you need to open your textfile with an editor as you obviously set some wrong control characters. All of the 4 control characters are important for importing successfully.

Hello, I found my problem. After removing the quotation marks in the enclosed field, the import was completed. Thanks for your help. Wolfgang