Importing excel data in mysql database [duplicate]

Question

I have a excel file with about 5000 rows to be insersted at one of my mysql databse table, can anyone give quick and dirty solution ? Thanks

Answer 1

Quick and dirty:

Put a calculated column in Excel to generate a "insert" statement. Then COPY+PASTE all sentences into MySQL command interpreter.

Your cell should have a formula like this:

=CONCATENATE("insert into your table (col1, col2) values (", A1, ", ", B1, ");")

Then copy the formula on all the rows and you'll have your script.

Other quick & dirty:

Open your Excel file with ACCESS, then use "Export to ODBC" function to put your data in MySQL. It's a little bit more complicated since you will have to setup your ODBC driver and Connection but if you plan to do this regularly, it might be a better choice.

Answer 2

Save as CSV; use the "Import text file" feature of a graphical mySQL client like HeidiSQL to import.