Import Data from Excel and CSV to SQL server using C# in Asp.net

Category: ASP.NETC#.Net Comments: 4 comments

The Page having a FileUpload control and the Upload button, on selecting the Excel or CSV  file user needs to click on Upload button to store the data to Server. Here we are treating the uploaded file as database hence we need to create OLEDB connection to this file, from this connection will be created and the data is fetched to C# as DataTable. ‘[Sheet1$]‘ is the Name of the Worksheet where requires data is present.

Table

CREATE TABLE [dbo].[Tag](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TagType] [int] NULL,
[TagCode] [nvarchar](100) NULL,
[Status] [bit] NULL,
[Duration] [int] NULL)

Stored Procedure

Create proc [dbo].[ExcelTag]
@TagType int,
@TagCode nvarchar(100),
@Status bit,
@Duration int
as
if Not exists(Select top 1 TagCode from dbo.[Tag] where TagCode=@TagCode)
Begin
insert into dbo.[Tag](TagType,TagCode,[Status],Duration)values(@TagType,@TagCode,@Status,@Duration)
End

Design your aspx page like this

<form id=”form1″ runat=”server”>

<div>
<table>
<tr>
<td style=”vertical-align:top”>
<asp:Label id=”lblupload” cssclass=”SubHead” runat=”server” Text=”Upload” />
</td>
<td>
<asp:FileUpload ID=”Fupload” runat=”server” /><br />
<asp:Label ID=”lblextMsg” ForeColor=”Red” CssClass=”NormalRed” runat=”server”></asp:Label>
</td>
</tr>
<tr><td colspan=”2″>
<asp:Button ID=”btnUpload” runat=”server” Text=”Upload” OnClientClick=”return ValidateFile()”
onclick=”btnUpload_Click” />
<br />
<br />
<asp:Literal ID=”LtrlFinalMessage” runat=”server”></asp:Literal>
</td></tr>
</table>
</div>
</form>

Validation on File Upload Control using Jquery

<script type=”text/javascript”>
//Jquery Validation for Upload File
var validFilesTypes = ["xls", "csv"];
function ValidateFile() {
debugger;
var file = document.getElementById(“<%=Fupload.ClientID%>”);
var lblextMsg = document.getElementById(“<%=lblextMsg.ClientID%>”);
var path = file.value;
var ext = path.substring(path.lastIndexOf(“.”) + 1, path.length).toLowerCase();
var isValidFile = false;
for (var i = 0; i < validFilesTypes.length; i++) {
if (ext == validFilesTypes[i]) {
isValidFile = true;
break;
}
}
if (!isValidFile) {
lblextMsg.style.color = “red”;
lblextMsg.innerHTML = “Please upload a File with” + ” extension:\n\n” + validFilesTypes.join(“, “);
}
return isValidFile;
}
</script>

validation

 

 

 

 

After that write the following code in Codebehind Button Click

