How to retreive data from Excel into SQLSERVER database table

Hi friends,

I need to retreive data from Excel into SQLSERVER database table

give me reply asap.

Thanks in advance...

you can do it, using SqlBulkCopy using ADO.NET

I have developed a small tool in C# that can work for you,

I have tested it using sql server 2000/2005 and works ok
but you require Framework 2.0

so if you wanna test it, just mail me
vish4forum at yahoo dot com. I will send you that exe

|||

Hi Vish,

I found ur reply useful..

please send me sample code that will be better

|||

Hi Vish,

I tried using SQLBulk copy..But i got error 'could not find installable ISAM'

Here is my code

string excelConnectionString = @."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=192.168.0.25;Extended
Properties=""Excel 11.0;HDR=YES;IMEX=1;""";

OleDbConnection con = new OleDbConnection(excelConnectionString);


con.Open();
OleDbCommand cmd = new OleDbCommand("select * from [test$]", con);
dr = cmd.ExecuteReader();

SqlConnection con1 = new SqlConnection();
con1.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["strcon"];
con1.Open();
SqlBulkCopy bulk1 = new SqlBulkCopy(con1);
bulk1.DestinationTableName = "tblexcel";
bulk1.WriteToServer(dr);
Response.Write("Loaded");

|||

Checkout this two articles

1)http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

2)http://support.microsoft.com/kb/321686

hope this helps !!!