Appear in Online Interview to test your skills and be ready for job!
Twitter Twitter LinkedIn YouTube Google
Be Interview ready ! | Search
Submit content and win cash & prizes monthly.

Win Prizes

Like us on Facebook
Top Blogs Author
Thu, 24-Apr-2014 Authors
All Time Authors

Latest members | More ...

(Statistics delayed by 5 minutes)

Import Data into Sql Server From Excel Spreadsheet using SQLBulkCopy class

Posted by Hmanjarawala under C# on 8/2/2011 2:12:06 AM | Points: 75 | Views : 9969 | Status : [Member]

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()





        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);




                // 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";









Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
Found interesting? Add this to:

Comments or Responses

Posted by: SheoNarayan on: 8/2/2011 | Level:HonoraryPlatinum | Status: [Microsoft_MVP] [Administrator] | Points: 15

Good codesnippets Himanshu.

Little explanation of the code would have made it awesome!

Posted by: Kunalverma31 on: 8/12/2011 | Level:Starter | Status: [Member] | Points: 15

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

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


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

i hope this helps u understand the code snippet

Login to post response.

Comments / Responses
Select text & click toolbar to format. Formatting appears in viewmode only. HTML Tags are not allowed.
Bold Italic Underline Paragraph Title Code  Link 
 Wait ... Processing ..... please wait.