Excel to SQL without JET or OLE (Version 2)

• Download ExcelToDB Eg. - 187.55 KB
• Download Excel To Database - 895.61 KB

Introduction

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.

Background

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.

Special Thanks

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

The ExcelDB class opens an Excel document and reads the rows specified into a DataTable.

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 DataTable.

ExcelToDB Class

The 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 ExcelBaseHandler class:

//97-2003 Excel File Handler
string Extension = System.IO.Path.GetExtension(FileName).ToLower();
if (Extension == ".xls")
{
    ExcelHandler = new Excel97to2003Handler
	(FileName, ConnInfo, TrunctTBL, X_StartRow, X_EndRow);
}
//2007+ Excel File Handler
else if (Extension == ".xlsx")
{
    ExcelHandler = new Excel2007UpHandler
	(FileName, ConnInfo, TrunctTBL, X_StartRow, X_EndRow);
}
//Any other file type
else
{
    WriteError(new NotSupportedException
	("This version ExcelToDB only Supports .xls and .xlsx files"));
    return;
}

The ExcelDB class only then has to have a reference to an ExcelBaseHandler instance to run both (or future) versions of the Excel Workbooks.

• SupportedFileTypes: A String representation 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

The ExcelBaseHandler class is an implimentation of the IExcel Interface and as such has an overridable procedure SpecificVersion_TableFromExcel.
This procedure is resposible for getting the information from the Excel Spreadsheet and turning it into the DataTable.

In order to simplify this process, I decided on two Handler classes that inherit from ExcelBaseHandler and in turn override the SpecificVersion_TableFromExcel.

• 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 OpenXML SDK and add the DocumentFormat.OpenXml and WindowsBase references to the project:

SvrDbInfo dbInfo = new SvrDbInfo("(local)\SQL", "Database", "ExcelTable");
ConnectionString ConnInfo = new ConnectionString(dbInfo, 30);

//Truncate the SQL Table prior to inputting the data?
bool TruncateTable = false;
//Does the Excel Worksheet have the 1st row as a Header?
bool ContainsHeader = false;

//The Sheet Number of the Workbook
int SheetNumberIndex = 0;

//String ExcelFile = "C:\\MyFile.xls";
String ExcelFile = "C:\\MyFile.xlsx";

ExcelDB ExcelToDatabasecls = new ExcelDB
	(ExcelFile, ConnInfo, TruncateTable, ContainsHeader);

//Assign the event listeners to each of the events
ExcelToDatabasecls.ExceptionEvnt += 
	new ExcelDB.ExceptionDelegate(ExcelToDatabasecls_ExceptionEvnt);
ExcelToDatabasecls.MessageEvnt += 
	new ExcelDB.MessageDelegate(ExcelToDatabasecls_MessageEvnt);
ExcelToDatabasecls.ReadExcelIntoDatabase(SheetNumberIndex);

private void ExcelToDatabasecls_ExceptionEvnt(Exception Error)
{
    Console.WriteLine(Error.Message);
}
private void ExcelToDatabasecls_MessageEvnt(string Message)
{
    Console.WriteLine(Message);
}

The 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.

The 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 SvrDbInfo instance.

All exceptions are handled within the ExcelBaseHandlerExcelDB events.

Additional Information

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 ExcelToDatabase console application with the switch /XMLFile:<File>.

History

• 5^th October 2010: 1^st submission