AdvancedHMI Software
General Category => Open Discussion => Topic started by: bachphi on August 03, 2018, 08:25:38 PM
-
FileSystemWatcher (FSW) is pretty useful to monitor a certain folder for new file creation, a text file that usually contains some result data. The file is processed by first parsing its content, next send its parsed data on to a SQL DB and finally move the file to an archived folder.
The key here is to know when process of file creation is complete. Most solutions are either relying on regular interval timer check or use several of FSW notify filters ( CreationTime, FileName, LastWrite, Size ) to trigger event and then trying to open the file to check for its completion status. This approach is fine for low rate of file creation, but if there's a lot of files created in a short period of time, buffer overflow and missing file watching can occur.
FSW use a limited & non-paged memory and therefore long file name, deep level of sub directories, filter can all affect its performance. Increase the InternalBufferSize can help but MS only recommend its maximum size of 64KB.
Private Sub FileWatcher()
Dim fsw As New FileSystemWatcher With {
.Path = sourceDir,
.InternalBufferSize = 65536,
.IncludeSubdirectories = True,
.Filter = "*.DAT",
.NotifyFilter = (NotifyFilters.LastWrite)
}
AddHandler fsw.Changed, New FileSystemEventHandler(AddressOf OnChanged)
fsw.EnableRaisingEvents = True
End Sub
Perhaps, the most logical choice is the LastWrite notify filter. Oddly enough, LastWrite will actually trigger events twice, it is the second trigger mark the end of file writing and so it's good time to open the file to parse its content.
Private _fileCreated As Boolean = False
Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
If _fileCreated Then 'catching the second trigger event
ParseFileContent(e.FullPath)
'End If
_fileCreated = Not _fileCreated
End Sub
The next logical one is the Size NotifyFilter, it trigger only once and appear to be at the end of file writing. I added the while loop for self assurance, may not needed:
Private Sub OnChanged(ByVal source As Object, ByVal e As FileSystemEventArgs)
Dim fileSize As Long = 0
Dim currentFile As New FileInfo(e.FullPath)
While Not (FileIsReady(e.FullPath))
fileSize = currentFile.Length
Thread.Sleep(25)
currentFile.Refresh()
End While
ParseFileContent(e.FullPath)
End Sub
Private Function FileIsReady(ByVal path As String) As Boolean
Try
Using file = System.IO.File.Open(path, FileMode.Open, FileAccess.ReadWrite, FileShare.None)
Return True
End Using
Catch __unusedIOException1__ As IOException
Return False
End Try
End Function
-
FSW can not watch for files that are already exist in the folder prior to the application running, therefore we need to check for these existing files and process them as well.
You may create a separate thread at the application startup and check for these existing files
Dim thr As New Thread(AddressOf CheckFilesPreExist)
thr.Start()
and the sub
Sub CheckFilesPreExist()
Dim files = Directory.EnumerateFiles(sourceDir, "*.DAT", SearchOption.AllDirectories)
If (files.Count > 0) Then
For Each Fil As String In files
Dim fileName = Path.GetFileName(Fil)
ParseFileContent(Fil)
Next
End If
End Sub
ParseFileContent parse the result data text file. If the file type is comma delimited type, one can use TextFieldParser to parse its content. The goal here is to put everything in one string to send to SQL DB.
Private Sub ParseFileContent(ByVal filepath As String)
Dim currentRow As String()
Dim intCount As Int16 = 0
txtFile = New FileIO.TextFieldParser(filepath)
txtFile.TextFieldType = FileIO.FieldType.Delimited
txtFile.SetDelimiters(";")
txtFile.CommentTokens = {"Parameter", "Leakage"}
'Skip header row
txtFile.ReadFields()
Try
While Not txtFile.EndOfData
currentRow = txtFile.ReadFields() 'Read all field in a row into a string array
If currentRow.Length = 8 Then
str = currentRow
str = (String.Join(";", str) & ";").Split(";") 'Add new col8 . Length = 9
' ...
End If
If currentRow.Length = 6 Then 'selecting parameters data to record
Select Case currentRow(0)
' ...
End Select
End If
End While
Catch ex As Exception
Debug.Print(ex.Message)
End Try
txtFile.Close() 'close the open file
If str IsNot Nothing Then
Invoke(New MethodInvoker(Sub() Me.dgvCSV.Rows.Add(str))) ' display data on a DataGridView
End If
For i = 0 To 5
SQL.AddParam("@col" & i, str(i))
Next
SQL.ExecQuery("INSERT INTO TB_Data (SerialNo, ResDate, ResTime, NioBits, Th, Pn) " &
"VALUES (@col0,@col1,@col2,@col3,@col4,@col5);", True)
If SQL.HasException(True) Then Exit Sub
End Sub
In the above example, I use SQL from a class, courtesy from VB Toolbox user@YouTube which made the challenging of dealing with SQL DB so much easier and more flexible. For example, in my execQuery statement, with the 'True' option being on, I can now check for the last row insert with
If SQL.DBDT.Rows.Count > 0 Then
Dim r As DataRow = SQL.DBDT.Rows(0)
MsgBox(r("LastID").ToString)
End If
-
well that's sounds wonderful, are you planning to publish a book or something?
-
That would probably be "The Book of Nonsense".