importing excel data to database using asp.net and c #

Question

am getting the following error while trying to import data from excel to the database.

The Microsoft Office Access database engine could not find the object 'C:\Users\DAKTARI\Desktop\smarttable.xls'

this is my code behind that am using.

 public partial class Smarttable : 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=C:\Users\DAKTARI\Desktop\smarttable.xls;Extended Properties=""Excel 12.0 Xml;HDR=YES;""");
        Econ = new OleDbConnection(constr);

    }
    private void connection()
    {
        sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;
        con = new SqlConnection(sqlconn);

    }


    private void InsertExcelRecords(string FilePath)
    {
        ExcelConn(FilePath);

        Query = string.Format("Select [InvoiceNumber],[AmountPaid],[Remarks] FROM [C:\\Users\\DAKTARI\\Desktop\\smarttable.xls]", "Orders$");
        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 = "smarttable";
        //Mapping Table column    
        objbulk.ColumnMappings.Add("InvoiceNumber", "InvoiceNumber");
        objbulk.ColumnMappings.Add("AmountPaid", "AmountPaid");
        objbulk.ColumnMappings.Add("Remarks", "Remarks");
        //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);
    }
}

Is this a timing error i.e. is the file upload still in progress as you attempt to open it? — camelCase, Aug 25 '17 at 07:47
no it is not in progress..i select the file but upon the upload i get the error. — john kc, Aug 25 '17 at 08:35

Tetsuya Yamamoto · Answer 1 · 2017-08-28T08:05:15.827

1

Your Excel file format uses XLS which means for Office 2003 or earlier, but you're using ACE OLEDB provider which used for Office 2007 or later:

constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\DAKTARI\Desktop\smarttable.xls;Extended Properties=""Excel 12.0 Xml;HDR=YES;"");

The correct usage is using Jet 4.0 provider like this:

constr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'", FilePath);

Also you have second issue which a wrong query string is used to read the data inside worksheet:

Query = string.Format("Select [InvoiceNumber],[AmountPaid],[Remarks] FROM [C:\\Users\\DAKTARI\\Desktop\\smarttable.xls]", "Orders$");

This should be changed to proper form below:

Query = "SELECT [InvoiceNumber],[AmountPaid],[Remarks] FROM [Orders$]";

edited Aug 28 '17 at 08:05

answered Aug 25 '17 at 07:31

Tetsuya Yamamoto

24,297
8
39
61

thanks alot. am using excel 2016. i have corrected the ACE OLEDB to using jet 4.o but am now getting this error; could not find installable ISAM. – john kc Aug 25 '17 at 08:37
kindly assist me..its giving me a hell of time – john kc Aug 25 '17 at 08:38
Use single quotes for `Extended Properties` part instead of double ones, see the edited one (from https://stackoverflow.com/questions/512143/error-could-not-find-installable-isam). – Tetsuya Yamamoto Aug 25 '17 at 08:42
i have done that and now am getting this error. Format of the initialization string does not conform to specification starting at index 89. – john kc Aug 25 '17 at 08:52
kindly help me out am totally confused on this one – john kc Aug 25 '17 at 08:53
could my web.config also have a problem.. here is what i have on the web.config. – john kc Aug 25 '17 at 09:06
According to https://yoursandmyideas.com/2011/02/05/how-to-read-or-write-excel-file-using-ace-oledb-data-provider/, seems that the connection string is valid even using single or double quotes in `Extended Properties`. What target platform you've used (x86 or x64)? – Tetsuya Yamamoto Aug 25 '17 at 09:21
am using x64..i have not changed the target build platform but you can guide me on how to configure and check – john kc Aug 25 '17 at 09:54
Well, AFAIK JET OLEDB provider only work for x86 mode, so you need to set target build platform to x86 (see https://msdn.microsoft.com/en-us/library/kb4wyys2.aspx for Target CPU setting details). – Tetsuya Yamamoto Aug 25 '17 at 09:57
its still not working and giving me the same error "Format of the initialization string does not conform to specification starting at index 89" – john kc Aug 25 '17 at 10:14
i have changed my webconfig to this: – john kc Aug 25 '17 at 10:15
@johnkc Sorry for late reply. I edited with proper usage of `string.Format` (since you're using `FilePath` argument to supply Excel file path). – Tetsuya Yamamoto Aug 28 '17 at 08:06
thanks Tetsuya Yamamoto..kindly send me the edited code you did kindly..am not getting it..please you will have saved me alot.. – john kc Aug 28 '17 at 08:15

score 0 · Answer 2 · answered Aug 25 '17 at 07:26

Change this line

Query = string.Format("Select [InvoiceNumber],[AmountPaid],[Remarks] FROM [C:\\Users\\DAKTARI\\Desktop\\smarttable.xls]", "Orders$");

To

Query = "Select [InvoiceNumber],[AmountPaid],[Remarks] FROM [Orders$]";

The FROM needs to be followed by the Sheet Name which is treated like a table

importing excel data to database using asp.net and c #

2 Answers2