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 2nd 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:
ConnectionString ConnString = new ConnectionString(D_Info, 30);
ConnectionString ConnString = new ConnectionString(D_Info, 30, "SA", "SAPassword");
Now that those are created, we can create the ExcelToDB
Class:
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");
ConnectionString ConnString = new ConnectionString(D_Info, 30);
/Creates a SQL Authenticated ConnectionString instance
ExcelDB ExcelToDBcls = new ExcelDB("C:\MyFile.xls", ConnString);
ExcelToDBcls.ExceptionEvnt += new ExcelDB.ExceptionDelegate
(ExcelToDBcls_ExceptionEvnt);
ExcelToDBcls.MessageEvnt += new ExcelDB.MessageDelegate(ExcelToDBcls_MessageEvnt);
ExcelToDatabasecls.ReadExcelIntoDatabase();
}
private static void ExcelToDBcls_MessageEvnt(ExcInfo_EventArgs args)
{
Console.Write(args.Message);
}
private static void ExcelToDBcls_ExceptionEvnt(ExcError_EventArgs args)
{
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:
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:
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:
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