Read from CSV file, write directly to data source
I'm a beginner to VB2008 and trying to "convert" an Access DB to a stand alone application. I have under estimated the differences between VBA and VB. Although I have many questions, I'll focus on this topic first.
The Access DB collects and maintains SNMP traps from a radio station. I import CSV files from a remote Unix server via FTP, parse the text line-by-line, and add to a table as a new record. So far, I can parse the text and add to a dataset but I want to write directly to the data source. I also need error handling that will trap errors for duplicate records. There is a Reconcile function that ensures that all lines in the CSV files are added to the table before the files are permanently deleted. Here's what I have so far:
Code:
Public Class clsRnm
Private msTrapTable As String
Private moTrap As clsRnmTrap
Private moAppendStat As clsTrapFileAppendStat
Private mlSystemID As Long
Public Sub New(ByVal lSystemID As Long)
mlSystemID = lSystemID
moTrap = New clsRnmTrap
moAppendStat = New clsTrapFileAppendStat
End Sub
Public Function AppendTrapFile(ByVal sFullPath As String) As clsTrapFileAppendStat
Dim oSR As StreamReader
Dim oTA As New RnmDataSetTableAdapters.tblRnmTrapsTableAdapter
Dim oStats As New clsTrapFileAppendStat
Dim iOK, iBad, iDup As Integer
On Error GoTo ErrProc
If My.Computer.FileSystem.FileExists(sFullPath) = False Then
oStats.Message = "Error: File Not Found"
GoTo ExitProc
End If
oStats.FileName = My.Computer.FileSystem.GetName(sFullPath)
oSR = New StreamReader(sFullPath)
Do Until oSR.EndOfStream
moTrap = SplitTrapLine(oSR.ReadLine)
If moTrap.EventType = "A" Then
If moTrap.Err = 0 Then
With moTrap
oTA.Insert( _
ArrivalID:=.ArrivalID, _
SystemID:=mlSystemID, _
TimeStamp:=.TimeStamp, _
EventDate:=.EventDate, _
ObjectName:=.ObjectName, _
LevelID:=.LevelID, _
Message:=.Message, _
ObjectClass:=.ObjectClass, _
RegionID:=.RegionID, _
SiteID:=.SiteID)
End With
iOK = iOK + 1
Else
'An error was encountered in the SplitTrapLine function
iBad = iBad + 1
End If
End If
DuplicateEntryResume:
Loop
oStats.ErrNum = 0
oStats.Message = "Complete: " & oStats.FileName & " " & iOK & "/" & iDup & "/" & iBad
oStats.EventsAdded = iOK
AppendStat.InvalidLines = iBad
oStats.DuplicateEvents = iDup
ExitProc:
AppendTrapFile = oStats
MsgBox(AppendTrapFile.Message)
Exit Function
ErrProc:
Select Case Err.Number
Case 5
' Error triggered on .ExecuteNonQuery when ArrivalID is a duplicate
iDup = iDup + 1
Resume DuplicateEntryResume
Case Else
oStats.ErrNum = Err.Number
oStats.Message = "Error: " & Err.Number & " - " & Err.Description
GoTo ExitProc
End Select
End Function
Private Function SplitTrapLine(ByVal sTrapLine As String) As clsRnmTrap
Dim sTrapElement() As String
On Error GoTo ErrProc
sTrapElement = Split(sTrapLine, ",")
With moTrap
.EventType = Trim(sTrapElement(0))
.TimeStamp = Trim(sTrapElement(1))
.EventDate = Trim(sTrapElement(2))
.ObjectName = Trim(sTrapElement(3))
.LevelID = Trim(sTrapElement(4))
.Message = Trim(sTrapElement(5))
.ObjectClass = Trim(sTrapElement(6))
.RegionID = Trim(sTrapElement(7))
.SiteID = Trim(sTrapElement(8))
.AocID = Trim(sTrapElement(9))
.TrapID = Trim(sTrapElement(10))
.SequenceNum = Trim(sTrapElement(11))
.Host = Trim(sTrapElement(12))
.User = Trim(sTrapElement(13))
.ArrivalTime = Trim(sTrapElement(14))
.ArrivalID = Trim(sTrapElement(15))
.Err = 0
End With
SplitTrapLine = moTrap
Exit Function
ErrProc:
moTrap.Err = Err.Number
End Function
End Class
Other suggestions that may help with technique are welcome.
Re: Read from CSV file, write directly to data source
I think I figured it out.
Code:
Public Function AppendTrapFile(ByVal sFullPath As String) As clsTrapFileAppendStat
Dim sConnStr As String
Dim sSql As String
Dim sDataSource As String
Dim oSR As StreamReader
Dim iOK, iBad, iDup As Integer
If My.Computer.FileSystem.FileExists(sFullPath) = False Then
moAppendStat.Message = "Error: File Not Found"
AppendTrapFile = moAppendStat
End If
'Test for valid SystemID here
sDataSource = "C:\Users\Randy\Documents\MS Access\RNM\rnm_Data_2011-08-12.mdb"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDataSource
sSql = "INSERT INTO tblRnmTraps (ArrivalID, SystemID, [TimeStamp], EventDate, ObjectName, " & _
"LevelID, Message, ObjectClass, RegionID, SiteID) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Try
Dim cn As New OleDbConnection(sConnStr)
Dim cmd As New OleDbCommand(sSql, cn)
moAppendStat.FileName = My.Computer.FileSystem.GetName(sFullPath)
oSR = New StreamReader(sFullPath)
Do Until oSR.EndOfStream
moTrap = SplitTrapLine(oSR.ReadLine)
If moTrap.EventType = "A" Then
If moTrap.Err = 0 Then
cmd.Parameters.Add("ArrivalID", OleDbType.Char).Value = moTrap.ArrivalID
cmd.Parameters.Add("SystemID", OleDbType.Integer).Value = mlSystemID
cmd.Parameters.Add("TimeStamp", OleDbType.Integer).Value = moTrap.TimeStamp
cmd.Parameters.Add("EventDate", OleDbType.Date).Value = moTrap.EventDate
cmd.Parameters.Add("ObjectName", OleDbType.Char).Value = moTrap.ObjectName
cmd.Parameters.Add("LevelID", OleDbType.Integer).Value = moTrap.LevelID
cmd.Parameters.Add("Message", OleDbType.Char).Value = moTrap.Message
cmd.Parameters.Add("ObjectClass", OleDbType.Char).Value = moTrap.ObjectClass
cmd.Parameters.Add("RegionID", OleDbType.Integer).Value = moTrap.RegionID
cmd.Parameters.Add("SiteID", OleDbType.Integer).Value = moTrap.SiteID
cn.Open()
Try
'Test for duplicate record in table
cmd.ExecuteNonQuery()
iOK = iOK + 1
Catch ex As OleDbException When ex.TargetSite.Name = "ExecuteCommandTextErrorHandling"
'Duplicate record exists
iDup = iDup + 1
Finally
cn.Close()
End Try
Else
'An error was encountered in the SplitTrapLine function
iBad = iBad + 1
End If
End If
Loop
moAppendStat.Message = "Complete: " & moAppendStat.FileName & " " & iOK & "/" & iDup & "/" & iBad
moAppendStat.ErrNum = 0
Catch ex As OleDbException
MsgBox(ex.TargetSite.Name)
Catch ex As InvalidOperationException
MsgBox(ex.TargetSite.Name)
Finally
moAppendStat.EventsAdded = iOK
moAppendStat.InvalidLines = iBad
moAppendStat.DuplicateEvents = iDup
AppendTrapFile = moAppendStat
MsgBox(AppendTrapFile.Message)
End Try
End Function
