Load Excel file into Database using FileUpload Control Error
-
Dear friends, I am uploading Excel sheet data using FileUpload control and want to store the data into sql server 2005 database. Excel Sheet file name is same as Table name in Database and Column headings are same as columns in database table. In my machine, I don't have MS Office. I am using OpenOffice now. I am getting an error "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly." . I have written the following complete code. Please help me. Its very urgent. Please please please. The code is using System; 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.SqlClient; using System.Data.OleDb; public partial class _Default : System.Web.UI.Page { SqlConnection connection=new SqlConnection("data source=10.0.3.30;initial catalog=rambasedev;user id=sa;password=admin"); protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { { Boolean fileOK = false; String path = Server.MapPath("~/UploadedFiles/"); if (FileUpload1.HasFile) { String fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); String[] allowedExtensions = { ".xls", ".xlsx" }; for (int i = 0; i < allowedExtensions.Length; i++) { if (fileExtension == allowedExtensions[i]) { fileOK = true; } } } if (fileOK) { try { string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath(FileUpload1.FileName) + ";" + "Extended Properties=Excel 8.0;"; using (OleDbConnection connection = new OleDbConnection(xConnStr)) { OleDbCommand command = new OleDbCommand("Select EMPNO,ENAME,BASIC FROM [Sheet1$]", connection); connection.Open(); // Create DbDataReader to Da
-
Dear friends, I am uploading Excel sheet data using FileUpload control and want to store the data into sql server 2005 database. Excel Sheet file name is same as Table name in Database and Column headings are same as columns in database table. In my machine, I don't have MS Office. I am using OpenOffice now. I am getting an error "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly." . I have written the following complete code. Please help me. Its very urgent. Please please please. The code is using System; 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.SqlClient; using System.Data.OleDb; public partial class _Default : System.Web.UI.Page { SqlConnection connection=new SqlConnection("data source=10.0.3.30;initial catalog=rambasedev;user id=sa;password=admin"); protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { { Boolean fileOK = false; String path = Server.MapPath("~/UploadedFiles/"); if (FileUpload1.HasFile) { String fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); String[] allowedExtensions = { ".xls", ".xlsx" }; for (int i = 0; i < allowedExtensions.Length; i++) { if (fileExtension == allowedExtensions[i]) { fileOK = true; } } } if (fileOK) { try { string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath(FileUpload1.FileName) + ";" + "Extended Properties=Excel 8.0;"; using (OleDbConnection connection = new OleDbConnection(xConnStr)) { OleDbCommand command = new OleDbCommand("Select EMPNO,ENAME,BASIC FROM [Sheet1$]", connection); connection.Open(); // Create DbDataReader to Da
For .xlsx files I think u will need another connection string Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0; u can also Add HDR property to connection string Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0;HDR=YES; HDR=YES --> implies that the file has header info in the 1st Row Try this let me know if it works or not Tej Aj, http://opexsolution.com/forum/[^]
-
For .xlsx files I think u will need another connection string Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0; u can also Add HDR property to connection string Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0;HDR=YES; HDR=YES --> implies that the file has header info in the 1st Row Try this let me know if it works or not Tej Aj, http://opexsolution.com/forum/[^]
Dear Tej, I have tried Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0; for .xlsx files. I am getting the following error. Please help me... ex = {"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."} Regards, Dileep
-
Dear Tej, I have tried Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties=Excel 12.0; for .xlsx files. I am getting the following error. Please help me... ex = {"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."} Regards, Dileep
may be u don't have excel providers installed try installing 2007 Office System Driver: Data Connectivity Components from below link http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en[^] They are used to install drivers on the machine not having excel installed on it. Tej Aj, http://opexsolution.com/forum/[^]