Best way to export excel table into MySQL db

Question

I'm trying to export an Excel table which contains about 400 records of HR stuff (e.g. name, surname, age, job, ecc.) into a MySQL db. I'm actually using mysql for excel and started reading a db book, but I noticed many format issues such datetimes from trying to convert from %d/%m/%Y to %d-%m-%Y.

Best practice hints for db administration are well accepted!

• jkavalik: If you know Excel better than SQL, you can create insert statements with everything needed directly in Excel by string concatenation and formatting. It is not very elegant solution but you have direct control, Excel lets you copy the "formula" for that statement anywhere and it is quite fast.
• Md Haidar Ali Khan: @bomba's, can you check again your question. What exactly you want. Are you want import excel sheet table into mysql or want to export mysql table into excel sheet.
• bomba: is my english faulty? Please suggest me a better title. I want to put excel data into a MySQL db.
• bomba: @jkavalik, I want to stop using excel because my colleagues are too stupid to understand that renaming/moving an excel file causes to lose all the references and also throw away lots of working hours of mine trying every time to fix this stuff (and the IT stuff lets things unregulated).
• jkavalik: @bomba but that can't stop you use excel to do the export the way you need - just create INSERT statements from the rows you have

Answer 1

For 1-time:

Save as text (csv) file.

CREATE TABLE ... with the columns approximating the Excel columns.

LOAD DATA LOCAL FILE ... into that table in MySQL.

(If you need 'automation', there may be better ways.)

• bomba: could you please show me some of these better ways?
• Rick James: There are probably APIs for Perl, PHP, etc, that let you write code to fetch/store data to/from Excel/MySQL.
• panofish: There are several good posts on stackoverflow like this one: stackoverflow.com/a/25578291/259538

Answer 2

As @Rick James has already said

CREATE TABLE ... with the columns approximating the Excel columns.

LOAD DATA LOCAL FILE ... into that table in MySQL.

I am just goin to explore a bit more. I am writting the steps by steps the procedure based on (SQLyog,MySQL Workbench) tool . How you shall import excel file or CSV file into Mysql DB.

1) First create the HR Stuff table in your database through your MySQL tool (like SQLyog, MySQL Workbench).(For Example, I have taken here 5 fields)

enter image description here

2) Create same date field appropriate column name in excel sheet.

enter image description here NB: Here i am using SQLyog tool to import excel data in MySQL DB.

3)Right Click on table name -> Import->Import CSV Data Using LOAD LOCAL

enter image description here

4)Click your select button to choose your excel file 

enter image description here

Then click over import button and ok button.

To show the HRStuff database details in MySQL DB .

Show Databases;
Use 'YouDatabaseName'; (Where HRStuff is present)
Desc hrstuff;

After that you shall all details of your hrstuff table details. Hope it will help out .