Faster export of data from Excel to Oracle using VBA

[duesentrieb]

Hi all,
I want to export data (10'000 to 20'000 rows) from Excel to Oracle using VBA. My current solution (see script) uses ADO to read the data cell by cell. ADO batch updates are not really faster than single row updates. What would be significant faster is using a SQL link. However, I appreciate any solution speeding up my script, even the Excel loops. I am seeking ideas like
- is there a way to use some fancy programming technique to speed up the loops (using arrays etc.)
- is there a way to use SQL into Oracle from Excel (like OPENROWSET on SQL Server)
- can I use JET to query the data from excel and export them to Oracle without creating an additional database?

My Script:
Sub myscript()
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim conStr As String

Dim rngData As Range, rngHeader As Range
Dim r As Long, c As Long, rMax As Long ' allow more than 32k rows
Dim tStart As Long

conStr = "Provider=OraOLEDB.Oracle.1;Password=somepwd;Persist Security Info=True;User ID=someuser;Data Source=somedatabase"
tStart = Timer
bolStatusBar = Application.DisplayStatusBar

Set con = New ADODB.Connection
Set rst = New ADODB.Recordset
con.Open conStr

With rst
.ActiveConnection = con
.Source = "SELECT * FROM targettable"
.CursorType = adOpenKeyset
.LockType = adLockOptimistic 'use with .update
.CursorLocation = adUseServer
.Open
End With

Set rngData = Range("datarange")
Set rngHeader = Range("header")

rMax = rngData.Rows.Count

For r = 1 To rMax 'use rMax also for progress bar
rst.AddNew
For c = 1 To rngData.Columns.Count
rst.Fields(rngHeader(1, c).Value) = rngData(r, c).Value
Next c
rst.Update '12-15 seconds for 1000 records
Next r

con.Close
MsgBox "Done, Time required: " & Int(Timer - tStart) & " seconds."
End Sub

Thank you for any input!

 

[RoryA]

I would imagine Oracle must have DTS tools to import data that would be much faster than anything you could do from VBA.

Loading data into Access or Excel from Excel, you can do something like this:

Sub ExportDataFromThisWorkbookToClosedWorkbook()
' Sample demonstrating how to export data from the current workbook to a closed workbook
   Dim cn As Object, strQuery As String
   Set cn = CreateObject("ADODB.Connection")
   With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
                          "Extended Properties=""Excel 12.0;HDR=Yes;"""
      .Open
   End With
   strQuery = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=C:\ADO Dest.xls] SELECT * FROM [Sheet1$B2:F3]"

   cn.Execute strQuery
   cn.Close
   Set cn = Nothing
End Sub

but I'm not sure whether the Oracle provider can read Excel workbooks using the same syntax.

 

[duesentrieb]

Hi Rory,
thank you, this was one of the first ideas I came across. But apparently the Oracle provider does not support reading Excel like this or elsewise I would be interested to see someone's sample code how to do it. Any SQL send to Oracle is interpreted by the database. DTS is not an option, the setup would be to complex in our environment.
Best,
Chris

 

[Fazza]

this thread from earlier in the month is relevant, http://www.mrexcel.com/forum/excel-questions/698670-using-excel-visual-basic-applications-export-data-ms-access-table.html

in particular, and as you asked about, instead of looping through reading cell by cell -slow - there is an example that loads the worksheet range to an array and then works with the array

regards

 

[duesentrieb]

Thank you, I just tried your idea and ran some tests. Your solution is for 1000 rows about 2 seconds faster.... We will use it, but maybe someone else has some clever solution, with a somewhat different approach at all?

 

[Fazza]

So still about 10 seconds for 1,000 records. That is very slow.

Suggest you post the exact code used & I can take a look. Maybe not til next week, though.

regards

 

[duesentrieb]

Thank you for looking into this, I tortured google already for hours and can't think of a really faster way. Brillant would be somehing that allows me to at least avoid the loops over the columns (it's about a 100 columns). Thank you!

====> The script is posted above in my initial post

 

[Fazza]

I don't know if you used an array instead of looping through cell be cell - my comments in thread post #4. Something like below: please modify to suit. regards

Sub untested()
  'late bound: will be faster if you early bind
  Dim r As Long, i As Long
  Dim cn As Object
  Dim rs As Object
  Dim ar As Variant
 
  ar = Range("datarange").Value
  
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=OraOLEDB.Oracle.1;Password=somepwd;Persist Security Info=True;User ID=someuser;Data Source=somedatabase"
  Set rs = CreateObject("ADODB.Recordset")
  rs.Open "targettable", cn, 1, 3, 2
  With rs
    For r = LBound(ar, 1) To UBound(ar, 1)
      .AddNew
      For i = LBound(ar, 2) To UBound(ar, 2)
        .fields(ar(1, i)) = ar(r, i)
      Next i
      .Update
    Next r
  End With
  rs.Close: Set rs = Nothing
  cn.Close: Set cn = Nothing
  Erase ar
End Sub

 

[BumKneesOhYeah]

You could use a statement like the following (it made my thing run 50 times quicker at least)
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

I got this from following link:

Oracle / PLSQL: INSERT ALL Statement

This Oracle tutorial explains how to use the Oracle INSERT ALL statement with syntax and examples.The Oracle INSERT ALL statement is used to add multiple rows with a single INSERT statement.

www.techonthenet.com