My Zen Arcade

And another tool I use a lot.
I often get asked to export all the (relatively small) tables in a schema into worksheets in an excel workbook, one per tab.
Usually, theres a back and forth first with me trying to explain how this probably won’t mean much, the user
not understanding, me explaining, the user either not caring or still not understanding.
Regardless, I end up having to export the data as they ask.
This isn’t a big deal especially using Toad. Unless there’s 100 tables (or, really, 15 is plenty to make it annoying).
This VBA does that all for you.
As before, you’ll have to build a form to bring in the relevant options (like schema, db, password).
I also have it set to do its work based on a naming patterns.
For example, maybe I only want tables that start with EST. If you do or don’t want that, the relevant line is:
“Select table_name from dba_tables where owner = ‘” & inSchema & “‘ and table_name like ‘” & inNameString & “‘ order by table_name”
There is one limitation in that it simply can’t handle any table with a LOB column (regardless as to the data contained within)

Again, I promise nothing as far as HOW this is written, just the functionality.

Dim strConnect As String
‘ Dim RS As Object
Dim conn As Object
Dim MyErrors As String
Dim MyGoodCount As Integer
Dim MyBadCount As Integer

Private Sub ConnectToOracle(inUID As String, inPWD As String, inServer As String)
‘ open the connection to Oracle
strConnect = “Driver={Microsoft ODBC for Oracle};” & “Server=” & inServer & “;uid=” & inUID & “;pwd=” & inPWD & “;”
Set conn = CreateObject(”ADODB.Connection”)
conn.ConnectionString = strConnect
conn.Open
End Sub

Private Sub getMyRows(inSchema As String, InTable As String)
Dim RS As Object
Dim TableSQL As String
Dim DataType As String
Dim DataLength As String
Dim DataPrecision As String
Dim DataScale As String
Dim ColCount As Integer
Dim WS As Worksheet
‘ create a sheet with the current table as name
Worksheets.Add().Name = InTable
Set RS = CreateObject(”ADODB.recordset”)
On Error GoTo GetOut
TableSQL = “Select * from ” & inSchema & “.” & InTable
‘ grab the data
RS.Open TableSQL, conn, adOpenStatic
For ColCount = 0 To RS.Fields.Count – 1
‘ set column headings to match table
ActiveSheet.Cells(1, ColCount + 1).Value = RS.Fields(ColCount).Name
Next

‘ copy table data to sheet
With Worksheets(InTable).Range(”A2″)
On Error GoTo GetOut
.CopyFromRecordset RS
End With
RS.Close

‘ a little formatting
ActiveSheet.Range(”A1″).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Columns.AutoFit
ActiveSheet.Rows(”1:1″).Select
Selection.Font.Bold = True

‘ track how many successes
MyGoodCount = MyGoodCount + 1
Exit Sub
GetOut:
‘ track errors and continue
MyErrors = MyErrors & “,” & InTable
MyBadCount = MyBadCount + 1
End Sub
Private Sub NewGetMyRows(inSchema As String, InTable As String)
Dim RST As Object
Dim TableSQL As String
Dim DataType As String
Dim DataLength As String
Dim DataPrecision As String
Dim DataScale As String
Dim ColCount As Integer
Dim WS As Worksheet

‘ this might be useful if we run into very long fields, HOWEVER, that’ll usually be clobs and those won’t work anyhow
Worksheets.Add().Name = InTable
Set RST = CreateObject(”ADODB.recordset”)
TableSQL = “Select * from ” & inSchema & “.” & InTable
‘ On Error GoTo GetOut
RST.Open TableSQL, conn
i = 1
For Each fld In RST.Fields
Worksheets(InTable).Cells(2, i).Value = fld.Name
i = i + 1
Next fld

Dim j As Long, k As Long

With Worksheets(InTable)
For j = 1 To RST.RecordCount
For k = 1 To RST.Fields.Count
If IsNull(RST(k – 1)) Then
.Cells(j + 2, k) = Empty
Else
If Len(RST(k – 1)) > 255 Then
For i = 0 To Int(Len(RST(k – 1)) / 255)
.Cells(j + 2, k).Value = .Cells(j + 2, k).Value & Mid(RST(k – 1), (i * 255) + 1, 255)
Next i
Else
.Cells(j + 2, k).Value = RST(k – 1)
End If
End If
Next k
RST.MoveNext
Next j
End With
RST.Close
Exit Sub
GetOut:
MsgBox InTable
‘ RST.Close
End Sub

Sub getTables(inUser As String, inPW As String, inDB As String, inSchema As String, inNameString As String)
Dim RSTables As Object
Dim TableListSQL As String
Dim SchemaName As String
Dim errtext As String
MyGoodCount = 0
MyBadCount = 0
Set RSTables = CreateObject(”ADODB.recordset”)
ConnectToOracle inUser, inPW, inDB
‘ grab list of tables and call getMyRows for each table
TableListSQL = “Select table_name from dba_tables where owner = ‘” & inSchema & “‘ and table_name like ‘” & inNameString & “‘ order by table_name”
RSTables.Open TableListSQL, conn, adOpenStatic
Do While Not RSTables.EOF
getMyRows inSchema, (RSTables(”table_name”))
RSTables.MoveNext
Loop
RSTables.Close
‘ set hint if there were errors
If MyBadCount > 0 Then
errtext = “(Errors may be caused by CLOB/LOB columns)”
Else
errtext = “”
End If
‘ report results
MsgBox “Completed! Good:” & MyGoodCount & ” Bad:” & MyBadCount & ” ” & errtext & ” Bad Tables:” & MyErrors

End Sub