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:
  1. Invoke control panel -> performance and maintenance -> administrative tools -> Data Source (ODBC)
  2. In ODBC Data Source Administrator  program, select User DSN tab and click on  Add button
  3. From list of drivers select Microsoft Excel Driver (*.xls)
  4. Click on Finish button.
  5. Give  msexcel and name of the Data source
  6. Click on Select Workbook and select  books.xls from file system.
  7. 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.

Keep Learning,
P.Srikanth.