How to upload the bulk excel sheet data into database with validation of excel sheet field?


Hi
In one project,there was requirement to upload the complete excel sheet data to database with following conditions
1. Excel field should not contain the special character like ‘XXXX’ or ‘-‘
2. It should dynamically read the excel sheet name
3. It should not hit more to database
4. Excel sheet will be 2003 or 2007

I did like this
Step1: Take file Upload, Button and label control. On click event write this code

Design page is like this

<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" /> &nbsp;&nbsp;&nbsp;&nbsp;
<asp:Button ID="BtnSubmit" runat="server" Text="Submit"
onclick="BtnSubmit_Click" />

<br />
<br />
<asp:Label ID="LblError" runat="server" Text=""></asp:Label>

</div>
</form>
</body>
</html>

Code behind default page is like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class Excel_Test_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
SaveExcel_Database();
}
protected void SaveExcel_Database()
{
try
{
if (FileUpload1.HasFile)
{
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

string path = FileUpload1.PostedFile.FileName;
ExcelUpload objbd = new ExcelUpload();

string flag = objbd.BudgetExcelUpload(Extension, path);

LblError.Text = flag;
}
else
{
LblError.Text = "Unable to upload the selected file. Please check the selected file path or confirm that the file is not blank!";
}
}
catch
{
LblError.Text = "Excel file is not in expected formate";
}

}

}

Step2: Create one class in App Code folder i.e ExcelUpload.cs
Write the method for getting excel sheet Name, Excel field validation and bulk excel insert to database like this
Note:Here in excel sheet I have taken the field I.e Id,EmpName,EmpSal
also create the same structure table in database i.e tblEmp

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ExcelUpload
/// </summary>
public class ExcelUpload
{

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True");

//This method is used to get the ExcelSheetNames
private string GetExcelSheetNames(string conStr)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
string aa = "";

try
{
objConn = new OleDbConnection(conStr);
// Open connection with the database.

objConn.Open();
// Get the data table containg the schema guid.

dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

// Add the sheet name to the string array.

foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}

// Loop through all of the sheets if you want too…

for (int j = 0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
aa = dt.Rows[0][2].ToString();
}

return aa;
}
catch (Exception ex)
{
return null;
}
finally
{
// Clean up.

if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}

public string BudgetExcelUpload(string Extesion, string path)
{
string msg="";

string Extension1 = Extesion;

string path1 = path;

string conStr = "";
switch (Extension1)
{
case ".xls": //Excel 97-03
conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + "; Extended Properties=\"Excel 8.0;IMEX=1\";";
break;
case ".xlsx": //Excel 07
conStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + "; Extended Properties=\"Excel 12.0;IMEX=1\";";
break;
}

OleDbConnection oconn = new OleDbConnection(conStr);
string worksheetName = GetExcelSheetNames(conStr);
OleDbCommand ocmd = new OleDbCommand("select * from [" + worksheetName + "]", oconn);

OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [" + worksheetName + "]", oconn);
System.Data.DataSet excelDataSet = new DataSet();
DataTable dt = new DataTable();
cmd.Fill(dt);
cmd.Fill(excelDataSet);

excelDataSet.AcceptChanges();

for (int i = 0; i < excelDataSet.Tables[0].Rows.Count; i++)
{
string Id =excelDataSet.Tables[0].Rows[i][0].ToString();
Id= CheckexcelData(Id);
if (Id == "Invalid")
{
msg = "Invalid";
break;
}
string EmpName = excelDataSet.Tables[0].Rows[i][1].ToString();

EmpName = CheckexcelData(EmpName);
if (EmpName == "Invalid")
{
msg = "Invalid";
break;
}
string EmpSal = excelDataSet.Tables[0].Rows[i][2].ToString();

EmpSal = CheckexcelData(EmpSal);

if (EmpSal == "Invalid")
{
msg = "Invalid";
break;
}
}

if (msg != "Invalid")
{
//code for bulk data insert in "tblEmp" table.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
con.Open();
bulkCopy.DestinationTableName = "tblEmp";
bulkCopy.WriteToServer(dt);
con.Close();
msg = "Data has been uploaded sucessfully";
}

}

return msg;

}

protected string CheckexcelData(string input)
{

switch (input)
{
case "xxxx":
input ="Invalid";
break;

case "-":
input = "Invalid";
break;
}
return input;
}

}

Advertisements

4 Responses to “How to upload the bulk excel sheet data into database with validation of excel sheet field?”

  1. anji Says:

    if 100 records are there , it wil enter all excel fields into data base table after 100 records it will taking nulls,
    so, how can i restrict only 100 records allow into data base.
    thanks

  2. Chandra Dev Says:

    Hi

    You write query for selecting top 100 records from excel, then insert into database. If you will get problem, let me know.

    Regards
    chandradev

  3. kiran Says:

    i want to upload the excel sheet bulk in the income tax return i want the posseger pls write clear

  4. Chandra Dev Says:

    Hi kiran

    do you want to upload sheet without validation? if yes then check this post

    http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel-to-sql-database.html


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: