loading data from spread sheet to oracle database

[ravinookala]

I am trying to load the values from spread sheet to oracle database
table. my code is doing only the first row five times insted of 5
rows. please help me to inset the next active cell of the range of
values
spread sheet values
===============
empno ename
1 anu
2 niru
3 poonam
4 sahil
5 raju

vb editor code.
===========

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim i As Integer
Private Sub cmdLoad_Click()
CN.Open "DsnRavi1", "system", "manager"
RS.Open "select * from c", CN, adOpenKeyset, adLockOptimistic
ActiveWorkbook.Sheets("Sheet1").Activate
Range("A2").Select
v_rowcount = Range("A2").CurrentRegion.Rows.Count - 1
For i = 1 To v_rowcount
RS.AddNew
RS!empno = ActiveCell.Value
RS!ename = ActiveCell.Offset(0, 1)
RS.Update
Next i
If IsEmpty(ActiveCell) = True Then
RS.Close
End If

End Sub

 

[Hank Scorpio]

RS!empno = ActiveCell.Value
RS!ename = ActiveCell.Offset(0, 1)

You may be incremening your i counter, but you're not doing anything
with it. You're on cell A2, and never move from there. And with each
loop, you're picking the same offset from the same cell.

(One other thing to watch out for; this means that Activecell (which
is always A2) is never empty. Accordingly the

RS.Close

line never executes. Chances are that the recordset DOES close when
the code stops running, but I maintain a healthy distrust of such
things.)

 

[ravinookala]

Hi

Then how do i go to the next row. I have tried
activecell.next.select also. not working

Ravi Nookala

 

[Hank Scorpio]

Then how do i go to the next row. I have tried
activecell.next.select also. not working

You don't need to move the cursor at all. In fact, your code will run
much faster if you don't. This will do what you want (extract of your
original code only):

-----------------------------------------------

'Note that we'll start from A1, because
'the first offset will be 1.
Range("A1").Select

v_rowcount = _
Range("A1").CurrentRegion.Rows.Count - 1

'Use the i counter to specify the row offset.
For i = 1 To v_rowcount

RS.AddNew
RS!empno = ActiveCell.Offset(i, 0)
RS!ename = ActiveCell.Offset(i, 1)
RS.Update

Next i

'The IF test is redundant.
RS.Close

 

[ravinookala]

Hi Hank

Thank you very much for the help. It is woking now.
One more clarification.

Is there a way to load data into the table without mentioning

rs!ename
rs!empno etc.......

When I pass the table name to the userform
as a parameter , it should be able to map the table column names to the
spread sheet cells one to one and load the data.

Ravi Nookala

 

[onedaywhen]

Rather than:

SELECT * FROM ...

you should use the column names:

SELECT ename, empno FROM ...

This way you are can be sure that the number of columns and their
ordinal positions within the recordset match those of your worksheet.
You can then loop through the recordset's Fields collection e.g.

For i = 1 To v_rowcount
RS.AddNew
For j = 0 to RS.Fields.Count - 1
RS.Fields(j).Value = ActiveCell.Offset(i, j)
Next
RS.Update
Next i

 

[ravinookala]

Hi

Thank you very much for your help.

It is not necessary to select the field names in the record set.
If it is so, my programme will become hardcoded.
I have done it dynamic as under and working fine.

intFieldsCount = RS.Fields.Count
v_rowcount = Range("A1").CurrentRegion.Rows.Count - 1
With RS
For i = 0 To v_rowcount - 1
RS.AddNew
For j = 0 To intFieldsCount - 1
If IsEmpty(ActiveCell.Offset(0, j)) = True Then
..Fields(j).Value = Null
Else
..Fields(j).Value = Trim(ActiveCell.Offset(0, j))
End If
On Error Resume Next
Next j
RS.Update
ActiveCell.Offset(1, 0).Select
Next i
End With

Ravi Nookala

 

[ravinookala]

Hi.

Suppose I have spread sheet values as below

empno ename deptno
1 anu 10
2 niru 10
3 poonam 20
4 sahil 20
5 raju 10


How can I insert the values of deptno 10 only in to the
table.

Ravi Nookal

 

[onedaywhen]

