My Zen Arcade

Just a little geek moment here.
For my job, I do a lot of dataloads. Its actually kind of annoying because ctl files are kind of annoying.
ANYHOO, I got tired of writing the things and the typos and such, so I made a little VBA app in Excel.
The code assumes that the column names are in the first row and are correct.
Basically, it grabs a column name, goes and gets information on the column from the database, and then uses that to make an entry in the control file.
Then it exports the data file.
Note that all I promise from this is that it works for me and works quickly (translation: its a hack, but a hack that gets it done).
You’ll need to create a form to go with this since certain information has to be entered.

The code is broken into two modules DATABASEPROCS and BUILDDATALOAD, since I use the DATABASEPROCS for other things as well.

MODULE DATABASEPROCS
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

Function ColumnType(inColName As String, inTabName As String, inConn As Object)
‘ attempt to determin the datatype of the column
Dim RSType As Object
Dim TypeSQL As String
Dim DataType As String
Dim DataLength As String
Dim DataPrecision As String
Dim DataScale As String
Dim col_type As String

Set RSType = CreateObject(”ADODB.recordset”)
‘ you can either hardcode owner or, like above, grab it from the form
TypeSQL = “Select distinct data_type,data_length, data_precision,data_scale from all_tab_cols ” & _
“where owner = **WHATEVEROWNER** and upper(table_name) = ‘” & UCase(inTabName) & “‘ and upper(column_name)=’” & UCase(inColName) & “‘”
RSType.Open TypeSQL, inConn
If Not (RSType.BOF And RSType.EOF) Then
‘ go to the first record
‘ Assumes the first record we find is the one to use. May not always be
RSType.MoveFirst
DataType = RSType(”data_type”)
DataLength = RSType(”data_length”)
‘ this bit is required due to funky handling of Strings and NULLs from the DB
If Not IsNull(RSType(”data_precision”)) Then
DataPrecision = RSType(”data_precision”)
Else
DataPrecision = “”
End If
If Not IsNull(RSType(”data_scale”)) Then
DataScale = RSType(”data_scale”)
Else
DataScale = “”
End If

‘ build the datatype based on type, scale, etc.

If DataType = “VARCHAR2″ Then
col_type = DataType & “(” & DataLength & “)”
‘ logic to determin exactly what kind of number/integer
ElseIf (DataType = “NUMBER” And DataPrecision = “” And DataScale = “0″) Then
col_type = “INTEGER”
ElseIf (DataType = “NUMBER” And DataPrecision = “” And DataScale = “”) Then
col_type = “NUMBER”
ElseIf (DataType = “NUMBER” And DataScale = “”) Then
col_type = “NUMBER(” & DataPrecision & “)”
ElseIf (DataType = “NUMBER”) Then
col_type = “NUMBER(” & DataPrecision & “,” & DataScale & “)”
Else
‘ probably just DATE at this point
col_type = DataType
End If
Else
‘ No match. Default to char
col_type = “VARCHAR2(2000)”
End If
ColumnType = col_type
RSType.Close
End Function

Function CtlType(inColName As String, inTabName As String, inConn As Object)
Dim ColType As String
Dim CTLDataType As String

ColType = ColumnType(inColName, inTabName, inConn)
If InStr(ColType, “VARCHAR”) > 0 Then
CTLDataType = Replace(ColType, “VARCHAR2″, “CHAR”)
ElseIf InStr(ColType, “INTEGER”) > 0 Then
CTLDataType = “INTEGER EXTERNAL”
ElseIf InStr(ColType, “NUMBER”) > 0 Then
CTLDataType = “DECIMAL EXTERNAL”
ElseIf InStr(ColType, “DATE”) > 0 Then
CTLDataType = “CHAR(2000) “”decode(length(substr(:” & inColName & “,instr(:” & inColName & “,’/',1,2)+1)),2,to_date(:” & inColName & “,’MM/DD/RR’),to_date(:” & inColName & “,’MM/DD/YYYY’))”"”
Else
CTLDataType = “CHAR(2000)”
End If
CtlType = CTLDataType
End Function

MODULE BUILDDATALOAD
–
Dim NewConn As Object

Public Sub CreateLoadFiles(InLoadID As String, inCOID As String, inTable As String)
Dim colnum As Integer
Dim ctlline(6) As String
Dim FileDir As String
Dim cellval As String
Dim spacing As String
Dim chartext As String
Dim endchar As String
Dim TABDataType As String
Dim CTLDataType As String
Dim LineIndex As Integer
Dim lArr

Dim retVal As Variant
Dim NewConn As Object

Set NewConn = CreateObject(”ADODB.Connection”)
NewConn = DataBaseProcs.ConnectToOracle inUser, inPW, inDB
NewConn.Open

endchar = “,”
ctlchartext = “char(2000)”
spacing = ” ”
ctlline(0) = “Load Data”
ctlline(1) = “INFILE ‘” & InLoadID & “.dat’”
ctlline(2) = “APPEND INTO Table load_” & inTable
ctlline(3) = “FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘” & Chr(34) & “‘”
ctlline(4) = “TRAILING NULLCOLS ”
‘ continue populating ctlline with whatever your control files should start with. Mine start with a sequence and constant
ctlline(5) = “(id sequence,”
ctlline(6) = “load_id CONSTANT ‘” & InLoadID & “‘,”

colnum = 1
‘ the control file
FileDir = “C:\Dataloads\” & InLoadID & “\”
MkDir (FileDir)
Open FileDir & InLoadID & “.ctl” For Append As #1
‘ lines every control file starts with
‘ modified based on your ctllines above
For LineIndex = 0 To 6
Print #1, ctlline(LineIndex)
Next LineIndex
‘ start at 1,1 and go across the top row until null

Do Until IsEmpty(ActiveSheet.Cells(1, colnum).Value)
‘ if next field is null, then this is the last field and end of file. Different line-end character
cellval = Replace(Replace(ActiveSheet.Cells(1, colnum).Text, ” “, “”), “-”, “”)
If IsEmpty(ActiveSheet.Cells(1, colnum + 1).Value) Then
endchar = “)”
End If
CTLDataType = DataBaseProcs.CtlType(cellval, inTable, NewConn)

‘ Output to file
Print #1, cellval & spacing & CTLDataType & endchar
colnum = colnum + 1
Loop
Close #1

ActiveWorkbook.SaveAs Filename:=FileDir & InLoadID & “.dat”, FileFormat:=xlCSV, CreateBackup:=False
‘ retVal = Shell(”sqlldr **USER**/**PASSWORD**@**SID** control=” & FileDir & InLoadID & “.ctl data= ” & FileDir & InLoadID & “.dat log=” & FileDir & InLoadID & “.log”, vbNormalFocus)
End Sub