Importing Excel documents into Microsoft SQL Server was a simple task before the world of x64.
Surprisingly, there are no drivers available (such as Jet) to directly interact with Excel.
To deal with this, I initially built an application that would read an Excel document and input the data into a SQL database.
To read details of the initial program, please see article here.
The limitation of this was that it only dealt with Excel 97-2003 documents. The latest version attempts to deal with this.
The reason for creating a separate article was that I modified the code base to such an extent that it is practically a different application.
Previously, I used the JET to import the XLS files into SQL. The driver for 2010 is available but you need to uninstall the 32 bit driver for the Office product before the 64bit will install. This is very risky when the product is in production and only the 32bit application is available.
The reason for not using OleDB is that the data that to be imported is created by a user maybe four times a month. As the process for import is included in a number of jobs, it was simpler to create a process that reads and imports the data based on a defined structure, rather than rely on a user to set up the Named Objects to link to the table.
Due to policies at work, Microsoft Office was not installed on the SQL server, so I required an Excel reader that did not need the Office Interop DLLs. I found the Excel Reader class created by Liu-Junfeng easy to use and implement.
Unfortunately, it does not yet read Excel 2007 documents. However, thanks to the ExcelPackage class created by John Tunnicliffe I was able to extend the support of the application.
The Way It Works
ExcelDB class opens an Excel document and reads the rows specified into a
The first task was to decide how to define the structure from an unknown Excel Spreadsheet.
I decided that the best way to accomplish this was to base it on an already structured
DataTable, such as one created in a SQL 2005/2008 database.
The code then reads the Excel Spreadsheet into the
DataTable, and writes the information back to the table it used to create the initial
ExcelDB class is the wrapper that will handle the reading of the Excel document and the input into the SQL Server as required.
The constructor is responsible for checking the Excel file for the correct version by using the Workbook File Extension and then instantiating the appropriate
string Extension = System.IO.Path.GetExtension(FileName).ToLower();
if (Extension == ".xls")
ExcelHandler = new Excel97to2003Handler
(FileName, ConnInfo, TrunctTBL, X_StartRow, X_EndRow);
else if (Extension == ".xlsx")
ExcelHandler = new Excel2007UpHandler
(FileName, ConnInfo, TrunctTBL, X_StartRow, X_EndRow);
("This version ExcelToDB only Supports .xls and .xlsx files"));
ExcelDB class only then has to have a reference to an
ExcelBaseHandler instance to run both (or future) versions of the Excel Workbooks.
Stringrepresentation of the extensions that are supported by the
ExcelDB class. It is returned as
"*.xxx;*.xxy". This is so that it can be directly used within a Dialog filter.
ExceptionEvnt: The event that fires if there is an Error Message
MessageEvnt: The event that fires when any messages are sent
ReadExcelIntoDatabase(int SheetNumber): The procedure resposible for utilizing the instance of the
ExcelBaseHandler class for transferring the data from the Excel Workbook into the SQL Server database
ExcelBaseHandler class is an implimentation of the
IExcel Interface and as such has an overridable procedure
This procedure is resposible for getting the information from the Excel Spreadsheet and turning it into the
In order to simplify this process, I decided on two
Handlerclasses that inherit from
ExcelBaseHandler and in turn override the
FileName: The Excel File path
TrnctTBL: True or False indication as to deleting all previous information from the SQL Table. The code will not ask for confirmation if True as this was intended for running as a batch process. Please use with caution.
X_StartRow: The Start row of the Excel Spreadsheet to begin reading from. This number must be less than the
X_EndRow. The number is
-1 for default application handling.
X_EndRow: The End row of the Excel Spreadsheet to finish reading from. This number must be greater than that of the
X_StartRow. The number is
-1 to indicate that the application should read to the last available row.
Using the Code
Before using the DLL files, you may be required to download the
OpenXMLSDK and add the
WindowsBase references to the project:
SvrDbInfo dbInfo = new SvrDbInfo("(local)\SQL", "Database", "ExcelTable");
ConnectionString ConnInfo = new ConnectionString(dbInfo, 30);
bool TruncateTable = false;
bool ContainsHeader = false;
int SheetNumberIndex = 0;
String ExcelFile = "C:\\MyFile.xlsx";
ExcelDB ExcelToDatabasecls = new ExcelDB
(ExcelFile, ConnInfo, TruncateTable, ContainsHeader);
private void ExcelToDatabasecls_ExceptionEvnt(Exception Error)
private void ExcelToDatabasecls_MessageEvnt(string Message)
SvrDbInfo class stores the information about the SQL Server name, the Database Name, and the Table Name from which the structure of the Excel Spreadsheet will be received and in turn written to.
ConnectionString class stores all the information regarding the SQL Server and the access.
This includes details such as truncating the table, SQL or Windows authentication etc. It is instantiated through the use of a
All exceptions are handled within the
In an attempt to make the application easier to use, I developed an XML utility that will create a settings file that can be passed to the
ExcelToDatabaseconsole application with the switch /XMLFile:<File>.
- 5th October 2010: 1st submission