.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> C# >> Post New QuestionBookmark and Share Subscribe to Forum

import excel file into sql database table

Posted By: Catherine     Posted Date: June 09, 2011    Points:5   Category :C#
I successfully uploaded my excel file into my server. but my excel file field should be import into my database table which is already in use.Excel file field have only 4 field but my table have 9 field. the specified field should be insert into table. here is my code which i used for upload into server. please help


protected void btnupload_Click(object sender, EventArgs e)
{
String path = "\\\\192.168.0.90\\HRMS_Convey_Upload\\";
Boolean fileOK = false;
string filename1 = FileUpLoad1.FileName;
if (filename1 == "")
{
Response.Write("<script> alert('Browse The File ')</script>");
}
else
{
string[] strstring = new string[4];
char[] splitter = { '_', '.' };
strstring = filename1.Split(splitter);

int thirdstring = strstring[2].Length;
if ((strstring[0] == "convey") && (strstring[1] == "1" || strstring[1] == "3") && (thirdstring == 5))
{
fileOK = true;
if (FileUpLoad1.HasFile)
{
String fileExtension = System.IO.Path.GetExtension(FileUpLoad1.FileName).ToLower();
String[] allowedExtensions = { ".xls", ".xlsx" };
for (int i = 0; i < allowedExtensions.Length; i++)
{
if (fileExtension == allowedExtensions[i])
{
fileOK = true;

GetExcelSheets();
}
}
}
}

if (fileOK)
{
try
{

FileUpLoad1.PostedFile.SaveAs(path + FileUpLoad1.FileName);

Response.Write("<script> alert('File uploaded!')</script>");

}
catch (Exception ex)
{
Response.Write("<script> alert('File could not be uploaded.')</script>");

}
}
else
{
Response.Write("<script> alert('Invalid File name')</script>");

}
}
}

Responses
Author: Gowthammanju             
Posted Date: June 09, 2011     Points: 20   

hey d0 not post the code like this

just type the content and post ur codings in zip files or in code box



Thanks & Regards,
GowthamManju
Chennai



Mark if the answer is true
Author: Gowthammanju             
Posted Date: June 09, 2011     Points: 20   

Hey look through below articles this might help u


http://www.codeproject.com/KB/database/Excel2Sql.aspx

Thanks & Regards,
GowthamManju
Chennai



Mark if the answer is true
Author: Gowthammanju             
Posted Date: June 09, 2011     Points: 20   

Hey look through below articles this might help u


http://www.codeproject.com/KB/database/Excel2Sql.aspx

Thanks & Regards,
GowthamManju
Chennai



Mark if the answer is true
Author: Sasi Prabhu             
Posted Date: June 09, 2011     Points: 20   

Modify It and Use :

Script :

create database sampletesting
go
use sampletesting
go
create table tbl_ExcelVals(ev_sno int identity(1,1),ev_p1 varchar(50),ev_p2 varchar(50),ev_p3 varchar(50),ev_p4 varchar(50),ev_p5 varchar(50),ev_p6 varchar(50),ev_p7 varchar(50),ev_p8 varchar(50),ev_p9 varchar(50),primary key(ev_sno))
go
create proc Proc_ExcelInsVal
@p1 varchar(50),@p2 varchar(50),@p3 varchar(50),@p4 varchar(50),@p5 varchar(50),@p6 varchar(50),
@p7 varchar(50),@p8 varchar(50),@p9 varchar(50)
as
begin
insert into tbl_ExcelVals values(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9)
end
go



Thanks & Regards,
Sasi Prabhu.
Author: Sasi Prabhu             
Posted Date: June 09, 2011     Points: 20   


In .aspx:

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Insert" />

Thanks & Regards,
Sasi Prabhu.
Author: Sasi Prabhu             
Posted Date: June 09, 2011     Points: 20   

In aspx.cs :

protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
FileUpload1.SaveAs(Server.MapPath("~/Files/" + FileUpload1.FileName));
string extn= System.IO.Path.GetExtension(FileUpload1.FileName);
if (extn == ".xls" || extn == ".xlsx")
{
OleDbConnection ocon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/Files/" + FileUpload1.FileName) + ";Extended Properties=Excel 12.0;");
try
{
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", ocon);
ocon.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
string p1 = "", p2 = "", p3 = "", p4 = "", p5 = "";
while (odr.Read())
{
p1 = odr[0].ToString();
p2 = odr[1].ToString();
p3 = odr[2].ToString();
p4 = odr[3].ToString();
p5 = odr[4].ToString();
ClassFiles cscs=new ClassFiles();
cscs.ProcInsVal(p1, p2, p3, p4, p5, "NULL", "NULL", "NULL", "NULL");
}
ScriptManager.RegisterStartupScript(Page, GetType(), "Myscript", "alert('File Process Completed');", true);
}
catch (Exception ex) { Response.Write(ex.Message); }
finally { ocon.Close(); }
}
else { ScriptManager.RegisterStartupScript(Page, GetType(), "Myscript", "alert('Invalid File');", true); }
}
else { ScriptManager.RegisterStartupScript(Page, GetType(), "Myscript", "alert('Please Upload File');", true); }
}




Thanks & Regards,
Sasi Prabhu.
Author: Sasi Prabhu             
Posted Date: June 09, 2011     Points: 20   

Class File => ClassFiles.cs


public void ProcInsVal(string v1, string v2, string v3, string v4, string v5, string v6, string v7, string v8, string v9)
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=sampletesting;Integrated Security=True");
try
{
SqlCommand cmd = new SqlCommand("Proc_ExcelInsVal",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p1", SqlDbType.VarChar).Value = v1;
cmd.Parameters.Add("@p2", SqlDbType.VarChar).Value = v2;
cmd.Parameters.Add("@p3", SqlDbType.VarChar).Value = v3;
cmd.Parameters.Add("@p4", SqlDbType.VarChar).Value = v4;
cmd.Parameters.Add("@p5", SqlDbType.VarChar).Value = v5;
cmd.Parameters.Add("@p6", SqlDbType.VarChar).Value = v6;
cmd.Parameters.Add("@p7", SqlDbType.VarChar).Value = v7;
cmd.Parameters.Add("@p8", SqlDbType.VarChar).Value = v8;
cmd.Parameters.Add("@p9", SqlDbType.VarChar).Value = v9;
con.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex) { }
finally { con.Close(); }
}


Try This Samples.

Thanks & Regards,
Sasi Prabhu.
Author: shiv chandra             
Posted Date: June 12, 2011     Points: 20   


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend