Download to read offline
![By Chetan Dhamija 3 Step 3: Add these two namespaces in your class file using System.Data.OleDb; using System.Data.SqlClient; Step 4: Add below method in your class file, you can call this method from any other class and pass the excel file path public void importdatafromexcel(string excelfilepath) { //declare variables - edit these based on your particular situation string ssqltable = "datamigrationtable"; // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different string myexceldataquery = "select student,rollno,course from [sheet1$]"; try { //create our connection strings string excelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + "; extended properties=" + ""excel 8.0; hdr=yes;""; string ssqlconnectionstring = "server= mydatabaseservername; userid=dbuserid; password=dbuserpassword; database=databasename; connection reset=false"; //execute a query to erase any previous data from our destination table string sclearsql = "delete from " + ssqltable; SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring); SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn); sqlconn.Open(); sqlcmd.ExecuteNonQuery(); sqlconn.Close(); //series of commands to bulk copy data from the excel file into our sql table OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring); OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn); oledbconn.Open(); OleDbDataReader dr = oledbcmd.ExecuteReader(); SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring); bulkcopy.DestinationTableName = ssqltable; while (dr.Read()) { bulkcopy.WriteToServer(dr); } oledbconn.Close(); } catch (Exception ex) { //handle exception } }](https://image.slidesharecdn.com/importmsexceldatatosqlserver-140326001511-phpapp01/75/Excel-to-SQL-Server-3-2048.jpg)
Excel to SQL Server
- 1. By Chetan Dhamija 1 Steps To ImportMS Excel Data to SQL Server table Using C# .Net
- 2. By Chetan Dhamija 2 Ifyou already have data in MS Excel file, and want to migrate your MS Excel data to SQL Server table, follow below steps Step 1: Let’s take an example to import the data to SQL Server table, I am going to import student information data from MS excel sheet to Student SQL table, My Excel sheet structure is looks like Step 2: Now design a Student table in SQL server CREATE TABLE ( STUDENT VARCHAR(64), ROLLNO VARCHAR(16), COURSE VARCHAR(32), ) Your MS excel sheet and SQL table is ready, now it’s time to write c# code to import the excel sheet into Student table
- 3. By Chetan Dhamija 3 Step3: Add these two namespaces in your class file using System.Data.OleDb; using System.Data.SqlClient; Step 4: Add below method in your class file, you can call this method from any other class and pass the excel file path public void importdatafromexcel(string excelfilepath) { //declare variables - edit these based on your particular situation string ssqltable = "datamigrationtable"; // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different string myexceldataquery = "select student,rollno,course from [sheet1$]"; try { //create our connection strings string excelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + "; extended properties=" + ""excel 8.0; hdr=yes;""; string ssqlconnectionstring = "server= mydatabaseservername; userid=dbuserid; password=dbuserpassword; database=databasename; connection reset=false"; //execute a query to erase any previous data from our destination table string sclearsql = "delete from " + ssqltable; SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring); SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn); sqlconn.Open(); sqlcmd.ExecuteNonQuery(); sqlconn.Close(); //series of commands to bulk copy data from the excel file into our sql table OleDbConnection oledbconn = new OleDbConnection(excelconnectionstring); OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn); oledbconn.Open(); OleDbDataReader dr = oledbcmd.ExecuteReader(); SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring); bulkcopy.DestinationTableName = ssqltable; while (dr.Read()) { bulkcopy.WriteToServer(dr); } oledbconn.Close(); } catch (Exception ex) { //handle exception } }