I'm often asked to load or to update database tables from source data stored in spreadsheets
or comma separated values (csv) files. Often the amounts of data are relatively trivial,
or the task is a one-off, so it doesn't seem worth rolling out the big guns - Oracle's
SQL*Loader utility or Microsoft's Bulk Copy Program (bcp) used with SQL Server.
It's useful to have an easy-to-use alternative which you can operate with a little typing and
a few clicks from a Windows desktop.
This page provides an Excel
macro capable of automating the
process of generating SQL to load database tables from data stored in an Excel spreadsheet.
If you have a csv file you simply need to open it into Excel.
Basically, if you can write the SQL to process a single row of spreadsheet data, then this macro
can read your template SQL and use it to generate the SQL for the rest.
How it works
As a simple example, suppose we have a database table of employees with two columns. These contain
the employee number (empno) and the employee name (ename). We could write template SQL code like:
INSERT INTO emp (empno, ename) VALUES(%A%,'%B%');
where %A% and %B% are placeholders for the actual data values.
The macro would use this template to generate a series of INSERT statements to load the EMP table.
The template indicates that for each statement the macro should take the values for the
empno and ename columns from columns A and B, respectively, of an Excel worksheet.
One INSERT statement would be generated for each row of the source data.
Note that the values for the ename column would be embedded in single quotes,
exactly as shown by the '%B%' placeholder.
The macro is actually capable of handling data load scenarios which are much more complex
than that described above. The spreadsheet available here for download shows examples of:
loading multiple tables from each row of spreadsheet data
updating rows if they exist, and inserting them otherwise
generating scripts for data which must be loaded in multiple stages.
The spreadsheet comes preloaded with some sample data and template SQL which demonstrates these capabilities.
In addition to the main macro which generates the SQL, additional macros are provided to clear the
generated SQL, save it to the clipboard, and save it to file.
These are tied to buttons which are displayed over the generated SQL.
The macros presented here are written in Visual Basic for Applications (VBA) for use
with Excel 97 or later. The sample data uses Oracle's dialect of SQL, but because the macros
use whatevever template SQL is provided, the dialect used by Microsoft Access or Transact-SQL could
equally well be generated.
Important - a word about macro viruses
Excel macros can contain viruses.
For your own protection it's recommended that you enable macro virus security in Excel and set it to medium.
Excel will then display a warning message whenever you open a workbook that contains macros.
You can then decide whether to open the workbook with the macros enabled
or whether to open the workbook with the macros disabled so that you can only examine and edit them.
A macro virus can be harmful only if it is allowed to run,
so disabling the macros allows you to open the workbook safely.
The message appears regardless of whether the macro actually contains a virus.
The button below will download the spreadsheet complete with macros and open it in a separate browser window.
When given the choice, disable the macros. Examine the macros (use the Tools, Macro and Visual Basic Editor menu options)
and satisfy yourself that they do what they say.
Use the File and Save As menu options of your browser to save the spreadsheet.
You can preview the macro code in the listing below.