Click here to Skip to main content
11,435,048 members (46,710 online)
Click here to Skip to main content

Import Data from Excel to SQL Server

, 16 Jan 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
This article mainly describes how to import data from Excel sheet to SQL Server, Viewing the data from SQL Server using the Gridview Control, Deleting the Data. In case the Excel sheet does not contain data (null values), those values are replaced by zero.........

Introduction

This article mainly describes how to import data from an Excel sheet to the SQL Server, viewing the data from SQL Server using the Gridview control, and deleting data. In case the Excel sheet does not contain data (null values), those values are replaced by zero......... 

Using the Code

Here I have created a step by step process to import data from Excel to SQL Server and replace the null values with zeros and store them in the SQL Server database.

Given below is a step-by-step process:

Step 1 

Create a sample table:

create table emp
(
sno int identity,
fname nvarchar(20),
lname nvarchar(20),
mobnum nvarchar(15),
city nvarchar(20),
state nvarchar(30),
zip int
)

Step 2

Take a Web page and name it as Excel2sql.aspx (Following is the code contained in the Excel2sql.aspx page).

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel2Sql.aspx.cs" 
	Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>:: Importing Data From Excel Sheet to SQL Server ::</title>
<style type="text/css">
.style1
{
width: 50%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>

<table align="center" class="style1">
<tr>
<td align="center">
<asp:LinkButton ID="insertdata" runat="server" onclick="insertdata_Click">
	Insert Data</asp:LinkButton>
</td>
<td align="center">
<asp:LinkButton ID="viewdata" runat="server" onclick="viewdata_Click">
	View Data</asp:LinkButton>
</td>
<td align="center"> 
<asp:LinkButton ID="deletedata" runat="server" onclick="deletedata_Click">
	Delete Data</asp:LinkButton>
</td>
</tr>
<tr>
<td colspan="9" align="center">
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</td>

</tr>
<tr>
<td colspan="9" align="center">
<asp:Label ID="lblmsg" runat="server" Width="500px"></asp:Label>
</td>

</tr>
</table>

</div>
</form>
</body>
</html> 

Step 3

Here is the C# code in the Excel2sql.aspx.cs file:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb; //This namespace is mainly used for dealing with 
		       //Excel sheet data
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void insertdata_Click(object sender, EventArgs e)
    {
        OleDbConnection oconn = new OleDbConnection
		(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
		Server.MapPath("example.xls") + ";
		Extended Properties=Excel 8.0");//OledbConnection and 
				// connectionstring to connect to the Excel Sheet
        try
        {
            //After connecting to the Excel sheet here we are selecting the data 
	   //using select statement from the Excel sheet
	   OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
	            oconn.Open();  //Here [Sheet1$] is the name of the sheet 
				//in the Excel file where the data is present
            OleDbDataReader odr = ocmd.ExecuteReader();
            string fname = "";
            string lname = "";
            string mobnum = "";
            string city = "";
            string state = "";
            string zip = "";
            while (odr.Read())
            {
                fname = valid(odr, 0);//Here we are calling the valid method
                lname = valid(odr, 1);
                mobnum = valid(odr, 2);
                city = valid(odr, 3);
                state = valid(odr, 4);
                zip = valid(odr, 5);
                //Here using this method we are inserting the data into the database
	       insertdataintosql(fname, lname, mobnum, city, state, zip);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    } 
//This valid method is mainly used to check where the null values are 
//contained in the Excel Sheet and replacing them with zero
    protected string valid(OleDbDataReader myreader, int stval)//if any columns are 
				//found null then they are replaced by zero
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }
    protected void viewdata_Click(object sender, EventArgs e)//Code to View 
						// the data from the SQL Server
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
	AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter("select * from emp", conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        catch (DataException de)
        {
            lblmsg.Text = de.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Shown Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }
    }
    public void insertdataintosql(string fname, string lname, 
		string mobnum, string city, string state, string zip)
    {//inserting data into the Sql Server
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
	AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "insert into emp(fname,lname,mobnum,city,state,zip) 
			values(@fname,@lname,@mobnum,@city,@state,@zip)";
        cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
        cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
        cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
        cmd.Parameters.Add("@state", SqlDbType.NVarChar).Value = state;
        cmd.Parameters.Add("@zip", SqlDbType.Int).Value = Convert.ToInt32(zip);
        cmd.CommandType = CommandType.Text;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }
    protected void deletedata_Click(object sender, EventArgs e)//Here we are deleting 
						// the data from the SQL Server
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;
	AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "delete from emp";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            cmd.ExecuteScalar();
            conn.Close();
        }
        catch (DataException de1)
        {
            lblmsg.Text = de1.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Deleted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
    }
} 

Points of Interest

  1. Using the OledbConnection to connect to the Excel Sheet
  2. The Connection String used to connect to the Excel sheet
  3. Selecting the data from the Excel file
  4. Replacing the null values with zeros from the Excel sheet and inserting into the SQL Server
  5. Viewing and deleting the data

History

  • 16th January, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Vivekananda Swamy.M
Software Developer
India India
Hi all,I am M.Vivekananda Swamy,I have completed my MCA
from Osmaina University,Hyderabad(India) and
currently working as an Software Developer(.Net) in an IT firm in Hyderabad.I have around 1.5yrs
of experience in developing the web applications.
My areas of interest are Asp.Net-2.0,3.5,C#.Net,Ajax,Sqlserver-2005,HTML/DHTML

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 998059120-Mar-15 2:37
memberMember 998059120-Mar-15 2:37 
GeneralThanks a lot Pin
Arkadeep De16-Oct-14 2:36
professionalArkadeep De16-Oct-14 2:36 
Questioncode not working for external file Pin
Vikram Rathaur9-Jul-14 0:34
memberVikram Rathaur9-Jul-14 0:34 
QuestionHow to import data from excel with more then one sheet to sql server Pin
saeel chary22-Oct-13 0:26
membersaeel chary22-Oct-13 0:26 
GeneralMy vote of 5 Pin
Member 1027935623-Sep-13 0:59
memberMember 1027935623-Sep-13 0:59 
GeneralMy vote of 3 Pin
prahalad.gaggar16-Apr-13 5:00
memberprahalad.gaggar16-Apr-13 5:00 
SuggestionGood Artical Pin
Cbab11-Dec-12 23:03
memberCbab11-Dec-12 23:03 
GeneralMy vote of 5 Pin
prahalad.gaggar27-Nov-12 0:13
memberprahalad.gaggar27-Nov-12 0:13 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 7:12
mvpKanasz Robert24-Sep-12 7:12 
GeneralGood article Pin
aliya_stan2-Sep-12 3:58
memberaliya_stan2-Sep-12 3:58 
AnswerAnother option Pin
Docco121-May-12 2:27
memberDocco121-May-12 2:27 
Questioni need to return null not zero Pin
tatatab11-Apr-12 15:35
membertatatab11-Apr-12 15:35 
QuestionError Pin
xbolslock4-Apr-12 18:43
memberxbolslock4-Apr-12 18:43 
Questionexcel sheets data into Access database usring vb.net code. Pin
HARSHAD PANCHAL3-Jan-12 20:46
memberHARSHAD PANCHAL3-Jan-12 20:46 
AnswerSOLUTION FOR ERROR "An attempt to attach an auto-named database for file " Pin
Syed Muhammad Ali10-Oct-11 12:38
memberSyed Muhammad Ali10-Oct-11 12:38 
HI, If someone is having this error"An attempt to attach an auto-named database for file "

You have to add this line of Code
Integrated Security=True;User Instance=True;
in all 3 Appearances of these lines of Code(Sql Connection)
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");

Modified Code:
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;Integrated Security=True;User Instance=True;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");



For details http://forums.asp.net/t/879454.aspx/1[^]


Here goes Complete Code for The File"Excel2Sql.aspx"

Code Starts here

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void insertdata_Click(object sender, EventArgs e)
{
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + ";Extended Properties=Excel 8.0");
try
{
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
oconn.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
string fname = "";
string lname = "";
string mobnum = "";
string city = "";
string state = "";
string zip = "";
while (odr.Read())
{
fname = valid(odr, 0);
lname = valid(odr, 1);
mobnum = valid(odr, 2);
city = valid(odr, 3);
state = valid(odr, 4);
zip = valid(odr, 5);
insertdataintosql(fname, lname, mobnum, city, state, zip);
}
oconn.Close();
}
catch (DataException ee)
{
lblmsg.Text = ee.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Inserted Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Green;
}
}
protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero
{
object val = myreader[stval];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}
protected void viewdata_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;Integrated Security=True;User Instance=True;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
try
{
SqlDataAdapter sda = new SqlDataAdapter("select * from emp", conn);
DataSet ds = new DataSet();
sda.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (DataException de)
{
lblmsg.Text = de.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Shown Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Green;
}
}
public void insertdataintosql(string fname, string lname, string mobnum, string city, string state, string zip)
{
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;Integrated Security=True;User Instance=True;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into emp(fname,lname,mobnum,city,state,zip) values(@fname,@lname,@mobnum,@city,@state,@zip)";
cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
cmd.Parameters.Add("@state", SqlDbType.NVarChar).Value = state;
cmd.Parameters.Add("@zip", SqlDbType.Int).Value = Convert.ToInt32(zip);
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
protected void deletedata_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;Integrated Security=True;User Instance=True;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "delete from emp";
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteScalar();
conn.Close();
}
catch (DataException de1)
{
lblmsg.Text = de1.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "Data Deleted Sucessfully";
lblmsg.ForeColor = System.Drawing.Color.Red;
}
}

}
GeneralExcellent , Simple And Elegant Pin
Syed Muhammad Ali10-Oct-11 12:25
memberSyed Muhammad Ali10-Oct-11 12:25 
Questiontotal 5 Pin
Uma Shankar Patel31-Aug-11 3:57
memberUma Shankar Patel31-Aug-11 3:57 
GeneralMy vote of 5 Pin
Marcio_Coelho16-Dec-10 6:48
memberMarcio_Coelho16-Dec-10 6:48 
GeneralMy vote of 5 Pin
pumudu8-Dec-10 0:55
memberpumudu8-Dec-10 0:55 
QuestionHow can I retain null values, rather than replacing with 0 [modified] Pin
CSharpNewcomer14-Oct-10 11:33
memberCSharpNewcomer14-Oct-10 11:33 
QuestionImport Data from Excel Sheets to Access database using VB.NET Pin
saradhi.dotnet11-Oct-10 2:35
membersaradhi.dotnet11-Oct-10 2:35 
GeneralImport Data from SQL Server to Excel Pin
wendetta7710-Oct-10 12:23
memberwendetta7710-Oct-10 12:23 
GeneralRe: Import Data from SQL Server to Excel Pin
Johnny Glenn18-Apr-12 23:35
memberJohnny Glenn18-Apr-12 23:35 
QuestionDatabase error Pin
Shiban19-Oct-10 21:15
memberShiban19-Oct-10 21:15 
AnswerRe: Database error Pin
Shiban Banerjee19-Oct-10 4:32
memberShiban Banerjee19-Oct-10 4:32 
Generalunvalid value excel text into sqlserver 2000 nvarchar format using vb.net Pin
newdbo3-Sep-09 23:42
membernewdbo3-Sep-09 23:42 
GeneralImporting Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns Pin
praveenkumar_mca8-Jun-09 20:21
memberpraveenkumar_mca8-Jun-09 20:21 
QuestionHow to Import Multiple sheets Pin
Manikantan200930-May-09 0:47
memberManikantan200930-May-09 0:47 
Generalreg import of excel sheets into tables in sql server database Pin
MahesJadhav21-Apr-09 23:15
memberMahesJadhav21-Apr-09 23:15 
Generalimport data Pin
orked mohamed17-Mar-09 5:38
memberorked mohamed17-Mar-09 5:38 
GeneralRe: import data Pin
prashantvk7-Apr-09 3:47
memberprashantvk7-Apr-09 3:47 
GeneralFormatting Pin
Mika Wendelius25-Jan-09 8:48
mvpMika Wendelius25-Jan-09 8:48 
GeneralCatch/Finally Blocks Pin
MikeG19-Jan-09 6:52
memberMikeG19-Jan-09 6:52 
GeneralNothing New... Pin
suresh suthar16-Jan-09 19:07
membersuresh suthar16-Jan-09 19:07 
GeneralXML to spead up the transactions Pin
Member 221825016-Jan-09 6:22
memberMember 221825016-Jan-09 6:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150428.2 | Last Updated 16 Jan 2009
Article Copyright 2009 by Vivekananda Swamy.M
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid