How to import data from Excel into SQL Server Express 2008 in MVC3

Question

I'm using MVC3 with C# code. I have a table in my SQL Server Express containing some columns, and I have an Excel sheet which has the same number of columns with the same datatypes and names.

My requirement is I want to browse that Excel file in my MVC3 application, where the user can select the file. There is a RegistrationNo column in both my database table as well as in the Excel sheet. Before importing the data in the table of the database the RegNo present in the Excel sheet should be compared with the RegNo in the database table and if that RegNo already exists than no insertion for that RegNO should take place else if that RegNo is not present in the table than the row for that RegNo should be inserted.

Below is my code that I have tried but I'm getting lot of issues with that.

[HttpPost]
public ActionResult AdmissionUpload()
{
    string filePath = null;
    foreach (string inputTagName in Request.Files)
    {
       HttpPostedFileBase Infile = Request.Files[inputTagName];      
       if (Infile.ContentLength > 0 && (Path.GetExtension(Infile.FileName) == ".xls" || Path.GetExtension(Infile.FileName) == ".xlsx" || Path.GetExtension(Infile.FileName) == ".xlsm"))
       {
          filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,
                        Path.GetFileName(Infile.FileName));
          if (System.IO.File.Exists(filePath))
          {
             System.IO.File.Delete(filePath);
          }
          Infile.SaveAs(filePath);
          //Infile.SaveAs(filePath); 
       }

       if (filePath != null)
       {
          System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath.ToString() + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
          oconn.Open();

          try
          {
             if (oconn.State == System.Data.ConnectionState.Closed)
                oconn.Open();
          }
          catch (Exception ex)
          {
             // MessageBox.Show(ex.Message);
          }

          dynamic myTableName = oconn.GetSchema("Tables").Rows[0]["TABLE_NAME"];
          OleDbCommand ocmd = new OleDbCommand("select * from [" + myTableName + "]", oconn);
          OleDbDataReader odr = ocmd.ExecuteReader();

          if (odr.HasRows)
          {
             while (odr.Read())
             {
                 if (odr[0].ToString().Trim() != "")
                 {
                    if (CheckDepartment(odr[0].ToString().Trim()) == false)
                    {
                       var model = new DepartmentMaster();
                       model.DepartmentName = odr[1].ToString().Trim();
                       db.DepartmentMasters.AddObject(model);
                       db.SaveChanges();
                       FLAG = true;
                    }
                 }
              }
          }
       }
   }
   return View();
}

Here CheckRegNo checks whether the RegNo exists.

Can you be **a bit more specific** about **WHAT** the issues/errors are??? — marc_s, Nov 05 '11 at 11:19
foreach (string inputTagName in Request.Files) {} When i debug and see what the issue is its just not moving inside the code of this for loop.Its checking the file request and then straightway going on return View(). — Sam M, Nov 05 '11 at 11:32

score 5 · Accepted Answer · answered Jan 18 '12 at 09:34

dynamic myTableName = oconn.GetSchema("Tables").Rows[0]["TABLE_NAME"];
OleDbCommand ocmd = new OleDbCommand("select * from [" + myTableName + "]", oconn);
OleDbDataReader odr = ocmd.ExecuteReader();
if (odr.HasRows)
{
     while (odr.Read())
 {
     var model = new Student();
     model.Col1=Convert.ToInt32(odr[0]);
     model.Col2 = odr[1].ToString().Trim();
     model.col3 = odr[2].ToString().Trim();
     model.col4 = odr[3].ToString().Trim();
    db.MyTable.AddObject(model);                            
 }
}

This is how im reading the Excel and saving data from Excel.

score 0 · Answer 2 · answered Jul 21 '17 at 04:29

We can also import using excel library. Below is code for it.

            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;

            int rCnt;
            int cCnt;
            int rw = 0;
            int cl = 0;
            string str;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;
            rw = range.Rows.Count;
            cl = range.Columns.Count;

            for (rCnt = 1; rCnt <= rw; rCnt++)
            {
                //Ignore first row as it consists of headers
                if (rCnt > 1)
                {
                    for (cCnt = 1; cCnt <= rw; cCnt++)
                    {
                        str = range.Value2[rCnt, cCnt];
                    }
                }
            }

How to import data from Excel into SQL Server Express 2008 in MVC3

2 Answers2