Transfer a Text File into an Oracle Database using an Excel Macro

2 12 2009

December 2, 2009

The following Excel macro shows how to open a text file as a database using ADO, create a table in Oracle, and then transfer the rows from the text file into the database table using bind variables.  Once the transfer finishes, a worksheet in Excel is populated with the data from the Oracle table.

Private Sub TransferRows()
    'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting
    Dim i As Integer
    Dim intResult As Integer
    Dim strSQL As String
    Dim strTable As String
    Dim strDBTable As String
    Dim snpData As ADODB.Recordset
    Dim comDataInsert As ADODB.Command

    On Error Resume Next

    strTable = Sheets("ExcelQueryofTextFile").Cells(1, 2).Value
    strTable = Right(strTable, Len(strTable) - InStrRev(strTable, "\"))
    'Set the database table name to the file name without the extension
    strDBTable = UCase(Left(strTable, InStr(strTable, ".") - 1))

    Sheets("ExcelQueryofTextFile").Range("A5:M10006").Delete Shift:=xlUp

    Set snpData = New ADODB.Recordset

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  [Source IP Address]," & vbCrLf
    strSQL = strSQL & "  [Destination IP Address]," & vbCrLf
    strSQL = strSQL & "  [Time]," & vbCrLf
    strSQL = strSQL & "  [Source Port]," & vbCrLf
    strSQL = strSQL & "  [Destination Port]," & vbCrLf
    strSQL = strSQL & "  [L3 Protocol]," & vbCrLf
    strSQL = strSQL & "  [Application Path]," & vbCrLf
    strSQL = strSQL & "  [Application Description]," & vbCrLf
    strSQL = strSQL & "  [Rule Description]" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  [" & strTable & "]" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  [Source IP Address]," & vbCrLf
    strSQL = strSQL & "  [Time]"
    snpData.Open strSQL, dbFile

    If snpData.State = 1 Then
        strSQL = "CREATE TABLE " & strDBTable & "(" & vbCrLf
        strSQL = strSQL & "  SOURCE_IP VARCHAR2(16)," & vbCrLf
        strSQL = strSQL & "  DESTINATION_IP VARCHAR2(16)," & vbCrLf
        strSQL = strSQL & "  ACCESS_TIME DATE," & vbCrLf
        strSQL = strSQL & "  SOURCE_PORT NUMBER(12)," & vbCrLf
        strSQL = strSQL & "  DESTINATION_PORT NUMBER(12)," & vbCrLf
        strSQL = strSQL & "  PROTOCOL VARCHAR2(15)," & vbCrLf
        strSQL = strSQL & "  APPLICATION_PATH VARCHAR2(100)," & vbCrLf
        strSQL = strSQL & "  APPLICATION_DESCRIPTION VARCHAR2(100)," & vbCrLf
        strSQL = strSQL & "  RULE_DESCRIPTION VARCHAR2(30))" & vbCrLf
        dbVMFG.Execute strSQL

        Set comDataInsert = New ADODB.Command
        With comDataInsert
            strSQL = "INSERT INTO " & strDBTable & "(" & vbCrLf
            strSQL = strSQL & "  SOURCE_IP," & vbCrLf
            strSQL = strSQL & "  DESTINATION_IP," & vbCrLf
            strSQL = strSQL & "  ACCESS_TIME," & vbCrLf
            strSQL = strSQL & "  SOURCE_PORT," & vbCrLf
            strSQL = strSQL & "  DESTINATION_PORT," & vbCrLf
            strSQL = strSQL & "  PROTOCOL," & vbCrLf
            strSQL = strSQL & "  APPLICATION_PATH," & vbCrLf
            strSQL = strSQL & "  APPLICATION_DESCRIPTION," & vbCrLf
            strSQL = strSQL & "  RULE_DESCRIPTION)" & vbCrLf
            strSQL = strSQL & "VALUES(" & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?," & vbCrLf
            strSQL = strSQL & "  ?)" & vbCrLf

            .Parameters.Append .CreateParameter("source_ip", adVarChar, adParamInput, 16)
            .Parameters.Append .CreateParameter("destination_ip", adVarChar, adParamInput, 16)
            .Parameters.Append .CreateParameter("access_time", adDate, adParamInput, 8 )
            .Parameters.Append .CreateParameter("source_port", adNumeric, adParamInput, 8 )
            .Parameters.Append .CreateParameter("destination_port", adNumeric, adParamInput, 8 )
            .Parameters.Append .CreateParameter("protocol", adVarChar, adParamInput, 15)
            .Parameters.Append .CreateParameter("application_path", adVarChar, adParamInput, 100)
            .Parameters.Append .CreateParameter("application_description", adVarChar, adParamInput, 100)
            .Parameters.Append .CreateParameter("rule_description", adVarChar, adParamInput, 30)

            'Set up the command properties
            .CommandText = strSQL
            .CommandType = adCmdText
            .CommandTimeout = 30
            .ActiveConnection = dbVMFG
        End With

        If Err = 0 Then
            dbVMFG.BeginTrans
            Do While Not snpData.EOF
                comDataInsert("source_ip") = snpData(0).Value
                comDataInsert("destination_ip") = snpData(1).Value
                comDataInsert("access_time") = CDate(snpData(2).Value)
                comDataInsert("source_port") = Val(snpData(3).Value)
                comDataInsert("destination_port") = Val(snpData(4).Value)
                comDataInsert("protocol") = Left(snpData(5).Value, 15)
                comDataInsert("application_path") = Left(snpData(6).Value, 100)
                comDataInsert("application_description") = Left(snpData(7).Value, 100)
                comDataInsert("rule_description") = Left(snpData(8).Value, 30)
                comDataInsert.Execute

                snpData.MoveNext
            Loop

            snpData.Close
            If Err = 0 Then
                dbVMFG.CommitTrans
            Else
                dbVMFG.RollbackTrans
            End If

            strSQL = "SELECT" & vbCrLf
            strSQL = strSQL & "  SOURCE_IP," & vbCrLf
            strSQL = strSQL & "  DESTINATION_IP," & vbCrLf
            strSQL = strSQL & "  ACCESS_TIME," & vbCrLf
            strSQL = strSQL & "  SOURCE_PORT," & vbCrLf
            strSQL = strSQL & "  DESTINATION_PORT," & vbCrLf
            strSQL = strSQL & "  PROTOCOL," & vbCrLf
            strSQL = strSQL & "  APPLICATION_PATH," & vbCrLf
            strSQL = strSQL & "  APPLICATION_DESCRIPTION," & vbCrLf
            strSQL = strSQL & "  RULE_DESCRIPTION" & vbCrLf
            strSQL = strSQL & "FROM" & vbCrLf
            strSQL = strSQL & "  " & strDBTable & vbCrLf
            strSQL = strSQL & "ORDER BY" & vbCrLf
            strSQL = strSQL & "  SOURCE_IP," & vbCrLf
            strSQL = strSQL & "  ACCESS_TIME"
            snpData.Open strSQL, dbVMFG

            If snpData.State = 1 Then
                'The fast way to place the query results into cells
                For i = 0 To snpData.Fields.Count - 1
                    Sheets("ExcelQueryofTextFile").Cells(5, i + 1).Value = snpData.Fields(i).Name
                Next i
                Sheets("ExcelQueryofTextFile").Range(Sheets("ExcelQueryofTextFile").Cells(5, 1), Sheets("ExcelQueryofTextFile").Cells(5, snpData.Fields.Count)).Font.Bold = True

                Sheets("ExcelQueryofTextFile").Range("A6").CopyFromRecordset snpData

                snpData.Close

            End If
        Else
            intResult = MsgBox("Could not create the table " & strDBTable & " in the database." & vbCrLf & Error(Err), 16, "Excel Demo")
        End If
    End If

    Sheets("ExcelQueryofTextFile").Range("A6").Select
    ActiveWindow.FreezePanes = True

    Set snpData = Nothing
    Set comDataInsert = Nothing
End Sub

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 148 other followers

%d bloggers like this: