I know my question seems easy,how to build an array to read the data from
Excel worksheet table then load the data into oracle.The table(employee)
looks like this have 4 columns:1-entity name with two
rows(employee,e mployee),2-entity/table
Definition(no rows),3-attribute name has 2
rows(employee_i d,employee_name ),4-Attribute/Column Definition with no rows.
My code is below:
Imports System.Data.Ole Db
Imports System.Data
Imports Oracle.DataAcce ss.Client
Public Class Form1
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim DS As System.Data.Dat aSet
Dim MyCommand As System.Data.Ole Db.OleDbDataAda pter
Dim MyConnection As System.Data.Ole Db.OleDbConnect ion
MyConnection = New System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\emplo yee.XLS; " & _
"Extended Properties=Exce l 8.0;")
' Select the data from Sheet1 of the workbook.
' Create OleDbCommand object and select data from worksheet Sheet1
MyCommand = New System.Data.Ole Db.OleDbDataAda pter( _
"select * from [Sheet1$]", MyConnection)
DS = New System.Data.Dat aSet()
' Fill the DataSet from the data extracted from the worksheet.
MyCommand.Fill( DS)
MyConnection.Cl ose()
Debug.Print(DS. Tables(0).Rows( 0).Item(0))
Debug.Print(DS. Tables(0).Rows( 0).Item(2))
Debug.Print(DS. Tables(0).Rows( 1).Item(0))
Debug.Print(DS. Tables(0).Rows( 1).Item(2))
'1.Create connection object to Oracle database
Dim con As OracleConnectio n = New OracleConnectio n()
Try
'2.Specify connection string
con.ConnectionS tring = ("Data Source=damn;Use r Id=gms;
Password=rts123 ")
'3. Open the connection through ODP.NET
con.Open()
Catch ex As Exception
'4.display if any error occurs
MsgBox(ex.Messa ge, Microsoft.Visua lBasic.MsgBoxSt yle.Exclamation ,
"OraScan")
'3.Create command object to perform a query against the database:
Dim cmdQuery As String = "SELECT * FROM employee"
' Create the OracleCommand object to work with select
Dim cmd As OracleCommand = New OracleCommand(c mdQuery)
cmd.Connection = con
cmd.CommandType = CommandType.Tex t
' Dispose OracleCommand object
cmd.Dispose()
Finally
' Close and Dispose OracleConnectio n object
con.Close()
con.Dispose()
End Try
End Sub
End Class
Excel worksheet table then load the data into oracle.The table(employee)
looks like this have 4 columns:1-entity name with two
rows(employee,e mployee),2-entity/table
Definition(no rows),3-attribute name has 2
rows(employee_i d,employee_name ),4-Attribute/Column Definition with no rows.
My code is below:
Imports System.Data.Ole Db
Imports System.Data
Imports Oracle.DataAcce ss.Client
Public Class Form1
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
Dim DS As System.Data.Dat aSet
Dim MyCommand As System.Data.Ole Db.OleDbDataAda pter
Dim MyConnection As System.Data.Ole Db.OleDbConnect ion
MyConnection = New System.Data.Ole Db.OleDbConnect ion( _
"provider=Micro soft.Jet.OLEDB. 4.0; " & _
"data source=C:\emplo yee.XLS; " & _
"Extended Properties=Exce l 8.0;")
' Select the data from Sheet1 of the workbook.
' Create OleDbCommand object and select data from worksheet Sheet1
MyCommand = New System.Data.Ole Db.OleDbDataAda pter( _
"select * from [Sheet1$]", MyConnection)
DS = New System.Data.Dat aSet()
' Fill the DataSet from the data extracted from the worksheet.
MyCommand.Fill( DS)
MyConnection.Cl ose()
Debug.Print(DS. Tables(0).Rows( 0).Item(0))
Debug.Print(DS. Tables(0).Rows( 0).Item(2))
Debug.Print(DS. Tables(0).Rows( 1).Item(0))
Debug.Print(DS. Tables(0).Rows( 1).Item(2))
'1.Create connection object to Oracle database
Dim con As OracleConnectio n = New OracleConnectio n()
Try
'2.Specify connection string
con.ConnectionS tring = ("Data Source=damn;Use r Id=gms;
Password=rts123 ")
'3. Open the connection through ODP.NET
con.Open()
Catch ex As Exception
'4.display if any error occurs
MsgBox(ex.Messa ge, Microsoft.Visua lBasic.MsgBoxSt yle.Exclamation ,
"OraScan")
'3.Create command object to perform a query against the database:
Dim cmdQuery As String = "SELECT * FROM employee"
' Create the OracleCommand object to work with select
Dim cmd As OracleCommand = New OracleCommand(c mdQuery)
cmd.Connection = con
cmd.CommandType = CommandType.Tex t
' Dispose OracleCommand object
cmd.Dispose()
Finally
' Close and Dispose OracleConnectio n object
con.Close()
con.Dispose()
End Try
End Sub
End Class
Comment