Import specified columns from Excel to SQL using Asp.net C# [Resolved]

Posted by Murugavelmsc under ASP.NET on 1/10/2016 | Points: 10 | Views : 1016 | Status : [Member] | Replies : 4
Hi Experts,

I have a created a employee tables with following fields
1. Empid - primary key
2. Name
3. Address
4. Dept
5 Salary
6. Qualification

I want to insert/update records from the excel files based on the empid.

For eg. in the excel files:
Empd, Name, Address

Only the above columns inserted/updated in the db.

Please help me.

Regards,
Murugavel S

Regards,
Murugavel S
murugavel.sadagopan@gmail.com
http://murugavelmsc.blogspot.in/



Responses

Posted by: Rajnilari2015 on: 1/10/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Murugavelmsc Sir, Since you tagged with Asp.net , So I am assuming that you are reading the excel data from your Server code like (C#/VB.net) and then you are passing the data to the database and performing a DML operation based on the "EmpID" column. If this assumption is correct, then the approach you can consider is

Step 1: Use "LinqToExcel" project (https://github.com/paulyoder/LinqToExcel ). You can use Nuget package manager to do so as
PM> Install-Package LinqToExcel 


Step 2: Once done , you can read the data from your excel sheet as shown under (an example)

void ReadEmployeesFromExcel()

{
string excelPath = @"D:\EmployeeRecords.xlsx";
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(excelPath);
var employees = (from a in excelFile.Worksheet(sheetName) select a).ToList();
employees.Foreach(e=>Console.WriteLine(e["EmpID"] +"-----" + e["EmpName"] + "-------" + e["Address"]));
}


Step 3: Assuming you have the SQL Connectivity done in your program(DAL layer through Ado.net Connectivity), you can pass the data to the SP and use Merge Statement (https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx ) to perform the DML operations.

OR If you are using ORM model (e.g EF), you can use the below code (a pseudocode) for your operation to execute.

public void UpsertEmployee(Employee emp)

{
var empRecord = enSEntities.tblEmployees.Where(e => e.EmpId == emp.EmpId).FirstOrDefault();
if (null != empRecord){
//case for updation
}else{
//case for insertion
}


Hope that helps.

Kindly let us know in case you have any concern for the same.

--
Thanks & Regards,
RNA Team

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 1/10/2016 [Administrator] HonoraryPlatinum | Points: 25

Up
1
Down
You can connect and read from MS Excel file using OledbConnection as described in http://www.dotnetfunda.com/articles/show/1954/how-to-read-ms-excel-file-and-populate-into-gridview.

After that you can use the SQL select and where clause to filter the excel data and then read it and insert into the SQL Server database.

Hope this helps.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 1/11/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
@Murugavelmsc Sir, Glad that it helped.

--
Thanks & Regards,
RNA Team

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajeshatkiit on: 1/10/2016 [Member] Starter | Points: 25

Up
-1
Down
Hi
Please refer these url for code and more details.
http://www.encodedna.com/2013/01/asp.net-import-from-excel.htm
http://www.dotnetfox.com/articles/import-records-from-excel-sheet-to-sql-server-using-Asp-Net-and-C-Sharp-1029.aspx
http://www.dotnetfox.com/articles/import-records-from-excel-sheet-to-sql-server-with-validations-in-Asp-Net-using-C-Sharp-1052.aspx
http://www.codeproject.com/Questions/715127/Import-Data-from-Excel-to-Sql-Server-in-ASP-N

Murugavelmsc, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response