Moving data from an Excel file to a database

Question

I am new to MVC ASP.NET and trying to import data from an Excel file and load it into a database.

The database is already created with the column names that matches the information in the Excel file. When I upload the Excel file and click submit, I get the error:

format of the initialization string does not conform to specification

Based on the debug the fault lies in the following line and at present not even sure if rest of code is correct:

excelConnection.Open();

Looked up similar error issues but the answer doesn't work. Full code for this portion:

//Code at Controller and cshtml
public ActionResult Import()
    {
        return View();
    }

    public ActionResult ImportExcel()
    {
        try
        {
            if (Request.Files["FileUpload1"].ContentLength > 0)
            {
                string extension = Path.GetExtension(Request.Files["FileUpload1"].FileName);
                string path1 = string.Format("{0}/{1}", Server.MapPath("~/App_Data/uploads"), Request.Files["FileUpload1"].FileName);
                if (System.IO.File.Exists(path1))
                    System.IO.File.Delete(path1);

                Request.Files["FileUpload1"].SaveAs(path1);

                string sqlConnectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\ExampleDB.mdf;Initial Catalog=aspnet-FormulaOne-20151105055609;Integrated Security=True";

                //Create connection string to Excel work book
                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=Excel 12.0;Persist Security Info=False, HDR=YES";
                //Create Connection to Excel work book
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                //Create OleDbCommand to fetch data from Excel
                OleDbCommand cmd = new OleDbCommand("Select [Speed],[Average],[Power],[Comment] from [Sheet1$]", excelConnection);

                //ERROR OCCURING AT THIS LINE
                excelConnection.Open();
                OleDbDataReader dReader;
                dReader = cmd.ExecuteReader();

                SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlConnectionString);
                //Give your Destination table name
                sqlBulk.DestinationTableName = "Stats";
                sqlBulk.WriteToServer(dReader);
                excelConnection.Close();

                // SQL Server Connection String
            }

            return RedirectToAction("Import");
        }

        catch (Exception e)
        {
            Console.WriteLine(e.StackTrace.ToString());
            return RedirectToAction("Import");
        }
    }
 }

@using (Html.BeginForm("Importexcel", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <table>
        <tr><td>Excel file</td><td><input type="file" id="FileUpload1" name="FileUpload1" /></td></tr>
        <tr><td></td><td><input type="submit" id="Submit" name="Submit" value="Submit" /></td></tr>
    </table>
}

Answer 1

I think that you problem is that program is not capable to find excel file correctly , I reccomend using OleDbConnection

            //path to your file
            string path = @"D:\your\path\to\excel\file.xlsx";
            // noitice that parameter HRD=YES if your file has header
            string connStr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""", path);


            using (OleDbConnection connection = new OleDbConnection(connStr))
                {
                   connection.Open();
                   // ensure that sheet name is correct
                   OleDbCommand command = new OleDbCommand("select * from [sheet$]", connection);
                   using (OleDbDataReader dr = command.ExecuteReader())
                       {
                          // here you can access rows and insert them respectively
                          //first column , first row
                          var name = dr[0].toString();
                          //second column , first row
                          var lastname = dr[1].toString();
                          //here you can do anything with this variables (ex insert to db)
                       }

                }