Click here to Skip to main content
15,868,016 members
Articles / Web Development / ASP.NET
Tip/Trick

Import Data from Excel File to Database Table in ASP.NET MVC 4

Rate me:
Please Sign up or sign in to vote.
4.94/5 (31 votes)
1 Apr 2014CPOL2 min read 627.3K   20.4K   29   86
This tip introduces how to Import data From Excel file to Data Table in ASP.NET MVC4

Introduction

My previous article discussed about how to Export data from database table to Excel file. Now in this article, I’ve covered a brief introduction about importing data from Excel File to database. There are lots of ways for Importing data from Excel to SQL server database, and here I’m going to introduce one simple common method to import data into data table.

Using the Code

To start this task, you need to create a database for storing data in data table. The design of database table looks like the following:

Image 1

First of all, open Visual Studio 2012. After that, select new project and click on ASP.NET MVC4 Web Application in Visual C#, name the project ImportToExcel and whatever you like. Create a controller named HomeController and in this controller, create an Action Result method named Index.

C#
   public ActionResult Index()
{
    return View();
}

Now, create a view, right click on the Indexaction method and select Add View and then click OK. Add a file uploader control in Index.cshtml page for upload Excel file or write the following code to the view for display data.

C#
 @{
    ViewBag.Title = "Index";
}

<h2>
    Index</h2>
@using (Html.BeginForm("Index","Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{ 
<input type="file" name="file" />
    <input type="submit" value="OK" />
}  

Now create httppost method for Index.cshtml page for get uploaded file on controller. Now write the code for read uploaded file. Here I’m using the OledbConnection to connect to the Excel Sheet. There are two types of connection strings for Excel file fist for”.xls” file and second is “.xlsx” file.

Write the connection string for “.xls” file:

C#
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
   fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

And the connection sting for “.xlsx” file is:

C#
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
  fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

Now get the data from Excel file and insert it into a DataTable. After that, insert DataTable to database or write the following code in the httppost method:

C#
[HttpPost]
      public ActionResult Index(HttpPostedFileBase file)
      {
          DataSet ds = new DataSet();
          if (Request.Files["file"].ContentLength > 0)
          {
              string fileExtension =
                                   System.IO.Path.GetExtension(Request.Files["file"].FileName);

              if (fileExtension == ".xls" || fileExtension == ".xlsx")
              {
                  string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
                  if (System.IO.File.Exists(fileLocation))
                  {

                      System.IO.File.Delete(fileLocation);
                  }
                  Request.Files["file"].SaveAs(fileLocation);
                  string excelConnectionString = string.Empty;
                  excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                  fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                  //connection String for xls file format.
                  if (fileExtension == ".xls")
                  {
                      excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                      fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
                  }
                  //connection String for xlsx file format.
                  else if (fileExtension == ".xlsx")
                  {
                      excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                      fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                  }
                  //Create Connection to Excel work book and add oledb namespace
                  OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                  excelConnection.Open();
                  DataTable dt = new DataTable();

                  dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                  if (dt == null)
                  {
                      return null;
                  }

                  String[] excelSheets = new String[dt.Rows.Count];
                  int t = 0;
                  //excel data saves in temp file here.
                  foreach (DataRow row in dt.Rows)
                  {
                      excelSheets[t] = row["TABLE_NAME"].ToString();
                      t++;
                  }
                  OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);


                  string query = string.Format("Select * from [{0}]", excelSheets[0]);
                  using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
                  {
                      dataAdapter.Fill(ds);
                  }
              }
              if (fileExtension.ToString().ToLower().Equals(".xml"))
              {
                  string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
                  if (System.IO.File.Exists(fileLocation))
                  {
                      System.IO.File.Delete(fileLocation);
                  }

                  Request.Files["FileUpload"].SaveAs(fileLocation);
                  XmlTextReader xmlreader = new XmlTextReader(fileLocation);
                  // DataSet ds = new DataSet();
                  ds.ReadXml(xmlreader);
                  xmlreader.Close();
              }

              for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
              {
                  string conn = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
                  SqlConnection con = new SqlConnection(conn);
                  string query = "Insert into Person(Name,Email,Mobile) Values('" +
                  ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() +
                  "','" + ds.Tables[0].Rows[i][2].ToString() + "')";
                  con.Open();
                  SqlCommand cmd = new SqlCommand(query, con);
                  cmd.ExecuteNonQuery();
                  con.Close();
              }
          }
          return View();
      }

