importing excel file into sql server 2005 in asp .net using c#

Dear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me?

rgds,
RK

try the link below [SqlBulkCopy]

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

 
// Connection String to Excel Workbookstring excelConnectionString = @."Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";// Create Connection to Excel Workbookusing (OleDbConnection connection =new OleDbConnection(excelConnectionString)){ OleDbCommand command =new OleDbCommand ("Select ID,Data FROM [Data$]", connection); connection.Open();// Create DbDataReader to Data Worksheetusing (DbDataReader dr = command.ExecuteReader()) {// SQL Server Connection Stringstring sqlConnectionString ="Data Source=.; Initial Catalog=Test;Integrated Security=True";// Bulk Copy to SQL Serverusing (SqlBulkCopy bulkCopy =new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName ="ExcelData"; bulkCopy.WriteToServer(dr); } }}
 

Good Luck./.

|||

Hi

you have to first read the XL content then you have to insert into Sql server.

you can transfer your XL to dataset by Oledb provider by this code

string conection_string ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=YOUR XL PATH; Extended Properties='Excel 8.0;IMEX=1;HDR=YES;'";

OleDbDataAdapter adp =newOleDbDataAdapter("SELECT * FROM [Sheet1$] ", conection_string);

DataSet ds =newDataSet();

adp.Fill(ds);

now your xl content would be in dataset ds , then you can access this dataset as well and read dataset line by line and insert into Sql

add namespace :using System.Data.OleDb;

|||

Dear what is Sheet1$ can u tell me detail?

|||

ramu778:

Dear what is Sheet1$ can u tell me detail?

sorry to interrupt

in ConnectionString; for DataSource we give the XLS file path...

and in below code line... we have to give the Sheet name in the Select Query from which we have to read the date from, as there would be more than one Sheet in an excel file...

OleDbDataAdapter adp =newOleDbDataAdapter("SELECT * FROM [Sheet1$] ", conection_string);

hope it clears./.

|||

Hi

sheet1 is nothing but the name of the XL sheet , there are some name at bottom of XL like sheet1,sheet2 and so, we have to give that name