Excel to SQL without JET or OLE

• Download ExcelToDB ConsoleEg - 98.91 KB
• Download solution - 130.2 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 overcome this issue, I designed a class to assist with the automated import of Excel documents into SQL Server.

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 is 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, but it did everything I required for 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.

ExcelDB Class

You will use the ExcelDB Class to read the Excel Document into SQL:

• FileName: The location and Name of the Excel File to read
• TrnctTBL: Indicates to the ReadExcelIntoDatabase() procedure whether or not to delete all information from the table
• X_Header: Indicates if the first Row is a Header (will read from the 2^nd Row.).
• X_StartRow: The first row to read from within the Excel Document. This value over-rules the X_Header
• X_EndRow: The last row to read to within the Excel Document

• ExceptionEvnt: The event that fires if there is an Error Message
• MessageEvnt: The event that fires when any messages are sent

All Messages and Errors that occur within the ExcelDB Class are done through the Events.

ConnectionString Class

The ConnectionString class is used to Store the details for connecting to the SQL Server.

• D_Info: Gets or Sets the SvrDBInfo information pertaining to the database
• Password: Gets or Sets the password for the SQL Server username
• UserName: Gets or Sets the Username to connect to the SQL Database
• Timeout: Gets or Sets the Connection (and Command) Timeout value (in seconds)
• TrustedConnection: Gets the value if the connection uses windows authentication or not

SvrDBInfo Class

The SvrDBInfo is used to store the details for the SQL Server.

• SVRName: Gets or Sets the SQL Server name
• DBName: Gets or Sets the database name
• TBLName: Gets or Sets the table name

Using the Code

In your project, add a reference to the ExcelToDB.dll. In your code, import the namespace ExcelToDB.

using ExcelToDB;

Create a SvrDBInfo class instance with the Server, Database and Table information:

SvrDbInfo D_Info = new SvrDbInfo("(local)\SQL", "ADatabase", "MyTempTable");

Create a ConnectionString class instance using the D_Info instance of SvrDBInfo above:

//Creates a Windows Authenticated ConnectionString instance
ConnectionString ConnString = new ConnectionString(D_Info, 30);
//Creates a SQL Authenticated ConnectionString instance
//Using 'SA' for sake of argument.
ConnectionString ConnString = new ConnectionString(D_Info, 30, "SA", "SAPassword");

Now that those are created, we can create the ExcelToDB Class:

//This will create the Class instance with just the default values.
//That is 
//    1. Read all rows.
//    2. The first row is a Header, read from the 2nd
//    3. Delete previous information from the Table 
//       (if the ReadExcelIntoDatabase() is called)
ExcelDB ExcelToDBcls = new ExcelDB("C:\MyFile.xls", ConnString);

Once done, we add some message handlers to display the messages to the users

private void button1_click()(object sender, eventargs e)
{
    SvrDbInfo D_Info = new SvrDbInfo("(local)\SQL", "ADatabase", "MyTempTable");
    //Creates a Windows Authenticated ConnectionString instance
    ConnectionString ConnString = new ConnectionString(D_Info, 30);
    /Creates a SQL Authenticated ConnectionString instance
    //Using 'SA' for sake of argument.
    //Commented out so as to show construction for two different connections.
    //ConnectionString ConnString = new ConnectionString
    // (D_Info, 30, "SA", "SAPassword");
    
    //This will create the Class instance with just the default values.
    //That is 
    //    1. Read all rows.
    //    2. The first row is a Header, read from the 2nd
    //    3. Delete previous information from the Table 
    //       (if the ReadExcelIntoDatabase() is called)
    ExcelDB ExcelToDBcls = new ExcelDB("C:\MyFile.xls", ConnString);
    
    //Assign the event listeners to each of the events
    ExcelToDBcls.ExceptionEvnt += new ExcelDB.ExceptionDelegate
					(ExcelToDBcls_ExceptionEvnt);
    ExcelToDBcls.MessageEvnt += new ExcelDB.MessageDelegate(ExcelToDBcls_MessageEvnt);
    //Read the Excel Document, and write it to the Table.
    ExcelToDatabasecls.ReadExcelIntoDatabase();
}

/// <summary>
/// ExcelToDBcls instance Message Event Listener
/// </summary> 
/// <param name="args">ExcelDB.MessageEvnt Event Argument</param>
private static void ExcelToDBcls_MessageEvnt(ExcInfo_EventArgs args)
{
    //Write the message to the Console.
    Console.Write(args.Message);
}
/// <summary>
/// ExcelToDBcls instance Exception Event Listener
/// </summary> 
/// <param name="args">ExcelDB.ExceptionEvnt Event Argument</param>
private static void ExcelToDBcls_ExceptionEvnt(ExcError_EventArgs args)
{
    //Create the Error Message and post to the Error within Console.
    StringBuilder SB = new StringBuilder();
    SB.AppendLine(args.Error.Message);
    SB.AppendLine(args.Error.StackTrace);
    Console.Error.Write(SB.ToString());
    SB = null;
}

Additional Notes

Please be advised that using the Console eg, with just the defaults will delete previous data from the table specified.
Please add the switch of /TrnctTBL=F.

Using SQL Server 2005

Running the application can be done using SQL's xp_cmdshell.
xp_cmdshell is usually advised to be switched off due to security vulnerabilities that can be exploited.
To combat this, switch it on only when required.

Turn xp_cmdshell on:

--Reconfigure so that xp_cmdshell is on
USE master 
GO 
EXEC sp_configure 'show advanced options', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'xp_cmdshell', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'show advanced options', 0 
GO

Create the Table and Import the data:

--Create the Table and Import the Data
Use Data
Go
Create Table SQLImportTable 
(
    ExcelColumn1 varchar(5), 
    ExcelColumn2 varchar(10), 
    ExcelColumn3 varchar(20), 
    ExcelDateTimeColumn smalldatetime
)

Declare @ExeLocation varchar(255),
        @Arguments varchar(255)

Set @ExeLocation = 'C:\ExcelToDB\ExcelToDatabase.exe'
Set @Arguments = '/SVRName:Server\SQL '+
                 '/DBName:Data ' +
                 '/TBLName:SQLImportTable ' +
                 '/FileName:C:\ExcelToDB\ExcelReport.xls'

Set @ExeLocation = @ExeLocation + ' ' + @Arguments
exec Master..xp_cmdshell @ExeLocation 
GO

Reconfigure so that xp_cmdshell is off:

--Reconfigure so that xp_cmdshell is off
USE master
GO 
EXEC sp_configure 'show advanced options', 1 
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'xp_cmdshell', 0
GO 
RECONFIGURE WITH OVERRIDE 
GO 
EXEC sp_configure 'show advanced options', 0 
GO

Using CLR Functions to Run the Application

Xiangyang Liu 刘向阳 created a CLR DLL file to run applications with arguments from SQL Server.
It's called XYRunProc. If you are more open to using CLR integration as opposed to using xp_cmdshell, here is the article.

History

• 13 July 2010 - Added Command-Line argument documentation for the ConsoleEg.
• 08 July 2010 - First publication of the article