Now build and run your application.

Image 2

Chose an Excel file for import data in database. Ensure that your database table columns and Excel file columns should be the same.

Image 3

Image 4

Click on “OK” button for upload file. If you have any issues and queries, then feel free to contact me.

History

  • 31st March, 2014: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Pure Diets India Limited
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionError Problem Pin
Member 1462257514-Oct-19 8:12
Member 1462257514-Oct-19 8:12 
Questionstoring the data from excel to Database Pin
Member 137148627-Mar-18 20:30
Member 137148627-Mar-18 20:30 
QuestionObject reference not set to an instance of an object Pin
Tony Girgenti14-Sep-17 9:42
Tony Girgenti14-Sep-17 9:42 
GeneralHow to import all the work sheets? Pin
KEERTHISREENU18-Jan-17 20:14
KEERTHISREENU18-Jan-17 20:14 
GeneralRe: How to import all the work sheets? Pin
Yogesh Kumar Tyagi5-Feb-17 22:19
professionalYogesh Kumar Tyagi5-Feb-17 22:19 
QuestionImport excel file to database in c# mvc Pin
Member 1283647718-Nov-16 0:05
Member 1283647718-Nov-16 0:05 
QuestionTrouble in connection string Pin
kenshinm7-Oct-16 1:18
kenshinm7-Oct-16 1:18 
AnswerRe: Trouble in connection string Pin
Yogesh Kumar Tyagi11-Oct-16 22:14
professionalYogesh Kumar Tyagi11-Oct-16 22:14 
GeneralRe: Trouble in connection string Pin
kenshinm16-Mar-22 15:53
kenshinm16-Mar-22 15:53 
GeneralRe: Trouble in connection string Pin
kenshinm16-Mar-22 15:54
kenshinm16-Mar-22 15:54 
QuestionSupporting data types other than string Pin
Member 37634462-Oct-16 19:13
Member 37634462-Oct-16 19:13 
AnswerRe: Supporting data types other than string Pin
Yogesh Kumar Tyagi11-Oct-16 22:09
professionalYogesh Kumar Tyagi11-Oct-16 22:09 
QuestionImportToExcel Pin
Navnath Ugale18-Sep-16 0:57
Navnath Ugale18-Sep-16 0:57 
Questionerror stackoverflowexception when upload file Pin
afiqdoherty5-Sep-16 23:04
afiqdoherty5-Sep-16 23:04 
AnswerRe: error stackoverflowexception when upload file Pin
Yogesh Kumar Tyagi6-Sep-16 22:20
professionalYogesh Kumar Tyagi6-Sep-16 22:20 
GeneralRe: error stackoverflowexception when upload file Pin
afiqdoherty6-Sep-16 22:25
afiqdoherty6-Sep-16 22:25 
Questionhow to get data from excel on asp.net web application. Pin
Vibhusha Devani27-Jun-16 1:41
Vibhusha Devani27-Jun-16 1:41 
AnswerRe: how to get data from excel on asp.net web application. Pin
Yogesh Kumar Tyagi1-Sep-16 4:01
professionalYogesh Kumar Tyagi1-Sep-16 4:01 
QuestionRead Multiple Sheets in Excel Pin
Sciclops16-May-16 4:33
Sciclops16-May-16 4:33 
AnswerRe: Read Multiple Sheets in Excel Pin
Yogesh Kumar Tyagi17-May-16 2:59
professionalYogesh Kumar Tyagi17-May-16 2:59 
GeneralRe: Read Multiple Sheets in Excel Pin
Sciclops20-May-16 5:29
Sciclops20-May-16 5:29 
GeneralRe: Read Multiple Sheets in Excel Pin
Yogesh Kumar Tyagi31-May-16 3:33
professionalYogesh Kumar Tyagi31-May-16 3:33 
QuestionError after publish and deploy on iis server Pin
Member 119419229-May-16 2:17
Member 119419229-May-16 2:17 
Questiontable with primary key Pin
afiqdoherty6-Dec-15 21:16
afiqdoherty6-Dec-15 21:16 
AnswerRe: table with primary key Pin
Yogesh Kumar Tyagi6-Dec-15 23:17
professionalYogesh Kumar Tyagi6-Dec-15 23:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.