Import Data into Sql Server From Excel Spreadsheet using SQLBulkCopy class

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.OleDb;

using System.Data.Common;

using System.Data.SqlClient;

 

namespace WindowsFormsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            // Connection String to Excel Workbook

            string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"";IMEX=1;";

 

            // Create Connection to Excel Workbook

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))

            {

                OleDbCommand command = new OleDbCommand("Select ID,Data FROM [Data$]", connection);

 

                connection.Open();

 

                // Create DbDataReader to Data Worksheet

                using (DbDataReader dr = command.ExecuteReader())

                {

                    // SQL Server Connection String

                    string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";

 

                    // Bulk Copy to SQL Server

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))

                    {

                        bulkCopy.DestinationTableName = "ExcelData";

                        bulkCopy.WriteToServer(dr);

                    }

                }

            }

 

        }

    }

}

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Good codesnippets Himanshu.

Little explanation of the code would have made it awesome!


Regards,
Sheo Narayan, Microsoft MVP
The Founder
http://www.dotnetfunda.com

I will try to explain you the code snippet

1) string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;ExtendedProperties=""Excel 8.0;HDR=YES;"";IMEX=1;";

above connection string to connect to excel and fetch the data

2)
OleDbCommand command = new OleDbCommand("Select ID,Data FROM [Data$]", connection);
this gets the data from excel tabel [data$], that is already defined in excel.

3)

SqlBulkCopy object is used to insert datatable in database table, please use below URL for more info

http://www.dotnetcurry.com/ShowArticle.aspx?ID=323

i hope this helps u understand the code snippet