read from excel worksheet and load it into Oracle repository

  • Thread starter Thread starter constantin
  • Start date Start date
C

constantin

I am new to VB, I am woking on a task to read meta data from excel worksheet
and dispaly it in excel format, then load it into Oracle repository my table
name
is employee and has 2 columns:employee_id pk, employee_name not null. So far
I tried to use ADO but my code isn't displaying what I mentioned above,this
is my code, I am using windowns XP proffesional. Thanks

Imports System
Imports System.Data
Imports Oracle.DataAccess.Client

Public Class Form1
'Public con As System.Data.OleDb.OleDbConnection = New
System.Data.OleDb.OleDbConnection()
'Oracle.DataAccess.Client.OracleConnection()

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Connecting to an Excel Workbook with ADO
'Public Class ConnectToExcelUsingSheetName
End Sub
'Private
Sub ConnectToExcelUsingSheetName()
On Error GoTo ConnectToExcelUsingSheetName_Err
' Declare variables
Dim con1 As New ADODB.Connection
Dim rst As New ADODB.Recordset

''Step 1 define an object con of type OracleConnection
'Dim con As New Oracle.DataAccess.Client.OracleConnection("Data
Source=dprod;User Id=smu; Password=mul99666")

Dim ConString As String
ConString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\employee.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
'con.ConnectionString = ConString
'Once I establish connection string I go a head and open the database
'con1.Open()

'Dim cmdQuery As String = "SELECT * FROM employee"

''create command object to work with select
'Dim dbCommand As New Oracle.DataAccess.Client.OracleCommand(cmdQuery,
con)

'specifying the name of the worksheet you must add a dollar
'sign ($) to the end of the name

