Code:
Sub DReport()
Dim fso As New FileSystemObject
Dim Tst As TextStream
Dim strline As String
Dim strFilePath As String
Dim i As Integer
Dim Strfilename As String
Dim objDB As DAO.Database
Dim mylog As DAO.Recordset
Dim objFSO As Scripting.FileSystemObject
Dim strInfile As String
Dim operator As String
Dim Username As String
Dim Tester As String
Dim MyArray() As String
Dim ArrayOperator() As String
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from ABI;"
DoCmd.SetWarnings True
strInfile = "\\project\ABI_testing\CSV_files\"
Strfilename = "Sea_testing.csv"
strFilePath = strInfile & Strfilename
Set objDB = CurrentDb()
Set mylog = objDB.OpenRecordset("ABI")
i = 1
' Reading .CSV file and creating ABI table Add a new record
If fso.FileExists(strFilePath) Then
Set Tst = fso.OpenTextFile(strFilePath, ForReading, False)
Do Until Tst.AtEndOfStream
' Reading .CSV file and creating ABI table Add a new record
strline = Tst.ReadLine
If (i = 1) Then
Dim file_name As String
file_name = Replace(Mid(strline, InStr(strline, ":") + 1), ",", "")
End If
If (i = 3) Then
Username = Trim(Replace(Mid(strline, InStr(strline, ":") + 1), ",", ""))
If Trim(Username) = "xx9" Then operator = "Reporter 1"
ElseIf Trim(Username) = "kkk2" Then operator = "Reporter 2"
End If
ArrayOperator = Split(operator, " ")
If (i = 8) Then
Dim rundate As String
If Mid(strline, InStr(strline, ":") + 1) Like "*,,,*" Then
rundate = Trim(Replace(Mid(strline, InStr(strline, ":") + 1), ",,,", ""))
Else
rundate = Trim(Replace(Mid(strline, InStr(strline, ":") + 1), ",,", ""))
End If
Dim Date_Tested As Date
Dim DateTime_Tested As Date
Date_Tested = DateValue(Mid([rundate], InStr([rundate], ",") + 2))
DateTime_Tested = CVDate(Mid([rundate], InStr([rundate], ",") + 1))
End If
If (i > 28) Then
Dim CT_num As Integer
Dim ID_NUMBER As Variant
Dim PRIMER_PROBE_SET As String
Dim temp_str As String
temp_str = Mid(strline, InStr(strline, ":") + 1)
MyArray = Split(temp_str, ",")
mylog.AddNew
mylog![Run_file_Name] = file_name
mylog![Username] = Username
mylog![operator] = operator
mylog![Tester] = Left(ArrayOperator(0), 1) & ArrayOperator(1)
mylog![rundate] = rundate
mylog![Date_Tested] = Date_Tested
mylog![DateTime_Tested] = DateTime_Tested
mylog![SampleID] = UCase(MyArray(1))
If IsNumeric(MyArray(1)) Then
mylog![ID_NUMBER] = MyArray(1)
Else
mylog![ID_NUMBER] = 0
End If
mylog.Update
End If
i = i + 1
Loop
Tst.Close
End If
mylog.Close ' Temporarily close the report log table
DoCmd.SetWarnings False
End Sub