vijay

welcome Netizen

Share Your Knowledge.It is a way to achieve immortality

Monday, January 24, 2011

IMPORT THE EXCEL TABLE INTO SQL TABLE


hi friends......

i knew many peoples are googled to find the solution fot this problem...
i hope this code will be digest your problem....
for that first you create one table in sql databse like this

CREATE TABLE mark
subject  varchar(50) NULL,int NULL,
mark1 int NULL,
mark2 int NULL


And then create table in excel sheet ..... subject mark1 mark2 maths 50 70 english 56 89 server side coding.... In the button field add the following coding... Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim filepath As String = "C:\Markt.xls" version = Mid(filepath, filepath.LastIndexOf(".") + 1, 5) Try If version = ".xls" Then 'Execute when MS EXCEL 2003 Format Dim MyConnection As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & filepath & "';Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection) DtSet = New System.Data.DataSet MyCommand.Fill(DtSet, "[Sheet1$]") dt = DtSet.Tables(0) MyConnection.Close() ElseIf version = ".xlsx" Then 'Execute when MS EXCEL 2007 Format Dim MyConnection As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & filepath & "';Extended Properties=Excel 12.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection) DtSet = New System.Data.DataSet MyCommand.Fill(DtSet, "[Sheet1$]") dt = DtSet.Tables(0) MyConnection.Close() Else Label1.Text = "This Version is not Support!" Exit Sub End If If dt.Rows.Count > 0 Then For i = 0 To dt.Rows.Count - 1 dr = dt.Rows(i) fetch = "insert into StudentList(Regno,Sname,Class) values(" fetch += "'" & dt.Rows(i).Item(0) & "'," fetch += "'" & dt.Rows(i).Item(1) & "'," fetch += "'" & dt.Rows(i).Item(2) & "')" sqlcmd = New SqlCommand(fetch, sqlcon) sqlcmd.CommandType = CommandType.Text sqlcmd.ExecuteNonQuery() fetch = "" Next Label1.Text = "successfully Imported values to SQL Server" End If Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

2 comments:

Muhammad Azeem said...

This is a nice article..
Its easy to understand ..
And this article is using to learn something about it..

c#, dot.net, php tutorial, Ms sql server

Thanks a lot..!
ri80

gates said...

sir i need a excel datasets which would contain more than 10000 datas...anybody help me...

Post a Comment