The preferred convention is to specify in the SELECT clause the
columns you require, rather than use SELECT * . There are all kinds of
good reasons for this (e.g. with no names to go on the DBMS must
consult a data dictionary and thereby increase execution time, adding
a further column with a constraint to the table will break your app,
you are taking it on faith the columns will appear in the correct
order but what if you port to a new version or another DBMS, etc)
which you probably don't think apply to your app, but I thought I
should point this out.

 

[onedaywhen]

Do you mean, you already have a row in the database where empno=1 and
ename='anu' and you want to set septno=10 for this row? Assuming your
key column is empno, you'd construct an UPDATE statement like this:

UPDATE c SET septno=10 WHERE empno=1

You'd do this for each row on your sheet.

 

[ravinookala]

Hi

All the suggestion you made are valid and relavant.

But my requirement is only for Oracle and will not be useing any
other DBMS. I do not want to add and remove the fields names
in the code when ever I am changing the value for the table name
in the userform.

Any way, my present concern is

I have all the 14 rows of emp table in the spread sheet .
My emp table is empty as of now.
I want to load into emp table all the rows of deptno 20 (5 rows),
OR
I want to load all the rows where job = CLERK like that.

This I am able to do when I am importing from emp table to sprea
sheet.

In the same way, How can I achive this functionality when I want
to export from spread sheet to oracle table.

Ravi Nookal

 

[onedaywhen]

Well, instead of constructing an UPPDATE statement for each
spreadsheet row (which works for existing rows), instead construct an
INSERT INTO statement e.g.

INSERT INTO c (empono, ename, deptno, job) VALUES (1, 'aun', 10,
'CLERK')

Note, as with a SELECT statement, the column list is optional but
again it is best practise to do so rather than reply on the ordinal
positions always being the same.

You should loop through the spreadsheet rows, test whether the
conditions are true for the current row (e.g. deptno=10 OR
job='CLERK') and if so construct an INSERT statement and execute it.

If your DBMS was MS (e.g. SQL Server) it may be possible to do it in
one hit by connecting to the Excel workbook using the OLEDB provider
for Jet and doing something like this:

INSERT INTO c IN '<odbc connection DB>'
(empono, ename, deptno, job)
SELECT empono, ename, deptno, job
FROM [Sheet1$]
WHERE deptno=10
OR job='CLERK'

But I've never done this with Oracle so don't know if it's possible.

 

[ravinookala]

Hi

I think it is not possible to build an insert statement using
VBA for oracle. It is only
rs.addnew
------------- statements
here I think we can loop and search and compare.

You should loop through the spreadsheet rows, test whether the
conditions are true for the current row (e.g. deptno=10 OR
job='CLERK') and if so construct an INSERT statement and execute it.
rs.update


How we can achieve this. Can you suggest one sample way of
doing this.

Ravi Nookal

 

[onedaywhen]

I think it is not possible to build an insert statement using
VBA for oracle. It is only
rs.addnew

Have you tried? I think you will find you are mistaken. I have
previously worked on a VB6 app that used thousands of INSERT
statements with ADO to populate Oracle databases for new clients.

Try this code:

Sub test()
Dim CN As ADODB.Connection
Dim i As Long
Dim strSql As String
strSql = "INSERT INTO c" & _
" (empono,ename,deptno,job)" & _
" VALUES (1,'aun',10,'CLERK')"
Set CN = New ADODB.Connection
With CN
.Open "DsnRavi1", "system", "manager"
.Execute strSql, i
.Close
End If
MsgBox "Records affected = " & CStr(i)
End Sub

--

 

[ravinookala]

Hi
It is nice you worked on VB. Then you can definitely guide me in thi
regard. The code you have suggeste me is hard coded one.

strSql = "INSERT INTO c" & _
" (empono,ename,deptno,job)" & _
" VALUES (1,'aun',10,'CLERK')"
Set CN = New ADODB.Connection
With CN
.Open "DsnRavi1", "system", "manager"
.Execute strSql, i
.Close

I want this insert statement to be build dynamically taking the value
from the spread sheet and accepting the parametes from the user form.

My user form accepts
1. table name
2. DSN name
3. user
4. password.
5. Where clause.

