How to build an array to make it easy to read the data from Excel worksheet
table then load the data into oracle.The table 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 yeetable.XLS; " & _
"Extended Properties=Exce l 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.Ole Db.OleDbDataAda pter( _
"select * from [Sheet1$]", MyConnection)
DS = New System.Data.Dat aSet()
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=dprod;Us er Id=rab1;
Password=gam")
'3. Open the connection through ODP.NET
con.Open()
Catch ex As Exception
'4.display if any error occurs
MsgBox(ex.Messa ge,Microsoft.Vi sualBasic.MsgBo xStyle.Exclamat ion,
"OraScan")
'3.Create command object to perform a query against the database:
Dim cmdQuery As String = "SELECT * FROM employeetable"
' 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
table then load the data into oracle.The table 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 yeetable.XLS; " & _
"Extended Properties=Exce l 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.Ole Db.OleDbDataAda pter( _
"select * from [Sheet1$]", MyConnection)
DS = New System.Data.Dat aSet()
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=dprod;Us er Id=rab1;
Password=gam")
'3. Open the connection through ODP.NET
con.Open()
Catch ex As Exception
'4.display if any error occurs
MsgBox(ex.Messa ge,Microsoft.Vi sualBasic.MsgBo xStyle.Exclamat ion,
"OraScan")
'3.Create command object to perform a query against the database:
Dim cmdQuery As String = "SELECT * FROM employeetable"
' 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