Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Blogs Author
Mon, 28-Jul-2014 Authors
All Time Authors
Samirbhogayta
9750
Satyapriyanayak
7575
Jvprabhusanthi
6150

Latest members | More ...


(Statistics delayed by 5 minutes)

Import Data into Sql Server From Excel Spreadsheet using SQLBulkCopy class

Hmanjarawala
Posted by Hmanjarawala under C# on 8/2/2011 2:12:06 AM | Points: 75 | Views : 10940 | 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()


        {


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

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



Login to post response