Open txt file

[jjpski]

Good Morning,

I am completely confused on how I can parse the file sort it by dates but retain only the last 6 months then remove duplicate computer names.

Need a solution to open a txt file that is comma delimited with four fields. I need all duplicate Workstation removed but retain the recent date within the last six months.

example to the text file
Date,Workstation,Username,Domain
"2019-08-10 07:00:38","Computer1","User","Domain"
"2020-02-10 08:47:20","Computer2","User","Domain"
"2020-03-01 09:01:00","Computer3","User","Domain"
"2020-04-01 10:05:58","Computer1","User","Domain"
"2020-05-07 08:15:15","Computer2","User","Domain"

Output:
CELL(1,1) = 2020-05-07
CELL(1,2) = Computer2
CELL(2,1) = 2020-04-01
CELL(2,2) = Computer1
CELL(3,1) = 2020-03-01
CELL(3,2) = Computer3

Thanks in advance.

 

[DanteAmor]

"2019-08-10 07:00:38","Computer1","User","Domain"
"2020-02-10 08:47:20","Computer2","User","Domain"
"2020-03-01 09:01:00","Computer3","User","Domain"
"2020-04-01 10:05:58","Computer1","User","Domain"
"2020-05-07 08:15:15","Computer2","User","Domain"

The following macro considers the structure of the data in the txt file as you put it in your example.

Change in the macro "C:\trabajo\pcs.txt" for the folder and name of your file.

Sub Open_txt()
  Dim sPathIn As String, sComp As String, d As String
  Dim dic As Object, sLine As Variant, sItems As Variant, sDate As Date
  '
  sPathIn = "C:\trabajo\pcs.txt"
  Set dic = CreateObject("Scripting.Dictionary")
  Open sPathIn For Input As #1
  Do While Not EOF(1)
    Line Input #1, sLine
    sLine = Replace(sLine, """", "")
    sItems = Split(sLine, ",")
    d = Split(sItems(0), " ")(0)
    sDate = CDate(Right(d, 2) & "/" & Mid(d, 6, 2) & "/" & Left(d, 4)) + TimeValue(Split(sItems(0), " ")(1))
    sComp = sItems(1)
    If dic.exists(sComp) Then If sDate > dic(sComp) Then dic(sComp) = sDate
    dic(sComp) = sDate
  Loop
  Close #1
  '
  Range("A2").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.Items))
End Sub

Output:

varios 18dic2019 read_txt_files.xlsm
	A	B
1
2	Computer1	2020-04-01 10:05:58 a.m.
3	Computer2	2020-05-07 8:15:15 a.m.
4	Computer3	2020-03-01 9:01:00 a.m.
Workstation

If you want another date view, just adjust the format of column B.

 

[DanteAmor]

Edit:
Consider this macro

Sub Open_txt()
  Dim sPathIn As String, sComp As String, d As String
  Dim dic As Object, sLine As Variant, sItems As Variant, sDate As Date
  '
  sPathIn = "C:\trabajo\pcs.txt"
  Set dic = CreateObject("Scripting.Dictionary")
  Open sPathIn For Input As #1
  Do While Not EOF(1)
    Line Input #1, sLine
    sLine = Replace(sLine, """", "")
    sItems = Split(sLine, ",")
    d = Split(sItems(0), " ")(0)
    sDate = CDate(Right(d, 2) & "/" & Mid(d, 6, 2) & "/" & Left(d, 4)) + TimeValue(Split(sItems(0), " ")(1))
    sComp = sItems(1)
    If dic.exists(sComp) Then
      If sDate > dic(sComp) Then dic(sComp) = sDate
    Else
      dic(sComp) = sDate
    End If
  Loop
  Close #1
  '
  Range("A2").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.Items))
End Sub

 

[jjpski]

This is awesome. A few minor changes to fit my scenario.
Thank you

 

[DanteAmor]

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