Monthly Archives: May 2006

Macro to export data from Excel to mySQL

I have been struggeling many times to do initial filling of databases for projects – especially if the data is a deliverable of a customer and the database structure is already fixed. So I built a VBA macro that generates SQL statements for creation of tables and data inserts from a given XLS-file. The idea is to build a ‘template’ of tables (=worksheets with fieldnames in the column headings) which have to be filled with data by the customer. As soon as this file is returned you execute the macro and voilà you have generated SQL statements which you can easily be feed into phpMyAdmin.

You can understand it as follows:

  • The XLS-file represents one database with structural information and content.
  • Worksheets represent the tables (their name being the tablename). This results in a CREATE TABLE statement.
  • Headings (the first line, their name being the fieldname) of each worksheet for as many consecutive cells as you need represent the fields of that table. This results in the fields created in the CREATE TABLE statements.
  • Lines from line 2 on downwards until the first empty line is met will be converted to INSERT statements, INSERTS INTO the fields of each cell’s column.

Puzzled? Check out the examples, they should be self explanatory:

VBA macro source

Excel file containing example ‘tables’/worksheets to be exported to SQL and the macro

Resulting SQL saved to C:exported.sql

The VBA snippet has to be added as a ‘module’ in the Excel VBA editor in the XLS-file holding your structure and data and can be saved with it as a macro. Triggering the macro will then export your SQL statements. You can see this from the example XLS-file above.