Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

What's the easiest way to export a table from either MS-Excel or OpenOffice calc and import it into a mysql database, where:

  1. The table will be imported as a table in Mysql
  2. The headers of the table will be the fields of the MySQL database
  3. There will be some smart analysis what data type is inside the cells, e.g. a Text field, an integer or a floating point value.
share|improve this question
up vote 5 down vote accepted

One of the tools I always have with me is 'Pentaho Kettle' which is a fantastic cross platform open source ETL. You can find the 'free' edition at

Once you have downloaded and installed the software, the steps are roughly as follows:

  1. Launch Pentaho Kettle
  2. (Typically) click on 'No Repository'
  3. Create a new transformation.
  4. On the left side you will see several 'blocks'. In this case, drag an 'Excel Input' block from the 'Input' category into your transformation file.
  5. Double click on the block you just dragged. The next steps refer to this block
    • Click 'Browse' and select the Excel file you want to import
    • Click on 'Add' (located to the left of the Browse button). You should see the file listed on the 'Selected Files' list underneath
    • Now go to the '!Sheets' tab (should be the second tab) and select the worksheet with the data you want to import into your database
    • Finally, go the '!Fields' tab (should be the last to the right), click on the button labeled 'Get fields from header row...' and change the different datatypes accordingly
    • Click on the button labeled 'Preview rows' to make sure everything is ok.
  6. Now, let's move this data to your SQL database. For simplicity, I'm going to describe how to create a new table on the database. However notice that Kettle provide blocks for handling updates and 'insert/update' tasks.
  7. Once again, drag a block from the left hand side. This time go to the 'Output' section and select 'Table output'.
  8. Connect the two blocks by holding the 'Shift' key and dragging the mouse pointer from the from the Excel to the Table Output block. The next steps are related to the Table Output block.
    • First, we need to build a connection to the database. To do this click on the button labeled 'New...' next to the 'Connection' dropdown box.
    • On the 'Connection Type' list, select 'MySQL' (as you can see Kettle allows you to connect with a vast number of different databases). Fill all of the appropriate fields and remember to click on 'Test' to ensure the communication is working.
    • Enter the name of the table
    • Go the tab labeled 'Database fields' and select 'Enter field mapping'. From here make sure you map all of the excel columns in the stream to the appropriate MySQL fields and click 'OK' (If the names in Excel match your table you can simply click on 'Guess' to get you close).
  9. Finally, Save your transformation and run it by selecting the 'Transformation>Run' menu. If everything goes well you should see a message that says 'Transformation Finished' on the execution box under the 'Logging' tab.

Good Luck!

share|improve this answer
Wow, the tool looked complicated to install and use, but in the end (thanks to your description) I managed to get it working! One side note: On the Table output step, I had to choose the tab "Database fields", press "Get fields" and then choose "SQL" and then "execute". This was the only way to get the structure automatically translated into a database table. Otherwise the transformation process complained that fields were not found in the table. Maybe you can improve your answer above with this comment? (I have no edit capabilities) – Roalt Nov 15 '10 at 11:02
Good Point! I forgot about that (that's what happens when you answer from memory ;). Typically what I do is click on 'Enter field mapping' and use the wizard to map my excel columns to the table (if they have the same name, clicking 'Guess' can save you a lot of time). I'll edit the answer to make sure it reflects your comments. – Edgar Nov 15 '10 at 16:16
Considering all the functionality that's in this program, I'll have to find more time to look at the other things you can do with it. It seems very powerful! – Roalt Nov 17 '10 at 21:09

You can try import external data from SQLyog. It is a simple GUI client.

share|improve this answer

Excel2MySQL is the easiest way to import Excel spreadsheet directly as a table into MySQL. To import OpenOffice calc, simply save as xls and then import. Full disclosure, I am the author of Excel2MySQL.

It meets all of your requirements:

  1. Each sheet is imported as a table in MySQL. In fact, the table is automatically created.
  2. The headers of the table can be used to define the fields of the MySQL Table.
  3. Excel2MySQL DOES smart analysis on column data type to automatically optimize the table field types (tinyint, smallint, mediumint, int, bigint, float, double, varchar, date, time, datetime)

And more:

  • Connects directly to your MySQL Server
  • Includes a command line interface, if you need to automate imports.
  • Append or replace existing table
  • Automatically detect and add new fields in existing table
  • Automatically explode Excel merged cells (not the same as unmerge).
  • Optionally exclude blank rows and blank columns
  • Optionally change spaces to underscores in field names (convenience function)


share|improve this answer
Since this is your product, could you provide a more detailed description of how it solves the OP's problem? Understandably, the creator of a product's opinion that the product is the "easiest and quickest solution available" isn't unbiased. – John Bensin Oct 10 '13 at 17:01
sorry about that .. full disclosure... I am the author of this utility, see above changes – panofish Oct 10 '13 at 17:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .