how to Import data from EXCEL to SQL Database in ASP.NET [Resolved]

Posted by Mandlaa under ASP.NET on 5/9/2013 | Points: 10 | Views : 9663 | Status : [Member] | Replies : 11
Hi,how to Import data from EXCEL to SQL Database in ASP.NET

I am tying two approuches But not solve my problem,
I am getting an error like this

ERROR:
Please once see my code i am attachment

please help me

SQL TABLE CODE:
CREATE TABLE [dbo].[Book1](
[ID] [int] NULL,
[NAME] [varchar](50) NULL,
[CITY] [varchar](50) NULL
)




Responses

Posted by: Satyapriyanayak on: 5/9/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Default.aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Show_Excel_gridview_insert_to_db._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 runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Table ID="TableButtons" runat="server">
<asp:TableRow ID="TableRow1" runat="server">
<asp:TableCell ID="TableCell1" runat="server">
<asp:Button ID="ButtonUpload" runat="server"
Text="Upload Excel Files" OnClick="ButtonUpload_Click" />
</asp:TableCell>
<asp:TableCell ID="TableCell2" runat="server">
<asp:Button ID="ButtonView" runat="server"
Text="View Excel Data" OnClick="ButtonView_Click" />
</asp:TableCell>

</asp:TableRow>
</asp:Table>
<asp:Panel ID="PanelUpload" runat="server" Visible="False">
<asp:FileUpload ID="FileUploadExcel" runat="server" />
<br />
Please select an Excel file to import:<br />
<asp:Button ID="ButtonUploadFile" runat="server"
Text="Upload File" OnClick="ButtonUploadFile_Click" /><br />
<asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="PanelView" runat="server" Visible="False">
<asp:Label ID="LabelGrid" runat="server" Text=""></asp:Label>
<asp:GridView ID="GridViewExcel" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84"
BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</asp:Panel>
<asp:Panel ID="PanelImport" runat="server" Visible="False">
<asp:Label ID="LabelImport" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Label ID="lblError" runat="server" Text=""></asp:Label>
</div>
<asp:Button ID="btn_insert" runat="server" onclick="btn_insert_Click"
Text="Insert to Db" />

<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</form>
</body>
</html>


If this post helps you mark it as answer
Thanks

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 5/9/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Default.aspx page


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Show_Excel_gridview_insert_to_db._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 runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Table ID="TableButtons" runat="server">
<asp:TableRow ID="TableRow1" runat="server">
<asp:TableCell ID="TableCell1" runat="server">
<asp:Button ID="ButtonUpload" runat="server"
Text="Upload Excel Files" OnClick="ButtonUpload_Click" />
</asp:TableCell>
<asp:TableCell ID="TableCell2" runat="server">
<asp:Button ID="ButtonView" runat="server"
Text="View Excel Data" OnClick="ButtonView_Click" />
</asp:TableCell>

</asp:TableRow>
</asp:Table>
<asp:Panel ID="PanelUpload" runat="server" Visible="False">
<asp:FileUpload ID="FileUploadExcel" runat="server" />
<br />
Please select an Excel file to import:<br />
<asp:Button ID="ButtonUploadFile" runat="server"
Text="Upload File" OnClick="ButtonUploadFile_Click" /><br />
<asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="PanelView" runat="server" Visible="False">
<asp:Label ID="LabelGrid" runat="server" Text=""></asp:Label>
<asp:GridView ID="GridViewExcel" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84"
BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</asp:Panel>
<asp:Panel ID="PanelImport" runat="server" Visible="False">
<asp:Label ID="LabelImport" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Label ID="lblError" runat="server" Text=""></asp:Label>
</div>
<asp:Button ID="btn_insert" runat="server" onclick="btn_insert_Click"
Text="Insert to Db" />

<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</form>
</body>
</html>


