Dot Net – How to upload excel sheet into databse using Dataset in ASP.NET

I thought of writing this blog post because there can a number of occasion where in you might need to upload something from word file or excel sheet or it can be any kind of  file need to be inserted into the database what we can do many things.

But probably what I feel good and follow most of the time is doing this way so that we can preserve the statements for future reference what we have actually used and where it is going to exactly affect.

Step 1: Create  a website and then add default page on to it.

Step 2: Write the following code in Page_Load(object sender, EventArgs e) function

DataSet ds = new DataSet();

OleDbConnection myCon = new OleDbConnection(@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=folderpath\sheet.xls;Extended Properties=Excel 8.0;”);

OleDbCommand myComm = new OleDbCommand(“select * from [sheet1$] where State<>””, myCon);

OleDbDataAdapter da = new OleDbDataAdapter(myComm);

da.Fill(ds);

for (int i = 0; i < ds.Tables[0].Rows.Count;i++ )

{

Response.Write(“insert into dbo.Temp2 values(‘” + ds.Tables[0].Rows[i][0].ToString() + “‘,'” + ds.Tables[0].Rows[i][1].ToString() + “‘,'” + ds.Tables[0].Rows[i][2].ToString() + “‘,'” + ds.Tables[0].Rows[i][3].ToString() + “‘)<br />”);

}

myCon.Close();

Step 3: Create a table in SQL Server names Temp2 where in u want insert the excel sheet into.

NOTE: The table name can be any name  you like its just an example never take it as standard, its for learing.

create table Temp2(Id varchar(10),country varchar(100),natAnimal varchar(50),lang varchar(50))

Step 4: Create or if u have  excel sheet just make sure that that contains header for each column it will be easy to understand

EX:

State	Country	Animal	Language
1	Australia	Kangroo	English
2	India	Peacock	Hindi
3	USA	American Biston	English

This is a sample excel sheet name it as sheet and inside as Sheet1 it will be applied by default so just hit the save button.

So all set!!!

now save the excel sheet in the folder of your website and run the web site the expected Output will be something like this will appear in your broswer

insert into dbo.Temp2 values(‘1′,’Australia’,’Kangroo’,’English’)

insert into dbo.Temp2 values(‘2′,’India’,’Peacock ‘,’Hindi’)

insert into dbo.Temp2 values(‘3′,’USA’,’American Biston’,’English’)

then copy it and paste in ssms (SQL server Query Editor and hit F5 to execute) Finally records are inserted into the database.

There can be many more ways I am sure but this is also handy when you have production and development section seperate.

Reference : Dilip Kumar Jena ( https://mstechexplore.wordpress.com )