What is the easiest way to import an excel sheet into mysql

Question

I have an Excel sheet with some cols and i want to import those into the mysql table. The Problem is that there are more colums in the mysql table than in the sheet (which is absolutely fine). What would be the easiest way of getting the data in the right fields?

My solution would be export to csv and put it in mysql via php, but there has to be a way that is more simple.

Answer 1

mysqlimport cmdline-tool has support for importing csv-files, and IIRC supports mapping of different columns in csv into different columns in your table.

http://linux.die.net/man/1/mysqlimport

I realize that it's just a cmd-line wrapper to the LOAD DATA INFILE sql statement, which can be used instead.

If you need to reaorganize the data, you could just import the csv flat into an equivalent table, and from there, do insert ... select from

Answer 2

You can use mysqlimport to import CSV data.

Answer 3

Although the mysqlimport solution is absolutely feasible, it can be cumbersome (NULL handling e.g.) if you have to import a lot of files or if you have to import them regularly. I sometimes use Toad® for MySQL which is able to directly import XLS(X) files into MySQL. This surely is overkill if you only import some Excel data now and then - but it's an alternative and it can be automated as Toad supports automation workflows.