How to Import data from EXCEL to SQL Database in ASP.NET

Shobhit Saxena

9y3.3k

i am using this code for this solution.

using System;

using System.Data;

using System.IO;

using System.Data.OleDb;

using System.Configuration;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

OleDbConnection Econ;

SqlConnection con;

string constr,Query,sqlconn;

protected void Page_Load(object sender, EventArgs e)

{

}

private void ExcelConn(string FilePath)

{

constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);

Econ = new OleDbConnection(constr);

}

private void connection()

{

sqlconn = ConfigurationManager.ConnectionStrings["con1"].ConnectionString;

con = new SqlConnection(sqlconn);

}

private void InsertExcelRecords(string FilePath)

{

ExcelConn(FilePath);

Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");

OleDbCommand Ecom = new OleDbCommand(Query, Econ);

Econ.Open();

DataSet ds=new DataSet();

OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);

Econ.Close();

oda.Fill(ds);

DataTable Exceldt = ds.Tables[0];

connection();

//creating object of SqlBulkCopy

SqlBulkCopy objbulk = new SqlBulkCopy(con);

//assigning Destination table name

objbulk.DestinationTableName = "Employee";

//Mapping Table column

objbulk.ColumnMappings.Add("Name", "Name");

objbulk.ColumnMappings.Add("City", "City");

objbulk.ColumnMappings.Add("Address", "Address");

objbulk.ColumnMappings.Add("Designation", "Designation");

//inserting Datatable Records to DataBase

con.Open();

objbulk.WriteToServer(Exceldt);

con.Close();

}

protected void Button1_Click(object sender, EventArgs e)

{

string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);

InsertExcelRecords(CurrentFilePath);

}

But i am getting this error

Server Error in '/InsertExcelFileRecords' Application.

The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

Source Error:

Line 44:             OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ); 
Line 45:             Econ.Close(); 
Line 46:             oda.Fill(ds); 
Line 47:             DataTable Exceldt = ds.Tables[0]; 
Line 48:        connection();

Source File: c:\Users\ghg\Desktop\InsertExcelFileRecords\Default.aspx.cs Line: 46

I have already open vs in admin mode but not working.Anyone suggest me what should i do in this code.My excel sheet name is Sheet1

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Uehara Ayaka9y ago
Hi you may find this article helpful

http://www.c-sharpcorner.com/blogs/import-and-export-data-from-excel-to-database
+1
Nilesh Shah9y ago
are the worksheets in your excel file named as Sheet1, Sheet2, etc.

or are those named differently?
0
Shobhit Saxena9y ago
No need to if (fileExt.CompareTo(".xls") == 0)

Code is run without any error.Only i restart the vs then i got, there is no error and data inserted in table.
+1
Pooja Dixit9y ago
First: You need to Excel Sheet Read

public DataTable ReadExcel(string fileName, string fileExt)
{
string conn = string.Empty;
DataTable dtexcel = new DataTable();
if (fileExt.CompareTo(".xls") == 0)
conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
else
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007
using (OleDbConnection con = new OleDbConnection(conn))
{
try
{
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet2$]", con); //here we read data from sheet1
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
}
catch { }
}
return dtexcel;
}
+1
Rajkiran Swain9y ago
Have u verified the path(try to manually find the file using the path though explorer) and sheet name ?(xls or xlsx)
+1
Manikandan Murugesan9y ago
Refer the following links :
http://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-excel-file-records-into-sql-server-database-using/

https://stackoverflow.com/questions/24281255/how-do-you-import-excel-data-to-sql-server-using-asp-net-c-sharp-where-the-table

http://www.c-sharpcorner.com/UploadFile/99bb20/import-excel-data-to-sql-server-in-Asp-Net/
+1