Import Excel Spreadsheet Data Into SQL Server Table Using C#

This blog will show you how you can import the excel spread sheet data into the SQL Server database table using c#.net.

This blog will show you how you an import the excel spread sheet data into the SQL Server database table using c#.net. So for this blog first we will first create a SQL table. Here is the query to create a table.
  1. SET ANSI_NULLS ON   
  2. GO   
  3. SET QUOTED_IDENTIFIER ON   
  4. GO   
  5. SET ANSI_PADDING ON   
  6. GO   
  7. CREATE TABLE [dbo].[Table1](   
  8.     [student] [varchar](50) NULL,   
  9.     [rollno] [intNULL,   
  10.     [course] [varchar](50) NULL   
  11. ON [PRIMARY]   
  12. GO   
  13. SET ANSI_PADDING OFF   
  14. GO  
After this we will prepare an excel sheet.
 
 
Now check the code to import the excel sheet
  1. public void ImportDataFromExcel(string excelFilePath)   
  2. {   
  3.     //declare variables - edit these based on your particular situation   
  4.     string ssqltable = "Table1";   
  5.     // make sure your sheet name is correct, here sheet name is sheet1,
        so you can change your sheet name if have    different 
      
  6.     string myexceldataquery = "select student,rollno,course from [Sheet1$]";   
  7.     try   
  8.     {   
  9.         //create our connection strings   
  10.         string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelFilePath +   
  11.         ";extended properties=" + "\"excel 8.0;hdr=yes;\"";   
  12.         string ssqlconnectionstring = "Data Source=SAYYED;Initial Catalog=SyncDB;Integrated Security=True";   
  13.         //execute a query to erase any previous data from our destination table   
  14.         string sclearsql = "delete from " + ssqltable;   
  15.         SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);   
  16.         SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);   
  17.         sqlconn.Open();   
  18.         sqlcmd.ExecuteNonQuery();   
  19.         sqlconn.Close();   
  20.         //series of commands to bulk copy data from the excel file into our sql table   
  21.         OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);   
  22.         OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);   
  23.         oledbconn.Open();   
  24.         OleDbDataReader dr = oledbcmd.ExecuteReader();   
  25.         SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);   
  26.         bulkcopy.DestinationTableName = ssqltable;   
  27.         while (dr.Read())   
  28.         {   
  29.             bulkcopy.WriteToServer(dr);   
  30.         }   
  31.         dr.Close();   
  32.         oledbconn.Close();   
  33.         Label1.Text = "File imported into sql server successfully.";   
  34.     }   
  35.     catch (Exception ex)   
  36.     {   
  37.         //handle exception   
  38.     }   
  39.  
In above first i have read the excel sheet and then uses the sql command to read the excel sheet data. After reading the data i have save the data to the sql table.