Importing data from Excel to database in Entity Framework

Question

I am new to model view controller and Entity Framework and I am designing a web application and I need to import data from Excel into a database.

I am having problem importing the data fields like date and I want that it take some values from my controller and some values from Excel.

For example if I have 3 fields (Name, product and date), then I want to import from Excel (Name and product) and it will take date by default in every row.

http://mvc4beginner.com/Sample-Code/ImportExportExcelData/MVC4-Import-ExcelData-to-Database.html

This is helping to import data from Excel but I am not able to take values of date and not able to give values by default.

public ActionResult Importexcel()
    {


        if (Request.Files[" fileupload1"].ContentLength > 0 && !string.IsNullOrEmpty(Request.Files["fileupload1"].FileName))
        {
            string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName);
            string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/UploadedFolder"), 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)\ProjectsV12;AttachDbFilename=|DataDirectory|\BlueWhaleDB.mdf;Initial Catalog=BlueWhaleDB;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";
            //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand("Select [Id],[ClientId],[InstituteId],[AdmissionId],[PreviousAdmissionId],[DateOfAdmission],[ClassId],[Class],[SectionId],[Section],[RollNumber],[FirstName],[MiddleName],[LastName],[GenderId],[DateOfBirth],[CategoryId],[StudentTypeId],[Session],[IsDeactivated],[DeactivatedDate],[ModifiedBy],[ModifiedDate] from [Student$]", excelConnection);

            excelConnection.Open();

            //OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
            //DataTable dReader = new DataTable();
            //DataSet objDataset1 = new DataSet();

            OleDbDataReader dReader;

            //for (int i = 0; i < dReader.Rows.Count; i++)
            //{
            //    new StudentViewModel
            //    {
            //        Id = dReader.Rows[i][1];

            //};



            //if (dReader.Rows[0][6].ToString() == DateTime.MinValue)
            //{
            //    dReader.Rows[i][6] = DateTime.Now;
            //}

            //else if (dReader.Rows[0][22].ToString() == "")
            //{
            //    dReader.Rows[i][22] = DateTime.Now;
            //}

            //else if (dReader.Rows[0][21].ToString() == "")
            //{
            //    dReader.Rows[i][21] = "System";
            //}

            //else if (dReader.Rows[0][19].ToString() != "")
            //{
            //    dReader.Rows[i][19] = false;
            //}
            dReader = cmd.ExecuteReader();

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

            // SQL Server Connection String


        }

        return RedirectToAction("Student");
    }

This is the code i am using please suggest me according to this code

Don't. ORMs like EF are not ETL tools. They deal with individual objects, not data sets. There are no objects or behaviour involved in importing data so there is no reason to use ORMs, or even classes. Use an ETL tool like SSIS, bcp or BULK INSERT commands to import the data. If you insist on using C#, use SqlBulkCopy to send data in bulk, using batches and minimal logging to the database. The difference in speed is several orders of magnitude, increasing with the size of the data set — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 1, 2016 at 8:41
PS there are a lot of duplicate questions and "Don't use an ORM" is always the anser — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 1, 2016 at 8:41
I am using SqlBulkCopy and as i said i am able to import data but i want to give some default values instead of importing all from excel for example i want that it import name and product from excel and it will take date as datetime.now. — Aman Goel
– Aman Goel, Commented Apr 1, 2016 at 8:48
That has nothing to do with ORMs. Either use SSIS and do the modifications there (you just add eg Derived Column, Replace Column steps froma toolbox), or in your code, eg in your data reader loop. How are you loading the data from Excel? How do you send the data to SqlBulkCopy ? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 1, 2016 at 8:51
i added some code below as i am new to development help me according to my code — Aman Goel
– Aman Goel, Commented Apr 1, 2016 at 8:58

Fanjo Lama · Accepted Answer · 2016-04-01 09:47:39Z

1

With the help of OleDbDataAdapter.

Below is the sample code.

Query = "Select [id],[Name],[Marks],[Grade] from [Sheet1$]";
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand(Query, excelCon);
//System.Data.OleDb.OleDbDataReader dReader;              
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, excelCon);
excelCon.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];

foreach (DataRow dr in Exceldt.Rows)
{
   ExampleModel NewModel= new ExampleModel ();
   //Pass values to model whatever your model is.
   //Add to your dbContext
   dbContext.tableName.Add(NewModel);
   dbContext.SaveChanges();
}
return RedirectToAction("Import");

edited Apr 1, 2016 at 9:47

answered Apr 1, 2016 at 9:42

Fanjo Lama

5813 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fanjo Lama Over a year ago

@AmanGoel Okay and handle the DateTime null value to DateTime.UtcNow

Collectives™ on Stack Overflow

Importing data from Excel to database in Entity Framework

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related