Click here to Skip to main content
11,648,094 members (70,609 online)
Click here to Skip to main content

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

, 1 Apr 2014 CPOL 80.3K 8K 19
Rate this:
Please Sign up or sign in to vote.
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:

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 ImportToExceland whatever you like. Create a controller named HomeControllerand in this controller, create an Action Result method named Index.

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

Now, create a view, right click on the Indexactionmethod 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.

@{
    ViewBag.Title = "Index";
}

<h2>
    Index</h2>

Now create httppostmethod for Index.cshtml page for get uploaded file on controller. Now write the code for read uploaded file. Here I’m using the OledbConnectionto 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:

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:

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 httppostmethod:

[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.

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

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)

Share

About the Author

Yogesh Kumar Tyagi
Software Developer Pure Diets India Limited
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionHow do I display the datatables? Pin
Member 118259499-Jul-15 23:26
memberMember 118259499-Jul-15 23:26 
AnswerRe: How do I display the datatables? Pin
Yogesh Kumar Tyagi12-Jul-15 19:10
professionalYogesh Kumar Tyagi12-Jul-15 19:10 
BugFile being locked Pin
yagzstillfree1-Jul-15 5:00
memberyagzstillfree1-Jul-15 5:00 
GeneralRe: File being locked Pin
Yogesh Kumar Tyagi1-Jul-15 19:41
professionalYogesh Kumar Tyagi1-Jul-15 19:41 
GeneralRe: File being locked Pin
yagzstillfree2-Jul-15 0:41
memberyagzstillfree2-Jul-15 0:41 
QuestionClosing Excel Connections Pin
Paul Goings24-Jun-15 9:20
memberPaul Goings24-Jun-15 9:20 
AnswerRe: Closing Excel Connections Pin
Yogesh Kumar Tyagi25-Jun-15 20:03
professionalYogesh Kumar Tyagi25-Jun-15 20:03 
QuestionImportToXLS / Only one row Pin
Member 109998785-Mar-15 19:52
memberMember 109998785-Mar-15 19:52 
AnswerRe: ImportToXLS / Only one row Pin
Yogesh Kumar Tyagi10-Mar-15 21:35
professionalYogesh Kumar Tyagi10-Mar-15 21:35 
QuestionError. Please help Pin
Member 114299585-Feb-15 4:01
memberMember 114299585-Feb-15 4:01 
AnswerRe: Error. Please help Pin
Yogesh Kumar Tyagi9-Feb-15 6:15
professionalYogesh Kumar Tyagi9-Feb-15 6:15 
QuestionExcel Reading web application Pin
shibin suresh11-Jan-15 22:24
membershibin suresh11-Jan-15 22:24 
AnswerRe: Excel Reading web application Pin
shibin suresh11-Jan-15 22:25
membershibin suresh11-Jan-15 22:25 
Questionerror: Object reference set to null Pin
Member 1131692316-Dec-14 22:31
memberMember 1131692316-Dec-14 22:31 
AnswerRe: error: Object reference set to null Pin
Yogesh Kumar Tyagi17-Dec-14 0:08
professionalYogesh Kumar Tyagi17-Dec-14 0:08 
QuestionHow to call this function from index,aspx file in a button click? Pin
Member 112409218-Dec-14 19:44
memberMember 112409218-Dec-14 19:44 
AnswerRe: How to call this function from index,aspx file in a button click? Pin
Yogesh Kumar Tyagi9-Dec-14 19:30
professionalYogesh Kumar Tyagi9-Dec-14 19:30 
QuestionKeyword not supported: 'metadata'. Pin
jose LL6-Nov-14 5:53
memberjose LL6-Nov-14 5:53 
AnswerRe: Keyword not supported: 'metadata'. Pin
Yogesh Kumar Tyagi6-Nov-14 17:18
professionalYogesh Kumar Tyagi6-Nov-14 17:18 
GeneralRe: Keyword not supported: 'metadata'. Pin
jose LL6-Nov-14 19:24
memberjose LL6-Nov-14 19:24 
GeneralRe: Keyword not supported: 'metadata'. Pin
Yogesh Kumar Tyagi6-Nov-14 22:06
professionalYogesh Kumar Tyagi6-Nov-14 22:06 
GeneralRe: Keyword not supported: 'metadata'. Pin
jose LL6-Nov-14 22:56
memberjose LL6-Nov-14 22:56 
GeneralRe: Keyword not supported: 'metadata'. Pin
Yogesh Kumar Tyagi7-Nov-14 17:38
professionalYogesh Kumar Tyagi7-Nov-14 17:38 
GeneralRe: Keyword not supported: 'metadata'. Pin
jose LL9-Nov-14 11:06
memberjose LL9-Nov-14 11:06 
GeneralRe: Keyword not supported: 'metadata'. Pin
Yogesh Kumar Tyagi9-Nov-14 19:30
professionalYogesh Kumar Tyagi9-Nov-14 19:30 
GeneralRe: Keyword not supported: 'metadata'. Pin
jose LL10-Nov-14 8:13
memberjose LL10-Nov-14 8:13 
GeneralRe: Keyword not supported: 'metadata'. Pin
jose LL10-Nov-14 8:15
memberjose LL10-Nov-14 8:15 
QuestionThanx Pin
Zaheer A.M.23-Sep-14 22:11
professionalZaheer A.M.23-Sep-14 22:11 
QuestionOverwriting Table Pin
Member 1044567617-Sep-14 5:06
memberMember 1044567617-Sep-14 5:06 
GeneralMy vote of 3 Pin
balajist0716-Sep-14 1:03
memberbalajist0716-Sep-14 1:03 
QuestionError msg i got Pin
Member 1021351312-Aug-14 23:55
memberMember 1021351312-Aug-14 23:55 
AnswerRe: Error msg i got Pin
Member 1021351313-Aug-14 1:20
memberMember 1021351313-Aug-14 1:20 
GeneralRe: Error msg i got Pin
Yogesh Kumar Tyagi13-Aug-14 2:03
professionalYogesh Kumar Tyagi13-Aug-14 2:03 
GeneralRe: Error msg i got Pin
Member 1021351313-Aug-14 4:55
memberMember 1021351313-Aug-14 4:55 
GeneralRe: Error msg i got Pin
Yogesh Kumar Tyagi13-Aug-14 18:06
professionalYogesh Kumar Tyagi13-Aug-14 18:06 
AnswerRe: Error msg i got Pin
Yogesh Kumar Tyagi13-Aug-14 2:00
professionalYogesh Kumar Tyagi13-Aug-14 2:00 
QuestionNotSupportedException Pin
AndyKay0111-Aug-14 21:21
memberAndyKay0111-Aug-14 21:21 
AnswerRe: NotSupportedException Pin
Yogesh Kumar Tyagi12-Aug-14 3:30
professionalYogesh Kumar Tyagi12-Aug-14 3:30 
GeneralRe: NotSupportedException Pin
AndyKay0112-Aug-14 5:38
memberAndyKay0112-Aug-14 5:38 
GeneralRe: NotSupportedException Pin
Yogesh Kumar Tyagi12-Aug-14 18:32
professionalYogesh Kumar Tyagi12-Aug-14 18:32 
Questionwhat if table in database is not empty Pin
jabee1241223-Jul-14 20:06
memberjabee1241223-Jul-14 20:06 
AnswerRe: what if table in database is not empty Pin
Yogesh Kumar Tyagi24-Jul-14 0:50
professionalYogesh Kumar Tyagi24-Jul-14 0:50 
GeneralRe: what if table in database is not empty Pin
jabee1241227-Jul-14 18:13
memberjabee1241227-Jul-14 18:13 
GeneralRe: what if table in database is not empty Pin
Yogesh Kumar Tyagi27-Jul-14 18:27
professionalYogesh Kumar Tyagi27-Jul-14 18:27 
GeneralRe: what if table in database is not empty Pin
jabee1241227-Jul-14 18:35
memberjabee1241227-Jul-14 18:35 
GeneralRe: what if table in database is not empty Pin
Yogesh Kumar Tyagi27-Jul-14 18:41
professionalYogesh Kumar Tyagi27-Jul-14 18:41 
QuestionMy Vote of 5* Pin
Developer Rahul Sharma23-Jul-14 18:31
professionalDeveloper Rahul Sharma23-Jul-14 18:31 
AnswerRe: My Vote of 5* Pin
Yogesh Kumar Tyagi23-Jul-14 18:38
professionalYogesh Kumar Tyagi23-Jul-14 18:38 
Questionwhen the value in the column contains "-" , it is not uploading to database Pin
Member 104451527-Jun-14 7:02
memberMember 104451527-Jun-14 7:02 
QuestionWorks Great Locally, Deployed to Azure and Doesn't work. Pin
Member 1080516015-May-14 5:27
memberMember 1080516015-May-14 5:27 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150804.4 | Last Updated 1 Apr 2014
Article Copyright 2014 by Yogesh Kumar Tyagi
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid