Public Sub ReadLineByLine(strFile As String)
' ***************************************************
' * Open a Text File And Loop Through It *
' ***************************************************
Dim intFile As Integer
Dim strIn As String
Dim strOut As String
Dim strSegment As String
Dim strMOM As String
Dim strDepartment As String
Dim TheSegment As String
Dim ThePrintDate As String
Dim ThePage As String
Dim TheMOM As String
Dim TheDepartment As String
intFile = FreeFile()
Open strFile For Input As #intFile
Do While Not EOF(intFile)
Line Input #intFile, strIn
strIn = CleanField(strIn)
If Left(strIn, Len("Segment")) = "Segment" Then
strSegment = strIn
'Debug.Print strSegment
TheSegment = Trim(Split(strSegment, ":")(1))
TheSegment = Trim(Split(TheSegment, " ")(0))
' Debug.Print TheSegment
ThePrintDate = Trim(Split(strSegment, TheSegment)(1))
ThePrintDate = Trim(Split(ThePrintDate, " ")(2))
'Debug.Print ThePrintDate
ThePage = Mid(strSegment, InStr(strSegment, "Page") + Len("Page"))
ThePage = Trim(ThePage)
'Debug.Print ThePage
ElseIf Left(strIn, Len("MOM ID:")) = "MOM ID:" Then
strMOM = strIn
TheMOM = Trim(Split(strMOM, ":")(1))
TheMOM = (Replace(TheMOM, " ", ""))
'Debug.Print TheMOM
ElseIf Left(strIn, Len("Department Name:")) = "Department Name:" Then
strDepartment = strIn
'Debug.Print strDepartment
TheDepartment = Trim(Split(strDepartment, ":")(1))
TheDepartment = Split(TheDepartment, " ")(0)
'Debug.Print TheDepartment
End If
If TheSegment <> "" And ThePrintDate <> "" And ThePage <> "" And TheMOM <> "" And TheDepartment <> "" Then
InsertRecord TheSegment, ThePrintDate, ThePage, TheMOM, TheDepartment
TheSegment = ""
ThePrintDate = ""
ThePage = ""
TheMOM = ""
TheDepartment = ""
End If
Loop
Close #intFile
End Sub
Public Sub testRead()
ReadLineByLine "C:\Users\500989\Documents\Personal\AccessDatabasesVBA\Text Utilities\sample_text.txt"
End Sub
Public Function CleanField(strField As String) As String
'turn spaces and nulls to empty strings
CleanField = strField & ""
' CleanField = (Replace(strField, " ", ""))
'Hidden characters are ASCII 0 to 32 but these are the main ones. You can google the table
'You can loop them all except 32 since that is a space which could be inside your string
CleanField = Replace(CleanField, Chr(9), "") 'Tab
CleanField = Replace(CleanField, Chr(10), "") 'Line Feed
CleanField = Replace(CleanField, Chr(13), "") 'Carriage return
CleanField = Trim(CleanField)
'either leave anwer as "" string or change all to null
End Function
Public Sub InsertRecord(TheSegment As String, ThePrintDate As String, ThePage As String, TheMOM As String, TheDepartment As String)
'Wrint an insert query here
Dim strSql As String
TheSegment = "'" & TheSegment & "'"
ThePage = "'" & ThePage & "'"
ThePrintDate = "'" & ThePrintDate & "'"
'...
strSql = "Insert Into Table1 (Segment,PrintDate,Page,MOM_ID,Department_Name) VALUES ( " & TheSegment & ", " & ThePrintDate & ", " & ThePage & ", ...)"
Debug.Print strSql
'currentdb.executeStrSql
End Sub