rst.Open("SELECT * FROM [Sheet1$];", con1,
ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
' Loop through the recordset and send data to the Immediate Window
rst.MoveFirst()
Do

Debug.Print (rst![Entity Name] & " " & rst![Entity/Table
Definition] & " " & rst! _
[Attribute Name] & "(" & rst![Attribute/Column Definition] & ")")
rst.MoveNext()
Loop Until rst.EOF
'Tidy up
ConnectToExcelUsingSheetName_Exit:
On Error Resume Next
rst.Close()
con1.Close()
rst = Nothing
con1 = Nothing
Exit Sub
ConnectToExcelUsingSheetName_Err:
MsgBox(Err.Number & vbCrLf & Err.Description, vbCritical, "Error!")
Resume ConnectToExcelUsingSheetName_Exit

End Sub
End Class
 
Greetings,

To read from Excel you can do this and write to Oracle do this:

-----------------------------------------------------
Imports System.Data.OleDb
Imports System.Data
Imports Oracle.DataAccess.Client

Public Class Form1

Private Sub Button1_Click(...) Handles Button1.Click

Dim daOle As OleDbDataAdapter, ds As DataSet
Dim conn As OleDbConnection
conn = New OleDbConnection

conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\bin\test1.xls;Extended Properties=Excel 8.0"

daOle = New OleDbDataAdapter
ds = New DataSet
daOle.SelectCommand = New OleDbCommand
daOle.SelectCommand.Connection = conn

daOle.SelectCommand.CommandText =
"Select * From [SheetName$]"
'--use name of excel sheet containing thedata followed by $

'--tbl1 gets created on the fly automatically here
daOle.Fill(ds, "tbl1") '--and fills with data from Excel

'--I don't have Oracle so not sure what the Oracle
'--dataAdapter looks like but once you create it do this
dim daOracle As New OracleDataAdapter '--pseudo here
dim connOracle as New OracleConnection '--pseudo here

daOracle.SelectCommand = New OracleSelectCommand
daOracle.SelectCommand.Connection = connOracle

daOracle.SelectCommand.CommandText =
"Select * From tbl_ToReceiveExcelData Where 1=2"
'--only want tbl2 structure - no data

daOracle.Fill(ds,"tbl2")
'--tbl2 will recieve the Excel data
'--need tbl2 structure in dataset ds - tbl2 empty here

Dim reader As DataTableReader =
dsOle.Tables("tbl1").CreateDataReader

'--here is where data gets transfered from tbl1 to tbl2
ds.Tables("tbl2").Load(reader, LoadOption.Upsert)

'--note: .Load.(reader, LoadOption.Upsert) is correct
'--Upsert means updateInsert abreviated - I think

'--now write data to oracle table
daOracle.Update(ds, "tbl2")

End Sub
End Class
--------------------------------------------------

The part for reading data from Excel to VB.Net is correct. The part for
transfering data from VB.Net to Server table - well, this is how to do
it for Sql Server. Since I don't have an Oracle driver on my
workstation - I am guessing the syntax should be similar for Oracle
because when I was using Classic ADO in VBA (and did have an Oracle
driver) most of the syntax was almost identical (tSql to PL92 or
whatever they call Oracle sql - except for a few minor operators). So I
suspect that dataAdapters will function similarly between Oracle and Sql
Server. AT least you have the Excel part because That Is how you do it.



Rich
 
I forgot the OracleInsertCommand - you will need that to write the data
to the Oracle table:

daOracle.InsertCommand = New OracleInsertCommand
daOracle.InsertCommad.Connection = connOracle
daOrace.InsertCommand.CommandText = "Insert Into
tbl_ToReceiveExcelDate(fld1, fld2) Select @P1, @P2"
daOracle.InsertCommand.Parameters.Add("@P1",OracleSql.Int,4,"fld1)
daOracle.InsertCommand.Parameters.Add("@P2",OracleSql.Varchar, 50,
"fld2")

place these lines of code right after

dim daOracle As New OracleDataAdapter '--pseudo here
dim connOracle as New OracleConnection '--pseudo here


Oh yeah, the columns and data types in the Excel file have to match the
columns and data types in the Oracle table exactly. If you have 2
columns in Excel, then your table should only have 2 columns - at least
the first 2 columns of your Oracle table need to match the Excel sheet.

So when you call

daOracle.Update(ds, "tbl2")

it will know what to do -- that is -- Insert/write the data from tbl2 to
the Oracle table on the server.

Rich
 
Thanks alot it helps but some errors. This is the code I changed to fit my req.

Imports System.Data.OleDb
Imports System.Data
Imports Oracle.DataAccess.Client


Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim daOle As OleDbDataAdapter
Dim ds As DataSet
Dim conn As OleDbConnection
conn = New OleDbConnection

conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\employeetable.xls;Extended Properties=Excel 8.0"

daOle = New OleDbDataAdapter
ds = New DataSet
daOle.SelectCommand = New OleDbCommand
daOle.SelectCommand.Connection = conn

daOle.SelectCommand.CommandText = "Select * From [Sheet1$]"
'--use name of excel sheet containing thedata followed by $

'--tbl1 gets created on the fly automatically here
daOle.Fill(ds, "tbl1") '--and fills with data from Excel

'--I don't have Oracle so not sure what the Oracle
'--dataAdapter looks like but once you create it do this
'Dim daOracle As New OracleDataAdapter '--pseudo here
Dim connOracle As New OracleConnection("Data Source=dprod;User Id=ug;
Password=mu") '--pseudo here
Dim daOracle As New OracleDataAdapter
daOracle.InsertCommand = New OracleCommand
daOracle.InsertCommand.CommandText = "Insert Into
tbl_ToReceiveExcelDate(fld1, fld2) Select @P1, @P2"
'daOracle.InsertCommand.Parameters.Add("@P1", OracleSql.Int, 4, "fld1")
'daOracle.InsertCommand.Parameters.Add("@P2", OracleSql.Varchar, 50,
"fld2")


daOracle.SelectCommand.CommandText = "Select * From
tbl_ToReceiveExcelData Where 1=2"
'--only want tbl2 structure - no data

daOracle.Fill(ds, "tbl2")
'--tbl2 will recieve the Excel data
'--need tbl2 structure in dataset ds - tbl2 empty here

Dim reader As DataTableReader = ds.Tables("tbl1").CreateDataReader()

'--here is where data gets transfered from tbl1 to tbl2
ds.Tables("tbl2").Load(reader, LoadOption.Upsert)

'--note: .Load.(reader, LoadOption.Upsert) is correct
'--Upsert means updateInsert abreviated - I think

'--now write data to oracle table
daOracle.Update(ds, "tbl2")

End Sub
End Class


error:Object reference not set to an instance of an object. At line
daOracle.SelectCommand.CommandText = "Select * From tbl_ToReceiveExcelData
Where 1=2"
Also plz can u explain these lines:

daOracle.InsertCommand.CommandText = "Insert Into
tbl_ToReceiveExcelDate(fld1, fld2) Select @P1, @P2"
daOracle.InsertCommand.Parameters.Add("@P1", OracleSql.Int, 4, "fld1")
daOracle.InsertCommand.Parameters.Add("@P2", OracleSql.Varchar, 50,
"fld2")
 
Back
Top