3

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!

  • 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. – jkavalik Sep 9 '15 at 19:05
  • @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. – Md Haidar Ali Khan Sep 9 '15 at 19:13
  • is my english faulty? Please suggest me a better title. I want to put excel data into a MySQL db. – bomba Sep 16 '15 at 15:05
  • @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). – bomba Sep 16 '15 at 15:40
  • @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 – jkavalik Sep 16 '15 at 17:01
3

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.)

| improve this answer | |
  • could you please show me some of these better ways? – bomba Sep 16 '15 at 15:23
  • There are probably APIs for Perl, PHP, etc, that let you write code to fetch/store data to/from Excel/MySQL. – Rick James Sep 16 '15 at 17:51
  • There are several good posts on stackoverflow like this one: stackoverflow.com/a/25578291/259538 – panofish Nov 16 '15 at 22:36
1

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 .

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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