Converting Data From MS Excel To SQL Server 2005
In this article, I show you how to load data from a worksheet of .XLS (MS Excel) file to a table in SQL Server 2005.
I have an Excel worksheet with the following data. The file name is books.xls
and worksheet name is Sheet1.
Title |
Authors |
Price |
Java complete reference |
Herbert Schildt |
495 |
Beginning Hibernate |
Dave Minter and Jeff Linwood |
299 |
Professional AJAX |
Nicholas, Jeremy, Joe |
399 |
Now we want to load this data to SQL Server table. So, we have to create a
table called BOOKS in SQL Server 2005 with the following structure.
title
|
varchar(50)
|
authors
|
varchar(100)
|
price
|
money
|
In order to access data from Excel document, we have to create an ODBC DSN
( Data Source Name) that refers to ODBC driver for Excel and books.xls document,
take the following steps:
- Invoke control panel -> performance and maintenance -> administrative tools -> Data Source (ODBC)
- In ODBC Data Source Administrator program, select User
DSN tab and click on Add button
- From list of drivers select Microsoft Excel Driver (*.xls)
- Click on Finish button.
- Give msexcel and name of the Data source
- Click on Select Workbook and select books.xls
from file system.
- Click on Ok button.
Create a new Console Application with C# in Visual Studio.NET 2005 and write the
following code.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.Odbc;
namespace database
{
class ConvertExcelToSqlServer
{
static void Main(string[] args)
{
//connect to MS Excel
OdbcConnection excelcon = new OdbcConnection("dsn=msexcel");
excelcon.Open();
OdbcCommand excelcmd = new OdbcCommand("select * from [sheet1$]", excelcon);
OdbcDataReader dr = excelcmd.ExecuteReader();
SqlConnection sqlcon = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=msdb;Integrated Security=True");
sqlcon.Open();
SqlCommand cmd = new SqlCommand("insert into books values (@title,@authors,@price)",sqlcon);
cmd.Parameters.Add("@title", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@authors", SqlDbType.VarChar, 100);
cmd.Parameters.Add("@price", SqlDbType.Money);
while (dr.Read())
{
cmd.Parameters[0].Value = dr[0];
cmd.Parameters[1].Value = dr[1];
cmd.Parameters[2].Value = dr[2];
cmd.ExecuteNonQuery();
}
excelcon.Close();
sqlcon.Close();
Console.WriteLine("Loaded data from MS Excel workbook to Sql Server Database Table");
}
}
}
The above program does the following.
- Imports namespaces related to SQL Server and ODBC drivers
- Connects to MS Excel workbook (books.xls) using MSEXCEL data source name that was created earlier.
- Sheet1 is the name of the worksheet in books.xls
- Reads data from sheet1 and load data into ODBCDataReader
- Connects to Sql Server 2005 express edition using .NET Data provider for Sql Server. The default database is MSDB.
- Creates a command to insert into BOOKS table with three parameters.Defines parameters with approriate data types.
- Next it reads one row from DataReader and copies values into parameters of INSERT command. Then it executes insert command.
- Finally it closes connection of Excel and Sql Server.
Keep Learning,
P.Srikanth.