FromUser = InputBox("Enter Date(e.g. 01-18): ", "Set Filename")
FlatFileName = "BYLOAN" & FromUser & "-2001"

Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Popup "Processing Flat File: " & FlatFileName & ".csv...", 3, "Flat File Scrub - Individual", 64

Set WshShell = Nothing

Set AnFSO = Wscript.CreateObject("Scripting.FileSystemObject")
Set TheFile = AnFSO.GetFile("C:\JOHN\Pending\Temp\In\" & FlatFileName & ".csv")
Set TheInputFile = TheFile.OpenAsTextStream(1)

OutPutBuffer = ""
CurrentLineCount = 0
DelimiterErrorCount = 0

DelimiterErrors = ""
Report = "" & vbcrlf & vbcrlf 
     
Do While Not TheInputFile.AtEndOfStream
     
  InputBuffer = TheInputFile.ReadLine
  CurrentLineCount = CurrentLineCount + 1
 
  ' Delete extra comma in employee field and get rid of white space
    
  LastCommaPosition = 1
  CommaCount = 0
  
  Do
    
    LastCommaFoundPosition = InStr(LastCommaPosition, InputBuffer, ",", 1)
    
    If LastCommaFoundPosition > 0 Then  
      CommaCount = CommaCount + 1
      Select Case CommaCount
        Case 5
          EmployeeNameFieldStart = LastCommaFoundPosition + 1
        Case 6
          EmployeeNameFieldEnd1 = LastCommaFoundPosition - 1
        Case 7
          EmployeeNameFieldEnd2 = LastCommaFoundPosition - 1
      End Select
      LastCommaPosition = LastCommaFoundPosition + 1
    Else
      Exit Do
    End If
    
  Loop While Not LastCommaPosition > Len(InputBuffer)
    
  Select Case CommaCount
     
    Case 22

      If Right(InputBuffer, 1) = "," Then
      
        InputBuffer = left(InputBuffer, Len(InputBuffer) - 1)
      
      Else
      
      InputBuffer = Left(InputBuffer, EmployeeNameFieldStart - 1) & _
                    Replace(InputBuffer, ",", "", EmployeeNameFieldStart, 1, 1)
                      
      InputBuffer = Replace(Left(InputBuffer, EmployeeNameFieldStart - 1), " ", "", 1, -1, 1) & _
                    Replace(Trim(Mid(InputBuffer, EmployeeNameFieldStart, EmployeeNameFieldEnd2 - EmployeeNameFieldStart)), "  ", " ", 1, -1, 1) & _
                    Replace(InputBuffer, " ", "", EmployeeNameFieldEnd2 + 1, -1, 1)

      End If
      
    Case 21

      If CurrentLineCount = 1 Then
  
        InputBuffer = Replace(Left(InputBuffer, EmployeeNameFieldStart - 1), " ", "", 1, -1, 1) & _
                      Replace(Trim(Mid(InputBuffer, EmployeeNameFieldStart, EmployeeNameFieldEnd1 - EmployeeNameFieldStart + 1)), " ", "", 1, -1, 1) & _
                      Replace(InputBuffer, " ", "", EmployeeNameFieldEnd1 + 1, -1, 1)
      Else

        InputBuffer = Replace(Left(InputBuffer, EmployeeNameFieldStart - 1), " ", "", 1, -1, 1) & _
                      Replace(Trim(Mid(InputBuffer, EmployeeNameFieldStart, EmployeeNameFieldEnd1 - EmployeeNameFieldStart + 1)), "  ", " ", 1, -1, 1) & _
                      Replace(InputBuffer, " ", "", EmployeeNameFieldEnd1 + 1, -1, 1)
      End If
    
    Case Else

      DelimiterErrorCount = DelimiterErrorCount + 1
      DelimiterErrors = DelimiterErrors & vbcrlf & "Line: " & CurrentLineCount & " CommaCount: " & _
                        CommaCount

  End Select

  ' Field Name Corrections
  
  If CurrentLineCount = 1 Then
  
    Select Case Ucase(Trim(Left(InputBuffer, 6)))
      Case "1RUNDA"
        InputBuffer = Replace(InputBuffer, "1RUNDAT", "RUNDATE")
      Case "RUNDAT"
        InputBuffer = Replace(InputBuffer, "RUNDAT", "RUNDATE")
    End Select
  
    InputBuffer = Replace(InputBuffer, "NAME", "EMPLOYEE")
    InputBuffer = Replace(InputBuffer, ",ADJ,", ",J,")
    InputBuffer = Replace(InputBuffer, ",OT,", ",T,")
    InputBuffer = Replace(InputBuffer, "SSN", "BEMSID")
    InputBuffer = Replace(InputBuffer, "CLOCK#", "BEMSID")
  
  End If
  
  ' Add processed line to OutputBuffer
  
  OutputBuffer = OutputBuffer & InputBuffer & VbCrLf
  
Loop

' Close the input file and write out the output file
    
TheInputFile.Close

Set TheOutputFile = AnFSO.CreateTextFile("C:\JOHN\Pending\Temp\Out\" & FlatFileName & ".csv", True)
TheOutputFile.Write OutputBuffer
TheOutputFile.Close

If DelimiterErrorCount > 0 Then

  Report = Report & " Delimiter errors encountered for file: " & FlatFileName & ".csv" & vbcrlf & _
         " -------------------------------" & vbcrlf & DelimiterErrors

  Set TheOutputFile = AnFSO.CreateTextFile("C:\JOHN\Pending\Temp\Out\" & FlatFileName & ".txt", True)
  TheOutputFile.Write Report
  TheOutputFile.Close

  ToUser = Msgbox("Done - delimiter errors are in the report", VBInformation, "Finished Processing " & FlatFileName & ".csv")

Else

  ToUser = Msgbox("Done - no delimiter errors :)", VBInformation, "Finished Processing " & FlatFileName & ".csv")

End If



set AnFSO = Nothing

Wscript.Quit