import .xls file to mysql database

protected void btnExcel_Click(object sender, EventArgs e)

{

string connString = "";

string strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();

string path =Server.MapPath(FileUpload1.PostedFile.FileName);

//Connection String to Excel Workbook

if (strFileType.Trim() == ".xls")

{

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

}

else if (strFileType.Trim() == ".xlsx")

{

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

}

// FileUpload1.SaveAs(Server.MapPath(FileUpload1.FileName));

string query = "SELECT * FROM [UserDetails]";

OleDbConnection conn = new OleDbConnection(connString);

if (conn.State == ConnectionState.Closed)

conn.Open();

OleDbCommand cmd = new OleDbCommand(query, conn);

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

// string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);

// dt=objBLHR.Read_Sheet(filename);

gd1.DataSource = ds.Tables[0];

gd1.DataBind();

}

How to avoid this error from the page.

this article might be of help

http://www.c-sharpcorner.com/blogs/import-and-export-data-from-excel-to-database

But sir first check for instalation of ms office 2007 with ms access database.

Otherwise download the oledb driver from microsoft office site

OleDbConnection Econ;
if (Path.GetExtension(filepath) == ".xls")
{
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=2""", filepath);
}
else if (Path.GetExtension(filepath) == ".xlsx")
{
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""", filepath);
}
Econ = new OleDbConnection(constr);

sir, it is showing error at conn.open().

sir you should use the datatable fromy system

System.Data.DataTable dt = ds.Tables[0];

gd1.DataSource = dt;

for the connection plz look that installed ms access 2007

5 Replies

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

Uehara Ayaka8y ago
this article might be of help

http://www.c-sharpcorner.com/blogs/import-and-export-data-from-excel-to-database
0
Laxmidhar Sahoo8y ago
But sir first check for instalation of ms office 2007 with ms access database.
Otherwise download the oledb driver from microsoft office site
0
Laxmidhar Sahoo8y ago
1. OleDbConnection Econ;
2. if (Path.GetExtension(filepath) == ".xls")
3. {
4. constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=2""", filepath);
5. }
7. else if (Path.GetExtension(filepath) == ".xlsx")
8. {
9. constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1""", filepath);
10. }
12. Econ = new OleDbConnection(constr);