Click here to Skip to main content
Click here to Skip to main content

Tagged as

Excel to SQL without JET or OLE (Version 2)

, 5 Oct 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Import an Excel Workbook ".xls" or ."xlsx" into SQL without the use of JET or OLE

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

Excel_DB_class_diagram.JPG

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

  • 5th October 2010: 1st submission

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kelvin Armstrong

United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
GeneralNot Working SQL Server 2000 PinmemberDinendra5-May-11 3:23 
GeneralPiece of art! Like a quasar! PinmemberRedDK14-Oct-10 12:51 
GeneralRe: Piece of art! Like a quasar! PinmemberKelvin Armstrong14-Oct-10 23:48 
Generalmissing reference: DocumentFormat.OpenXML PinmemberRedDK14-Oct-10 12:06 
GeneralRe: missing reference: DocumentFormat.OpenXML PinmemberKelvin Armstrong14-Oct-10 12:13 
GeneralRe: missing reference: DocumentFormat.OpenXML PinmemberRedDK14-Oct-10 12:19 
GeneralRe: missing reference: DocumentFormat.OpenXML PinmemberRedDK14-Oct-10 12:26 
GeneralNot only... but also... PinmemberDaveRRR12-Oct-10 10:17 
Admittedly my needs have been more for writing Excel workbooks rather than reading them, but I have moved on from Excel Reader and ExcelPackage.
 
I prefer to use NPOI for Excel 2003 workbooks and the latest version of the OpenXml SDK for Excel 2007 workbooks. I have even unzipped an Excel 2007 workbook, used as a template, written the worksheet files and strings file (using StreamWriters) and rezipped it when I have needed decent performance on a large workbook.
 
Take a look at NPOI and OpenXml SDK and see what you think.
GeneralRe: Not only... but also... PinmemberKelvin Armstrong12-Oct-10 10:33 
GeneralDTS PinmemberScottM111-Oct-10 22:42 
GeneralRe: DTS PinmemberKelvin Armstrong12-Oct-10 4:08 
GeneralRe: DTS PinmemberScottM112-Oct-10 5:31 
GeneralRe: DTS PinmemberKelvin Armstrong12-Oct-10 10:30 
GeneralGreat Solution PinmemberRichardClark11-Oct-10 12:19 
GeneralRe: Great Solution PinmemberKelvin Armstrong12-Oct-10 0:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 5 Oct 2010
Article Copyright 2010 by Kelvin Armstrong
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid