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.