using System;
using System.Collections;
using System.Configuration;
using System.Data;
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;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace Show_Excel_gridview_insert_to_db
{
public partial class _Default : System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand com;

protected void ButtonUploadFile_Click(object sender, EventArgs e)
{
if (FileUploadExcel.HasFile)
{
try
{
FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"));
LabelUpload.Text = "Upload File Name: " +
FileUploadExcel.PostedFile.FileName + "<br>" +
"Type: " + FileUploadExcel.PostedFile.ContentType +
" File Size: " + FileUploadExcel.PostedFile.ContentLength +
" kb<br>";
}
catch (System.NullReferenceException ex)
{
LabelUpload.Text = "Error: " + ex.Message;
}
}
else
{
LabelUpload.Text = "Please select a file to upload.";
}
}
protected OleDbCommand ExcelConnection()
{

string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection Con = new OleDbConnection(ConnStr);
Con.Open();
OleDbCommand com = new OleDbCommand("SELECT * FROM [sheet1$]", Con);
return com;

}
protected void ButtonView_Click(object sender, System.EventArgs e)
{
PanelUpload.Visible = false;
PanelView.Visible = true;
PanelImport.Visible = false;

OleDbDataAdapter oledbda = new OleDbDataAdapter();
oledbda.SelectCommand = ExcelConnection();
DataSet ds = new DataSet();
oledbda.Fill(ds);
GridViewExcel.DataSource = ds.Tables[0].DefaultView;
GridViewExcel.DataBind();
}
protected void ButtonUpload_Click(object sender, System.EventArgs e)
{
PanelUpload.Visible = true;
PanelView.Visible = false;
PanelImport.Visible = false;
}

protected void btn_insert_Click(object sender, EventArgs e)
{
foreach (GridViewRow g1 in GridViewExcel.Rows)
{
SqlConnection con = new SqlConnection(connStr);
com = new SqlCommand("insert into student(sid,sname,smarks,saddress) values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "')", con);
con.Open();
com.ExecuteNonQuery();
con.Close();

}
Label1.Text = "Records inserted successfully";
}
}
}


If this post helps you mark it as answer
Thanks

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mandlaa on: 5/9/2013 [Member] Starter | Points: 25

Up
0
Down
Hi ,I am trying this but getting this error in my systen

ERRO:External table is not in the expected format.

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 5/9/2013 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Guys please format your Code into a Code Tag supplied to you on the toolbar

Thank you for posting at Dotnetfunda
[Administrator]

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Learningtorise on: 5/9/2013 [Member] Starter | Points: 25

Up
0
Down
You can use Import Wizard of SQL for same.... Its easy and fast...

To create an Excel connection from the SQL Server Import and Export Wizard:

Right Click on DB Where you want to import, select Tasks>Import Data...

This will start the 32-bit version of the SQL Server Import and Export Wizard.

On the Choose Data Source, select Microsoft Excel, and then configure the Excel connection.

http://hashtagakash.wordpress.com/

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mandlaa on: 5/9/2013 [Member] Starter | Points: 25

Up
0
Down
Thank you so much ,it's working fin


Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mandlaa on: 5/9/2013 [Member] Starter | Points: 25

Up
0
Down
It's working fin,

string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/sample.xls") + ";" + "Extended Properties=Excel 8.0;";

The above code given:Server.MapPath("~/sample1.xls")
i can use dynamically apply this filename when i am Uploadfile,Don't use the staticd file name
For suppose i want upload"sample1.xls' it's not working

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Valley on: 5/9/2013 [Member] Starter | Points: 25

Up
0
Down
http://www.66pacific.com/sql_server_import_from_excel.aspx

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shekhar04 on: 6/23/2013 [Member] Starter | Points: 25

Up
0
Down
For import data into database from excel check the below link
http://chauhanshekhar.blogspot.in/p/how-to-import-data-from-excel-to-sql.html
this will help you to resolve your issues....Hope this will like you...!

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Priyagoel_24 on: 7/23/2013 [Member] Starter | Points: 25

Up
0
Down
hello friend
please help me
how to export data from sqldatabase to excel asp.net
thankyou


Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 7/23/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi priya,


First fetch db records to a gridview then export it to excel sheel.

Default.aspx code

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" 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 runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
<asp:GridView ID="g1" runat="server">
</asp:GridView>
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="Export data to Excel"
BackColor="Yellow" Font-Bold="True" ForeColor="#FF3300"></asp:Label><br />

<asp:ImageButton ID="ImageButton1" runat="server" ImageUrl= "~/images/Excel2.JPG" />

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

Default.aspx.vb code

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Partial Class _Default
Inherits System.Web.UI.Page
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ConnectionString").ToString()
Dim con As New SqlConnection(strConnString)
Dim str As String
Dim com As SqlCommand
Dim sqlda As SqlDataAdapter
Dim ds As DataSet
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
bindgrid()
g1.Visible = False
End Sub
Sub bindgrid()
con.Open()
str = "select * from SampleCustomer"
com = New SqlCommand(str, con)
sqlda = New SqlDataAdapter(com)
ds = New DataSet
sqlda.Fill(ds, "SampleCustomer")
g1.DataSource = ds
g1.DataMember = "SampleCustomer"
g1.DataBind()
con.Close()
End Sub
Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Dim oStringWriter As New System.IO.StringWriter
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

g1.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString())
Response.[End]()

End Sub

Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
g1.Visible = True
ExportToExcel("Report.xls", g1)
End Sub
Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

End Sub
End Class


If this post helps you mark it as answer
Thanks

Mandlaa, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response