Search input value in multiple text files and extract the match rows in excel

[DevC]

Hi,

I have 30 .txt files delimited by "|". One column in the header of each text file is "SOL_ID". This column is not fixed and changes its position. I want to search for a particular input value in this column "SOL_ID" and if found, it will extract the entire row to an excel sheet. Separate worksheet for separate text files. Can it be done. Please help. I am pasting a sample of the text file;

S_NO|BINARY_ID|KPI_ID|BUSINESS_STREAM|REPORT_DATE|EXECUTION_DATE|PROC_DATE|SOL_ID|PPO_NUM|
CO_307| |CO_307|CO FUNCTIONS|APR-2020|04-MAY-20|30-APR-20|296|684471606048|
CO_307| |CO_307|CO FUNCTIONS|APR-2020|04-MAY-20|30-APR-20|3330|684471606019|
CO_307| |CO_307|CO FUNCTIONS|APR-2020|04-MAY-20|30-APR-20|3330|684471605737|
CO_307| |CO_307|CO FUNCTIONS|APR-2020|04-MAY-20|30-APR-20|707|684471606134|
CO_307| |CO_307|CO FUNCTIONS|APR-2020|04-MAY-20|30-APR-20|157|713831606072|
CO_307| |CO_307|CO FUNCTIONS|APR-2020|04-MAY-20|30-APR-20|007|CMISC0130222016|

 

[DanteAmor]

Try this.
In this line put the folder where you have your txt files.
sPath = "C:\trabajo\folder2\"

Sub Search_value_in_text_files()
  Dim sInput As Variant, sFile As Variant, sData As Variant
  Dim sPath As String, sLine As String, sName As String
  Dim col As Long, i As Long, bCreate As Boolean
  
  sPath = "C:\trabajo\folder2\"
  sFile = Dir(sPath & "*.txt")
  sInput = Application.InputBox("input value :")
  If sInput = "" Or sInput = False Then Exit Sub
  
  Do While sFile <> ""
    Open sPath & sFile For Input As #1
    Line Input #1, sLine
    sData = Split(sLine, "|")
    col = -1
    
    For i = 0 To UBound(sData)
      If sData(i) = "SOL_ID" Then
        col = i
        Exit For
      End If
    Next
    
    If col > -1 Then
      bCreate = False
      Do While Not EOF(1)
        Line Input #1, sLine
        If Split(sLine, "|")(col) = sInput Then
          If bCreate = False Then
            sName = Left(sFile, Len(sFile) - 4)
            Sheets.Add(, Sheets(Sheets.Count)).Name = sName
            Sheets(sName).Range("A1").Resize(1, UBound(sData) + 1).Value = sData
            bCreate = True
          End If
          Sheets(sName).Range("A" & Rows.Count).End(3)(2).Resize(1, UBound(sData) + 1).Value = Split(sLine, "|")
        End If
      Loop
    End If
    
    Close #1
    sFile = Dir()
  Loop
End Sub