Export data from Excel 2007 to MSSQL Server 2005

Some time ago I was asked to export some data from Excel to MSSQL Server 2005. I spent some time looking for possible solutions to tackle this task. There are some ways, such as:

• using SQL Server Integration Services tool (which I don’t have)


• linking Excel as external database and then writing some strange SQL queries


• finally use Jakarta POI to read data and send it to MSSQL with ColdFusion

All of those look like one-time solution*. Simplest way would be just clicking a button in Ribbon but that isn’t implemented (at least it appears to be not implemented). So I’ve created this add-in called MSSQL Export Add-in for Excel 2007.

Here it is:

You can download installer and source code** from CodePlex. It requires following components installed first:

• .NET 3.5 framework


• Office 2007 Primary Interop Assemblies
  This component should be installed by default with Microsoft Office 2007 but if prompted on install search for “2007 Office Primary Interop Assemblies”.


• Visual Studio Tools for Office Runtime 3.0
  Small download which enables your computer to run Office add-in’s.

Once add-in is installed you can find it in Data tab.

* UPDATE *
If your add-in won’t start when installed try do following:

1. Set system environment variable VSTO_SUPPRESSDISPLAYALERTS with value 0


2. Restart Excel


3. Click Office button


4. Click Excel Options button


5. Go to Add-Ins section


6. Select COM Add-ins in Manage option and click Go… button


7. Tick MSSQLExport and press OK


8. Excel should ask for MSSQLExport Add-in installation permission


9. Click Install button

Add-in should work fine now.

When clicked it will bring new panel called MSSQL Export Add-in.

MSSQL Export Add-in can connect to more than one server at a time. But before I describe how to use it I will explain structure of the worsheet. Add-in assumes first row in active worksheet contain colummn names and rest of rows contain data that has to be exported. There should be no empty rows at the beginning and empty columns. Of course cells in data rows can be empty. You can see sample valid worksheet below.

For each connection it will show new tab with name of the server as tab label. First you will have to select database, then table. Once that is done add-in makes ti best to match worksheet column names with table column names.

If specific column should not be exported, simply select empty value from corresponding drop-down. As you can see on above screenshot there are few additional controls available

• refresh button: when active worksheet is changed it allows to run column auto-matching again


• use transaction tick box: when checked all database operation will run under SQL transaction


• truncate first tick box: when checked target table will be truncated first

That is all. When ready just click Export data button. You will be asked for confirmation and when you click Yes button data will be exported. Depending on amount of data export may take few seconds or few minutes.

Few words about licensing. Binary release and source code are available under MIT license. Application uses selected icons from iVista2 icon pack created by gakuseisean and are not included in source release but they are redistributed as compiled DLL assembly. If you want build application from source you will need add reference to it (DLL is included in source release).

There are probably some other methods I don’t know about, but it was good fun to build this one.
* I’m sure it isn’t the best C# code you’ve seen in your life.

Posted by Neil Middleton on 05 Aug 2008