If my where clause is deptno=10 then
the insert statment should be built dynamically and take the value
from the spread sheet and then insert the rows of only
deptno =10. My where clause may be changing time to time. It may be
where job='CLERK'

I want your guidance as to how can I build this insert statement
dynamically.

Please help.

Ravi Nookala
for this how ca

 

[onedaywhen]

Below is some example code, for both approaches (recordset and
INSERT). I have made obvious assumptions about your dynamic properties
e.g. your connection details are in textboxes called txtDsn, txtUserid
and txtPassword, etc.

The one thing I haven't attempted is the dynamic WHERE clause, because
this would involve too many assumptions. Instead I've hard-coded in
accordance with your data in an earlier post. As a suggestion, if your
first row contains column headers, you could loop through them looking
for the relative position of the WHERE clause column name (deptno or
jobno). You would then use this position instead of my hard-coded '3'
in

If .Cells(i, 3).Value = 10 Then

You'd also uses the the WHERE clause value rather than my hard-coded
'10'.

As I said, I making all sorts of assumptions now so I'll stop. One
thing that does still puzzle me is, if your spreadsheet is that
dynamic how can you possibly get away with SELECT * ?

' <Code>
Option Explicit

Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset

Private Sub Use_RS()
Dim i As Long
Dim j As Long
Dim v_rowcount As Long

Dim oRange As Excel.Range
Set CN = New ADODB.Connection
CN.Open txtDsn.Text, txtUserid.Text, txtPassword.Text

Set RS = New ADODB.Recordset
With RS
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Source = "select empno, ename, deptno from " & txtTableName.Text
.ActiveConnection = CN
.Open
.ActiveConnection = Nothing
End With

Set oRange = ActiveWorkbook.Sheets("Sheet1").Range("A2")
With oRange

v_rowcount = .CurrentRegion.Rows.Count - 1

For i = 1 To v_rowcount
' hard-coded WHERE clause i.e. column position/value
If .Cells(i, 3).Value = 10 Then
RS.AddNew
For j = 0 To RS.Fields.Count - 1
RS.Fields(j).Value = .Cells(i, j + 1).Value
Next j
End If
Next i
End With

With RS
.ActiveConnection = CN
.UpdateBatch
.ActiveConnection = Nothing
.Close
End With

CN.Close
End Sub

Private Sub Use_INSERT()
Dim i As Long
Dim j As Long
Dim lngRowsAffected As Long
Dim v_rowcount As Long
Dim lngColCount As Long
Dim strInsert As String
Dim oRange As Excel.Range

Const strINSERT_TEXT As String = "INSERT INTO c (empno,ename,deptno)
VALUES ("
Const strCOMMA As String = ","
Const strCLOSE_BRACKET As String = ")"
Const strQUOTE As String = "'"

Set CN = New ADODB.Connection
CN.Open txtDsn.Text, txtUserid.Text, txtPassword.Text

Set oRange = ActiveWorkbook.Sheets("Sheet1").Range("A2")
With oRange

v_rowcount = .CurrentRegion.Rows.Count - 1
lngColCount = .CurrentRegion.Columns.Count

For i = 1 To v_rowcount
' hard-coded WHERE clause i.e. column position/value
If .Cells(i, 3).Value = 10 Then

strInsert = strINSERT_TEXT

For j = 1 To lngColCount
If IsNumeric(.Cells(i, j).Value) Then
strInsert = strInsert & .Cells(i, j).Value &
strCOMMA
Else
strInsert = strInsert & strQUOTE & _
.Cells(i, j).Value & strQUOTE &
strCOMMA
End If

Next j

strInsert = Left$(strInsert, Len(strInsert) - 1) &
strCLOSE_BRACKET

CN.Execute strInsert, lngRowsAffected

' error trapping here e.g. if lngRowsAffected <> 1

End If
Next i
End With

CN.Close

End Sub

 

[ravinookala]

Hello

Are you there . No postings for the past 4 days.

Ravi Nookal

 

[ravinookala]

Hi

Now I can do conditional export also with the sample code
provided by you.

Thank you verymuch for the cooperation.
With this I close this thread.

Once again ! Thank you very much.

Ravi Nookal