Import Data From Excel To SQL Server Database Using MVC Code First Approach

Pawan Tiwari

5y77.2k16100Article

16 Comments

Join the conversation! Your thoughts help the community grow.

Ivan Marin4y ago
Hi to all, how this work with the autoincremental identity?
Alexis Montas6y ago
Var workSheet = currentSheet.First(); i have an error in this code :System.InvalidOperationException: 'The sequence contains no elements'
- eda toper5y ago
  You can use this code: ExcelPackage.LicenseContext = LicenseContext.Commercial; Before: using (var package = new ExcelPackage(file.InputStream))...
Adaiah Mave6y ago
Buen ejemplo pero si por un error hay un dato repetido en el excel y la base como puedo mandar la condicion de que hay un dato repetido y mencione exactamente cual es el que ya se encuentra en la base?
- Pawan Tiwari6y ago
  Para verificar filas duplicadas puede usar "foreach (var item in usersList.distinct ())". Consulte este enlace para obtener m?s informaci?n: https://www.dotnetperls.com/duplicates
Parthiban Parthasarathy6y ago
May I know why we have put rowiterator =2 ? is there any specific reason ? pls advice
- Pawan Tiwari6y ago
  Yes Parthiban Parthasarathy As you can see excel sheet snapshot. Row 1 contains data heading not the actual data. So to eliminate row 1 and get actual data only I have started rowIterator from row 2.
Ricardo Lopez7y ago
'Object reference not set to an instance of an object.' appears because iterator get a null value from the row after the last, what should i do?
- Pawan Tiwari7y ago
  Its because of null values in excel row. You can visit the link for solution https://stackoverflow.com/questions/52117722/epplus-find-if-the-entire-row-is-empty-in-excel
minhal raffat7y ago
How to deal with sqlbulkcopy class if you need to insert bulk of data within no time. Thanks
- Pawan Tiwari7y ago
  Hi minhal raffat. You can try this article. https://www.c-sharpcorner.com/UploadFile/sourabh_mishra1/sqlbulkcopy-in-C-Sharp/
Alice Nguyen7y ago
Great article, thanks you so much
Alice Nguyen7y ago
Many thanks for your sharing
Former member9y ago
Category sahi kar le pahle. Sara kam kar raha hai MVC se or category select ki hai SQL server ki
- Pawan Tiwari9y ago
  Hi as your profile name shows how you are. Still I would like to thanks you to suggesting me to change the category. If you find any classes of learning how to communicate in professional manner, you must join and after that you should come here. And one more thing as I've selected invalid category of article, you have choosen invalid profile name. Be professional and enjoy coding.
Oyedepo Olufemi9y ago
Everything worked fine...

What is an Import feature?

Import feature lets the users save the data from excel sheets into the databases. This is useful when we have a requirement of entering bulk data into a database and there may be chances of less internet connectivity, which results in data loss. To prevent this data loss, we prefer to upload the data, using import from an Excel feature.

The other requirement of this feature is that most of the data entry operators have expertise in Excel sheets, which will result in faster data entry to any software.

There are three basic steps to implement this functionality.

Get the filled Excel sheet from the user machine.
Validate the data according to the Software Data Model.
Save validated data to the database.

Now, let us move on to implement the steps, using coding.

The very first step is to find a third-party library to read the content of Excel sheets. The list is given below of the reference libraries

We can find more reference libraries here

Here, I am going to explain how can we import the data into MVC models, using the EPPlus reference library. Here, I’m using Database Approach First. The steps are given below.

Step 1

Create database ExcelImportDB. Create table Users.

SQL script for step 1 is given below.

create database ExcelImportDB
Go
use ExcelImportDB
create table Users2(SNo int PRIMARY KEY NOT NULL, Name varchar(500), Age int)

Step 2

Create a new MVC Project ExcelImport.

Step 3

Add reference EPPlus reference library by executing the command given below.

Install-Package EPPlus

Step 4

Add new ADO.NET Entity Model, using the steps given below.

Right-click the Models folder under Solution Explorer. Click Add new Item and the Window was given below will open.

Name your entity as ExcelImportEntities. Click the Add button and the Window was given below will appear.

Click Next and the Window given below will appear.

Click New Connection and the Window given below will appear.

Enter your SQL Server credentials and click Test connection. The Window given below will appear.

Click OK in the test connection message and click OK in Connection Properties Window. The Window given below will appear.
This will save your connection string in config as ExcelImportDBEntities. Click Next and the Window given below will appear.

Click Finish. Now, your database is connected with your Application. Don’t forget to build a solution in this step.

Step 5

Go to Index.cshtml page and write the code given below to create a Web page upload, which will allow the users to upload Excel sheets on the Server.

@ {
ViewBag.Title = "Index";
}
@using(Html.BeginForm("Upload", "Home", FormMethod.Post, new {
enctype = "multipart/form-data"
})) { < table > < tr > < td > File: < /td> < td > < input type = "file"
name = "UploadedFile" / > < /td> < /tr> < tr > < td colspan = "2" > < input type = "submit"
name = "Submit"
value = "Submit" / > < /td> < /tr> < /table>
}

Step 6

Go to HomeContollers and write the Action method given below.

public ActionResult Upload(FormCollection formCollection) {
var usersList = new List < User > ();
if (Request != null) {
HttpPostedFileBase file = Request.Files["UploadedFile"];
if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName)) {
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
using(var package = new ExcelPackage(file.InputStream)) {
var currentSheet = package.Workbook.Worksheets;
var workSheet = currentSheet.First();
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++) {
var user = new User();
user.SNo = Convert.ToInt32(workSheet.Cells[rowIterator, 1].Value);
user.Name = workSheet.Cells[rowIterator, 2].Value.ToString();
user.Age = Convert.ToInt32(workSheet.Cells[rowIterator, 3].Value);
usersList.Add(user);
}
}
}
}
using(ExcelImportDBEntities excelImportDBEntities = new ExcelImportDBEntities()) {
foreach(var item in usersList) {
excelImportDBEntities.Users.Add(item);
}
excelImportDBEntities.SaveChanges();
}
return View("Index");
}

Step 7

Build your solution and run it. The Web page given below will open.

Step 8

Choose your Excel sheet from which you want to upload the data and click Submit. My Excel sheet screenshot is given below.

As soon as you click the Submit button, the data of an Excel sheet will be imported to your SQL Server. The screenshot of the SQL Server database is given after importing the data.

Now, you have successfully imported the data from an Excel sheet into the SQL Server database, using MVC Database First Approach.