In this article, I show you how to load data from a worksheet of .XLS (MS Excel) file to a table in SQL Server 2008.
I have an Excel worksheet with the following data. The file name is books.xls and worksheet name is Sheet1.
Title | Authors | Price |
ITeShare Book C# | Ahmed Rabie | 495 |
ITeShare Book asp.net | Ahmed Rabie | 299 |
ITeShare Book Design patterns | Ahmed Rabie | 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 DsnName 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=DsnName");
excelcon.Open();
OdbcCommand excelcmd = new OdbcCommand("select * from [sheet1$]", excelcon);
OdbcDataReader dr = excelcmd.ExecuteReader();
SqlConnection sqlcon = new SqlConnection(@"Your Connection");
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.
0 التعليقات:
إرسال تعليق