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 can import the excel spreadsheet 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.

SET ANSI_NULLS ON   
GO   
SET QUOTED_IDENTIFIER ON   
GO   
SET ANSI_PADDING ON   
GO   
CREATE TABLE [dbo].[Table1](   
    [student] [varchar](50) NULL,   
    [rollno] [int] NULL,   
    [course] [varchar](50) NULL   
) ON [PRIMARY]   
GO   
SET ANSI_PADDING OFF   
GO

After this we will prepare an excel sheet.

Now check the code to import the excel sheet

public void ImportDataFromExcel(string excelFilePath)   
{   
    //declare variables - edit these based on your particular situation   
    string ssqltable = "Table1";   
    // 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 sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelFilePath +   
        ";extended properties=" + "\"excel 8.0;hdr=yes;\"";   
        string ssqlconnectionstring = "Data Source=SAYYED;Initial Catalog=SyncDB;Integrated Security=True";   
        //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(sexcelconnectionstring);   
        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);   
        }   
        dr.Close();   
        oledbconn.Close();   
        Label1.Text = "File imported into sql server successfully.";   
    }   
    catch (Exception ex)   
    {   
        //handle exception   
    }   
}

 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.