Loading Excel values to database

If you want to load the excel values to database (eg:SQL server database) follow the steps:

1) Create a empty table with columns.

CREATE TABLE [dbo].[users](

[user_name] [varchar](50) NOT NULL,

[Password] [nchar](10) NULL )

Check the data must be empty.

2) Create an excel sheet with your data. In the excel the top row

must contain same column names as table created by us and

remember that you have your data in Sheet1 of excel sheet.

3) In code behind page write this code:

This code can be written in the event you like.

For now I am writing this code in Button_click

Event.

string excelConnectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\users.xls;Extended Properties=””Excel 8.0;HDR=YES;”””;

OleDbConnection conn = new

OleDbConnection(excelConnectionString);

//Here Sheet1 means the current sheet in excel file If your data is in sheet2 you can give Sheet2.

string selqry = “select * from [Sheet1$]”;

OleDbCommand cmd = new OleDbCommand(selqry, conn);

conn.Open();

OleDbDataReader dr;

dr = cmd.ExecuteReader();

string sqlConnectionString = “Data Source=MNCXP08;Initial

Catalog=test;Persist Security Info=True;User

ID=sa;Password=admin123″;

SqlBulkCopy bcp = new SqlBulkCopy(sqlConnectionString);

//Here users is the table we created

bcp.DestinationTableName = “users”;

bcp.WriteToServer(dr);

4) Check the users table you will get all the results.