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
Recent Comments