How to Import Excel File into Database in ASP.NET MVC

How to Import Excel File into Database in ASP.NET MVC

If you want to Import Excel File into your database in ASP.NET MVC then this tutorial will teach you the fastest way to accomplish this. In this tutorial you will learn:

  • How to upload an Excel File to the Server with an input control of type=”file”.
  • How to Read the uploaded Excel File data with OleDbDataReader class.
  • How to Import Excel File’s data into a Database table with SqlBulkCopy class. This is the fastest method of importing excel’s data.

The Excel File Structure

You can Import Excel file that can have any number of rows and columns. Make sure you provide the names to each and every column of the excel file.

Here I am using an excel file that contains the sale report of a shop. It has 7 columns:

  • 1. Date – containing date values.
  • 2. Region – contains string.
  • 3. Person – contains string.
  • 4. Item – contains string.
  • 5. Units – contains values of type int.
  • 6. Unit Cost – contains decimal values.
  • 7. Total – contains decimal values.

You can download this excel file by clicking here.

Excel File structure

Creating Database Table

Based on the structure of my excel file, I have to create a database table into which the data of this excel file will be copied.

I name this table Sale and create its structure like this:

  • 1. Id – Primary key and identity column of type int.
  • 2. Region – varchar(25).
  • 3. Person – varchar(25).
  • 4. Item – varchar(25).
  • 5. Units – int.
  • 6. UnitCost – money.
  • 7. Total – money.
  • 8. AddedOn – date.
Notice that I used varchar columns for storing string values from excel and for decimal values I created columns of money datatype.

Download the Database Table here.

The Create table Query of my Sale table is:

CREATE TABLE [dbo].[Sale](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Region] [varchar](25) NOT NULL,
	[Person] [varchar](25) NOT NULL,
	[Item] [varchar](25) NOT NULL,
	[Units] [int] NOT NULL,
	[UnitCost] [money] NOT NULL,
	[Total] [money] NOT NULL,
	[AddedOn] [date] NOT NULL,
 CONSTRAINT [PK_Sale] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Note: After this tutorial learn how to do Export to Excel in ASP.NET MVC.

Step 1: Create Model

The model code is:

public class ImportExcel
{
    [Required(ErrorMessage = "Please select file")]
    [FileExt(Allow = ".xls,.xlsx", ErrorMessage = "Only excel file")]
    public HttpPostedFileBase file { get; set; }
}

The FileExt() is the Custom Validation attribute used for doing Server Side Validation of file upload control. Thus it will allow file upload control to upload only .xls and .xlsx files.

Step 2: Create FileExt() Class

Create a new class named FileExt and add this code to it:

public class FileExt : ValidationAttribute
{
    public string Allow;
    protected override ValidationResult IsValid(object value, ValidationContext validationContext)
    {
        if (value != null)
        {
            string extension = ((System.Web.HttpPostedFileBase)value).FileName.Split('.')[1];
            if (Allow.Contains(extension))
                return ValidationResult.Success;
            else
                return new ValidationResult(ErrorMessage);
        }
        else
            return ValidationResult.Success;
    }
}
Step 3: Create Controller

In the controller add Index Action of type post as shown below:

[HttpPost]
public ActionResult Index(ImportExcel importExcel)
{
    if (ModelState.IsValid)
    {
        string path = Server.MapPath("~/Content/Upload/" + importExcel.file.FileName);
        importExcel.file.SaveAs(path);

        string excelConnectionString = @"Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + path + "';Extended Properties='Excel 12.0 Xml;IMEX=1'";
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

        //Sheet Name
        excelConnection.Open();
        string tableName = excelConnection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();
        excelConnection.Close();
        //End

        OleDbCommand cmd = new OleDbCommand("Select * from [" + tableName + "]", excelConnection);

        excelConnection.Open();

        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["CS"].ConnectionString);

        //Give your Destination table name
        sqlBulk.DestinationTableName = "sale";

        //Mappings
        sqlBulk.ColumnMappings.Add("Date", "AddedOn");
        sqlBulk.ColumnMappings.Add("Region", "Region");
        sqlBulk.ColumnMappings.Add("Person", "Person");
        sqlBulk.ColumnMappings.Add("Item", "Item");
        sqlBulk.ColumnMappings.Add("Units", "Units");
        sqlBulk.ColumnMappings.Add("Unit Cost", "UnitCost");
        sqlBulk.ColumnMappings.Add("Total", "Total");

        sqlBulk.WriteToServer(dReader);
        excelConnection.Close();

        ViewBag.Result = "Successfully Imported";
    }
    return View();
}

Also add the following namespaces to the controller:

using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

Explanation:

  • The first procedure to Import Excel file is by uploading it into the “Upload” folder. Then a connection string is made using Microsoft.ACE.OLEDB.12.0 provider.
  • Once the Excel’s file table is fetched into tableName variable I am creating OleDbCommand object, passing to it the select Query and the connection string.
  • Then I am creating OleDbDataReader object that will read every record of the Excel file.
  • Next the Import Excel procedure starts, for this I create the SqlBulkCopy class object and sets the SQL Database Table name to its DestinationTableName property. Then through the sqlBulk.ColumnMappings.Add(SourceColumn,DesinationColumn), the column mappings are done.
  • Finally through .WriteToServe() function the Excel File’s Data is copied into the database table.
Step 4: Create View

Add the following code to the Index View:

<h4>@ViewBag.Result</h4>
@using (Html.BeginForm("Index", "ImportExcel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.TextBoxFor(m => m.file, new { type = "file" })
    <button id="submitButton" type="submit">Submit</button>
    @Html.ValidationMessageFor(model => model.file)
}
You can also create PDF Files from ASP.NET website. For this check my tutorial – How to Create a PDF file in ASP Net MVC using iTextSharp DLL.

Conclusion
Now Run the application and Import the Excel File. After a couple of second the excel records will be copied into the database table. You can check your database table to find the newly copied records.

Next: You can also check my tutorial How to Read Excel and show it in Grid with Paging.

excel records copied to database

DOWNLOAD

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "WordPress, SEO, jQuery, HTML" and more.