CSVDataParse.bas

'Created: 28FEB2023, by Kyle Krieger
'PURPOSE: Loop through all .CSV files within a provided folder
'         Parse and perform calculations on time and weekday for further analysis


Sub LoopCSVFilesAndParseData()

Dim wb As Workbook
Dim ws As Worksheet
Dim myFile, myPath, myExtension As String
Dim FldrPicker As FileDialog
Dim lastrow As Long

'Optimize Macro Speed
  Application.CutCopyMode = False
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.StatusBar = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.csv*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      Set ws = wb.Worksheets(1)
      DoEvents
    
      'Configure data boundary variable based on table primary key
      lastrow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
    
        'Create columns to parse time (start, end & difference) & weekday from dataset
        ws.Columns("E:H").Insert
        ws.Columns("I:P").Delete 'removes location data (outside project scope)
    
        'Hard code the newly created column headers
        ws.Range("E1").Value = "start_time"
        ws.Range("F1").Value = "end_time"
        ws.Range("G1").Value = "ride_length"
        ws.Range("H1").Value = "Weekday"

        'Parse time (start, end & difference)
        ws.Range("E2").FormulaR1C1 = "=TIME(HOUR(RC[-2]),MINUTE(RC[-2]),SECOND(RC[-2]))"
        ws.Range("F2").FormulaR1C1 = "=TIME(HOUR(RC[-2]),MINUTE(RC[-2]),SECOND(RC[-2]))"
        ws.Range("G2").FormulaR1C1 = "=(RC[-1]-RC[-2])*1440"
        ws.Range("H2").FormulaR1C1 = "=WEEKDAY(RC[-5],1)" 'Parse day of week
        
        'Apply the data functions to the cells below headers - data boundary
        ws.Range("E2").AutoFill Destination:=Range("E2:E" & lastrow)
        ws.Range("F2").AutoFill Destination:=Range("F2:F" & lastrow)
        ws.Range("G2").AutoFill Destination:=Range("G2:G" & lastrow)
        ws.Range("H2").AutoFill Destination:=Range("H2:H" & lastrow)
    
        'Data normalization for when ride end time goes past midnight
        For Each i In ws.Range("G2:G" & lastrow)
            If i.Value < 0 Then
                i.Value = i.Value + 1440
            End If
        Next i
    
        'Typecast the parsed "weekday" variable to a int value (1 - 7)
        ws.Range("G2:H" & lastrow).NumberFormat = "General"
            
      wb.Close SaveChanges:=True
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Datasets altered successfully!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.CutCopyMode = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.StatusBar = True
    Application.Calculation = xlCalculationAutomatic

End Sub