How to import an excel file in to a MySQL database

Fastest and simpliest way is to save XLS as ODS (open document spreasheet) and import it from PhpMyAdmin

Step 1 Create Your CSV file

Step 2 log in to your mysql server

     mysql -uroot -pyourpassword 

Step 3 load your csv file

     load data local infile '//home/my-sys/my-excel.csv' into table my_tables fields terminated by ',' enclosed by '"' (Country, Amount,Qty);

Another useful tool, and as a MySQL front-end replacement, is Toad for MySQL. Sadly, no longer supported by Quest, but a brilliant IDE for MySQL, with IMPORT and EXPORT wizards, catering for most file types.

If you are using Toad for MySQL steps to import a file is as follows:

1. create a table in MySQL with the same columns that of the file to be imported.
2. now the table is created, goto > Tools > Import > Import Wizard
3. now in the import wizard dialogue box, click Next.
4. click Add File, browse and select the file to be imported.
5. choose the correct dilimination.("," seperated for .csv file)
6. click Next, check if the mapping is done properly.
7. click Next, select the "A single existing table" radio button also select the table that to be mapped from the dropdown menu of Tables.
8. Click next and finish the process.

If you don't like plugins, VBA and external tools, I have an excel file that using formulas only allows you to create INSERT/UPDATES. You only have to put the data on the cells:

As an extra, there's another tab in the file to CREATE TABLES:

The file can be found on the following link:

EXCEL FILE

I've had good results with the Tools / Import CSV File feature in HeidiSQL, with CSV files directly exported from Excel 2019 with "Save As..."

It uses LOAD DATA INFILE internally but with a GUI interface and also analyzes the CSV file before passing it to LOAD DATA INFILE so it can, for example, create the table using the first row as column names and guessing the column data type (<New table> option as shown in the picture)