FuploadInfo finfo = new FuploadInfo();
SqlConnection con = new SqlConnection(“Data Source=.\\SQLExpress;Database=Practise;Integrated Security=true;”);
protected void Page_Load(object sender, EventArgs e)
{
}
public void SaveFileContent()
{
string path = Fupload.PostedFile.FileName;
string extension = Path.GetExtension(path);
switch (extension)
{
case “.xls”:
SaveDataXLS();
break;
case “.csv”:
SaveDataCSV();
break;
}
}
public void SaveDataCSV()
{
if (Fupload.HasFile)
{

//upload = Folder Name

FileInfo f = new FileInfo(Server.MapPath(“~/upload/”) + Fupload.FileName.ToString());
DataTable dts = new DataTable();
Fupload.SaveAs(Server.MapPath(“~/upload/”) + Fupload.FileName.ToString());
string[] Lines = File.ReadAllLines(f.FullName);
string[] Fields;
Fields = Lines[0].Split(new char[] { ‘,’ });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
{
dt.Columns.Add(Fields[i].ToLower(), typeof(string));
}
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
Fields = Lines[i].Split(new char[] { ‘,’ });
Row = dt.NewRow();
for (int j = 0; j < Cols; j++)
Row[j] = Fields[j];
dt.Rows.Add(Row);
}
foreach (DataRow dr in dt.Rows)
{
if (dr["TagType"].ToString().ToLower() == “free”)
{
finfo.TagType = 1;
}
else if (dr["TagType"].ToString().ToLower() == “paid”)
{
finfo.TagType = 2;
}
else if (dr["TagType"].ToString().ToLower() == “unpaid”)
{
finfo.TagType = 3;
}
else
{
lblextMsg.Text = “TagType fields is not in correct format.”;
lblextMsg.Visible = true;
break;
}
if (dr["Duration"] != string.Empty || dr["Duration"] != “”)
finfo.ExpirationDate = Convert.ToString(dr["Duration"]);
else
{
lblextMsg.Text = “Duration should be integer value.”;
lblextMsg.Visible = true;
break;
}
finfo.TagCode = dr["TagCode"].ToString();
finfo.Status = 0;
InsertedInfo(finfo);
lblextMsg.Visible = false;
}
}
}
public void SaveDataXLS()
{
if (Fupload.HasFile)
{

DataTable dts = new DataTable();

//upload = Folder Name

Fupload.SaveAs(Server.MapPath(“~/upload/”) + Fupload.FileName.ToString());
string ExcelFilePathName = Server.MapPath(“~/upload/”) + Fupload.FileName;

DataTable dts1 = new DataTable();
OleDbConnection con = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + ExcelFilePathName + “;Extended Properties=’Excel 8.0;HDR=Yes;’”);
DbCommand command = con.CreateCommand();
OleDbDataAdapter da = new OleDbDataAdapter(“select * from [Sheet1$]“, con);
con.Open();
DataSet ds = new DataSet();
da.Fill(dts);
if (dts.Rows.Count > 0)
{
foreach (DataRow dr in dts.Rows)
{
if (dr["TagType"].ToString().ToLower() == “free”)
{
finfo.TagType = 1;
}
else if (dr["TagType"].ToString().ToLower() == “paid”)
{
finfo.TagType = 2;
}
else if (dr["TagType"].ToString().ToLower() == “unpaid”)
{
finfo.TagType = 3;
}
else
{
lblextMsg.Text = “TagType fields is not in currect formate”;
lblextMsg.Visible = true;
break;
}

if (dr["Duration"] != string.Empty || dr["Duration"] != “”)
finfo.ExpirationDate = Convert.ToString(dr["Duration"]);
else
{
lblextMsg.Text = “Duration should be integer value.”;
lblextMsg.Visible = true;
break;
}
finfo.TagCode = dr["TagCode"].ToString();
finfo.Status = 0;
InsertedInfo(finfo);
lblextMsg.Visible = false;
}

}
}
}
void showFinalmessage()
{
int ins = Convert.ToInt32(ViewState["INSERTEFROWS"]),
fail = Convert.ToInt32(ViewState["FAILEDFROWS"]);

LtrlFinalMessage.Text = String.Format(“Inserted Successfull:<b>{0}</b> ” +
” <br> Duplicate found:<b>{1}</b> <br> Out of Total:<b>{2}</b>”,
ins.ToString(), fail.ToString(), (ins + fail).ToString());
}
void InsertedInfo(FuploadInfo finfo)
{
if (AddFileToTable(finfo) > 0)
{
ViewState["INSERTEFROWS"] = Convert.ToInt32(ViewState["INSERTEFROWS"]) + 1;
}
else
{
ViewState["FAILEDFROWS"] = Convert.ToInt32(ViewState["FAILEDFROWS"]) + 1;
}
}
protected int AddFileToTable(FuploadInfo finfo)
{
con.Open();
SqlCommand cmd = new SqlCommand(“ExcelTag”, con);
cmd.Parameters.AddWithValue(“@TagType”, finfo.TagType);
cmd.Parameters.AddWithValue(“@TagCode”, finfo.TagCode);
cmd.Parameters.AddWithValue(“@Status”, finfo.Status);
cmd.Parameters.AddWithValue(“@Duration”, finfo.ExpirationDate);
cmd.CommandType = CommandType.StoredProcedure;
int res = cmd.ExecuteNonQuery();
con.Close();
return res;
}
protected void btnUpload_Click(object sender, EventArgs e)
{
ViewState["INSERTEFROWS"] = 0;
ViewState["FAILEDFROWS"] = 0;
SaveFileContent();
showFinalmessage();
}

Class

public class FuploadInfo
{

public string _expirationDate;
public int _Status;
public string _TagCode;
public int _TagType;

public string ExpirationDate { get; set; }
public int Status { get; set; }
public string TagCode { get; set; }
public int TagType { get; set; }
}

Success:

success

 

 

 

 

 

 

Duplicate :

duplicate

 

 

 

 

 

 

 

Success and Duplicate:

dup&suc

 

 

 

 

 

 

 

Excel Image:

excel

 

 

 

 

 

 

 

 

 

 

CSV Image:

csv

 

 

 

 

 

Download Code: Import Excel and Csv

Tags:  

4 comments on “Import Data from Excel and CSV to SQL server using C# in Asp.net

  1. avinash on said:

    Dear sir,
    it is very helpful but i need to know to validate date in excel sheet while uploading the excel sheet in asp.net c#

    • Abdul Khursheed on said:

      Hi Avinash,

      First you need to specify the format of datetime in excel sheet and then you validate the date format.

      Thanks

      • avinash on said:

        Sir it is only checking the first row blank value if i put
        if(dr[\"Duration\"].ToString()==”\";
        but in second row if it is blank then its not checking and copying previous row’s corresponding value. Please help me.

  2. avinash on said:

    sir how to increment dts.Rows.Count by 1 if it is blank.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

HTML tags are not allowed.