Import last row of each CSV file within a folder

[la333]

The closest post I've found that does similar to what I'm needing: Import last row of csv to excel answered by @DanteAmor

@DanteAmor I'm looking to do something similar but I have about 650 CSV files that I need to do the following for...
For each CSV file, I need to import the file name as Column A and then import the last row of 5 columns.

They are all in the same directory.

For further e.g. -

I have files named

AA1234.CSV
AA1235.CSV
AA1236.CSV
AA1237.CSV
AA1238.CSV
etc

and then each of the files appear as such -

bc456 1/6/2022 10:07 AM 192.168.1.5 Y
la333 1/7/2022 9:07 AM 192.168.1.5 Y
mc257 1/8/2022 7:07 AM 192.168.1.5 N
la333 1/9/2022 8:07 AM 192.168.1.5 Y
bc456 1/10/2022 9:07 AM 192.168.1.5 Y

I would like for my new file to add the name of the excel file to the first column and then pull the last row from each of the files.

AA1234 bc456 1/10/2022 9:07 AM 192.168.1.5 Y

Thanks for all of your help in advance!

 

[DanteAmor]

Set in the macro the name of the path where you have the csv files. And the name of the sheet "Sheet1" where the imported rows will be, in these lines:
Set sh = Sheets("Sheet1")
sPath = "C:\trabajo\entrada\"

Sub Import_last_row()
  Dim wb As Workbook
  Dim sh As Worksheet
  Dim sPath As String, sFile As Variant
  Dim lr As Long, i As Long
  
  Application.ScreenUpdating = False
  
  Set sh = Sheets("Sheet1")
  sPath = "C:\trabajo\entrada\"
  sFile = Dir(sPath & "*.csv")
  i = 2
  Do While sFile <> ""
    Set wb = Workbooks.Open(Filename:=sPath & sFile, ReadOnly:=True, local:=True)
    lr = wb.Sheets(1).Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    sh.Range("A" & i).Resize(1, 5).Value = wb.Sheets(1).Range("A" & lr).Resize(1, 5).Value
    i = i + 1
    wb.Close False
    sFile = Dir()
  Loop
End Sub

 

[la333]

@DanteAmor while this does grab the last line of each CSV file really well, I need for each line to add the filename of where that line came from. Is that possible? Preferably stripping the ".csv" file extension but I can do that manually if that isn't possible.

Thanks!

 

[DanteAmor]

I need for each line to add the filename

I forgot that part.
Try this:

Sub Import_last_row()
  Dim wb As Workbook
  Dim sh As Worksheet
  Dim sPath As String, sFile As Variant
  Dim lr As Long, i As Long
  
  Application.ScreenUpdating = False
  
  Set sh = Sheets("Sheet1")
  sPath = "C:\trabajo\entrada\"
  sFile = Dir(sPath & "*.csv")
  i = 2
  Do While sFile <> ""
    Set wb = Workbooks.Open(Filename:=sPath & sFile, ReadOnly:=True, local:=True)
    lr = wb.Sheets(1).Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    sh.Range("A" & i).Value = Replace(sFile, ".csv", "")
    sh.Range("B" & i).Resize(1, 5).Value = wb.Sheets(1).Range("A" & lr).Resize(1, 5).Value
    i = i + 1
    wb.Close False
    sFile = Dir()
  Loop
End Sub

 

[la333]

That did it! Awesome, that's a big time saver for me... One question though, I didn't notice it initially but the time stamp is being converted to some type of a decimal. It's an easy fix by just selecting the column and switching to Time format but is there something in the script that can be toggled to fix that during the import?

None the less, thank you SO much for this!

 

[DanteAmor]

After this line:
Loop

Add this line:

Columns("C:C").NumberFormat = "[$-F400]h:mm:ss AM/PM"

 

[la333]

After this line:
Loop

Add this line:

Columns("C:C").NumberFormat = "[$-F400]h:mm:ss AM/PM"

That worked

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[la333]

Sorry to bother... When I implemented this into our sheets, I was asked if we could grab the second to last user as well... I've been trying to modify what you've sent but I'm seeing there's some completely other calls needed for that. How can I grab the second to last line rather than the previous line? Thank you.

 

[DanteAmor]

How can I grab the second to last line rather than the previous line?

Change this line:

lr = wb.Sheets(1).Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

For this line:

lr = wb.Sheets(1).Range("A:E").Find("*", , xlValues, , xlByRows, xlPrevious).Row - 1