Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
strInFile = "B:\EE\EE29139695\Book4.csv"
strOutFile = "B:\EE\EE29139695\Book4.new"
Set objFSO = CreateObject("Scripting.FileSystemObject")
strInFile = objFSO.GetAbsolutePathname(strInFile)
strOutFile = objFSO.GetAbsolutePathname(strOutFile)
If Not objFSO.FileExists(strInFile) Then
Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
Wscript.Quit
End If
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close
arrData = Split(strData, vbCrLf)
For i = 1 To UBound(arrData)
If arrData(i) <> "" Then
' Split this input line into it's fields
arrFields = Split(arrData(i), ",")
' Make sure we have at least 101 fields
If UBound(arrFields) > 6 Then
If arrFields(4) <> "" And arrFields(5) <> "" And arrFields(7) <> "" Then
' Look for matching field value and perform needed edit
If Right(UCase(arrFields(4)), 1) <> "M" And Right(UCase(arrFields(5)), 1) <> "M" Then
strAmPm = GetAmPm(arrFields(4), arrFields(5), arrFields(7))
If strAmPm <> "" Then
arrFields(4) = arrFields(4) & " " & Left(strAmPm, 2)
arrFields(5) = arrFields(5) & " " & Right(strAmPm, 2)
End If
End If
' Calculate lunch hours
arrFields(6) = CalcLunchTime(arrFields(4), arrFields(5), arrFields(7))
arrData(i) = Join(arrFields, ",")
End If
End If
End If
Next
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close
Function GetAmPm(strStartTime, strEndTime, strTotalHours)
Dim dblTotalHours, i, j, datStartTime, datEndTime, arrAmPm
arrAmPm = Array("AM", "PM")
dblTotalHours = CDbl(strTotalHours)
GetAmPm = ""
For i = 0 To 1
For j = 0 To 1
datStartTime = CDate("1/1/1900 " & strStartTime & " " & arrAmPm(i))
datEndTime = CDate("1/1/1900 " & strEndTime & " " & arrAmPm(j))
If datStartTime > datEndTime Then
datEndTime = DateAdd("d", 1, datEndTime)
End If
If Abs(DateDiff("h", datStartTime, datEndTime) - dblTotalHours) <= 2 Then
GetAmPm = arrAmPm(i) & arrAmPm(j)
Exit Function
End If
Next
Next
End Function
Function CalcLunchTime(strStartTime, strEndTime, strTotalHours)
Dim dblTotalHours, datStartTime, datEndTime
dblTotalHours = CDbl(strTotalHours)
datStartTime = CDate("1/1/1900 " & strStartTime)
If Right(strStartTime, 2) = "PM" And Right(strEndTime, 2) = "AM" Then
datEndTime = CDate("1/2/1900 " & strEndTime)
Else
datEndTime = CDate("1/1/1900 " & strEndTime)
End If
CalcLunchTime = DateDiff("h", datStartTime, datEndTime) - dblTotalHours
End Function
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
strInFile = "B:\EE\EE29139695\Book4.csv"
strOutFile = "B:\EE\EE29139695\Book4.new"
Set objFSO = CreateObject("Scripting.FileSystemObject")
strInFile = objFSO.GetAbsolutePathname(strInFile)
strOutFile = objFSO.GetAbsolutePathname(strOutFile)
If Not objFSO.FileExists(strInFile) Then
Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
Wscript.Quit
End If
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
strData = objFile.ReadAll
objFile.Close
arrData = Split(strData, vbCrLf)
For i = 1 To UBound(arrData)
If arrData(i) <> "" Then
' Split this input line into it's fields
arrFields = Split(arrData(i), ",")
' Make sure we have at least 101 fields
If UBound(arrFields) > 6 Then
If arrFields(4) <> "" And arrFields(5) <> "" And arrFields(7) <> "" Then
' Look for matching field value and perform needed edit
If Right(UCase(arrFields(4)), 1) <> "M" And Right(UCase(arrFields(5)), 1) <> "M" Then
strAmPm = GetAmPm(arrFields(4), arrFields(5), arrFields(7))
If strAmPm <> "" Then
arrFields(4) = arrFields(4) & " " & Left(strAmPm, 2)
arrFields(5) = arrFields(5) & " " & Right(strAmPm, 2)
End If
End If
' Calculate lunch hours
arrFields(6) = CalcLunchTime(arrFields(4), arrFields(5), arrFields(7))
arrData(i) = Join(arrFields, ",")
End If
End If
End If
Next
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
objFile.Write Join(arrData, vbCrLf)
objFile.Close
Function GetAmPm(strStartTime, strEndTime, strTotalHours)
Dim dblTotalHours, i, j, datStartTime, datEndTime, arrAmPm
arrAmPm = Array("AM", "PM")
dblTotalHours = CDbl(strTotalHours)
GetAmPm = ""
For i = 0 To 1
For j = 0 To 1
datStartTime = CDate("1/1/1900 " & strStartTime & " " & arrAmPm(i))
datEndTime = CDate("1/1/1900 " & strEndTime & " " & arrAmPm(j))
If datStartTime > datEndTime Then
datEndTime = DateAdd("d", 1, datEndTime)
End If
If Abs(DateDiff("h", datStartTime, datEndTime) - dblTotalHours) <= 2 Then
GetAmPm = arrAmPm(i) & arrAmPm(j)
Exit Function
End If
Next
Next
End Function
Function CalcLunchTime(strStartTime, strEndTime, strTotalHours)
Dim dblTotalHours, datStartTime, datEndTime
dblTotalMins = CDbl(strTotalHours) * 60
datStartTime = CDate("1/1/1900 " & strStartTime)
If Right(strStartTime, 2) = "PM" And Right(strEndTime, 2) = "AM" Then
datEndTime = CDate("1/2/1900 " & strEndTime)
Else
datEndTime = CDate("1/1/1900 " & strEndTime)
End If
CalcLunchTime = (DateDiff("n", datStartTime, datEndTime) - dblTotalMins) / 60
Wscript.Echo strStartTime & ", " & strEndTime & ", " & datStartTime & ", " & datEndTime & ", " & DateDiff("n", datStartTime, datEndTime) & ", " & dblTotalMins & ", " & CalcLunchTime
End Function
'====================================================================================
Sub SavedCode()
Set objFSO = CreateObject("Scripting.FileSystemObject")
If (WScript.Arguments.Count > 0) Then
strInFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
Else
WScript.Echo "No input filename specified."
WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
strOutFile = WScript.Arguments(1)
strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
Else
strOutFile = strInFile
End If
If Not objFSO.FileExists(strInFile) Then
Wscript.Echo "ERROR: Input file """ & strInFile & """ does not exist."
Wscript.Quit
End If
End Sub
Will the input start and end times in the file being read have AM and PM already on all values? Or will there be no AM and PM on any values? Or is there a mix?There will be a mix as original data comes from from two sources, one with AM/PM and one w/o them.
The way you have the data structured with a start date but no end dateAre you referring to Start/End times? In cases where there is no start or end time I guess you can ignore them and no need to do anything.
...makes this a challenging questionIf that turns out to be quite challenging perhaps would close the question and open another one on the remaining issues so you can be awarded properly.
if you want to try it there...I can test for errors or just randomly but real testing would have to wait for users tom.
,a,b,2/1/2019,7:00 PM,12:00,,5,,,
,a,b,2/1/2019,11:45,8:00,,6.25,,,
I got the correct output I believe of:,a,b,2/1/2019,7:00:00 PM,12:00:00 AM,0,5,,,
,a,b,2/1/2019,11:45:00 AM,8:00:00 PM,2,6.25,,,