Search and copy data from text to Excel

[AjinkyaG]

Hi team,
I have one text file which contains data about list of errors.
I want to search only for ERROR word which have occurred multiple times in file and copy data quoted in '......' which is following the word ERROR=
Format is: ERROR = "short name of error.(number of words may vary,but the text following error is quoted in '...')" .
I tried this to open the file:-

Sub FileOpenDialogBox()
Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Excel Files", " *.txt", 1
.Show
fullpath = .SelectedItems.Item(1)
End With
End Sub

And to search and copy data:-

Sub import()
Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
myFile = "fullpath"
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
posLat = InStr(text, "ERROR")

Range("A1").Value = Mid(text, posLat + 10, 5)

End Sub

with this script i am not able to copy each error from that text file and also data between '......' is not captured.
Can you please help me with it.

 

[Yongle]

Welcome to the forum

1 Please paste 10 lines from a typical sample text file into your reply
2 Also tell us the expected output

Ensure that the sample data includes line items that should be returned and those which should be excluded

thanks

 

[AjinkyaG]

• 
  $ information:
  $ Role : component
  $ Role : battery_box
  
  
  ERROR = 'Some data'
  $----------------------------------------------------------------------LIST
  • 
    $ information:
    $ Role : component
    $ Role : front
    
    
    ERROR = 'Some data'
    
    $----------------------------------------------------------------------LIST
    • 
      
      ERROR = 'Some data'
      $----------------------------------------------------------------------LIST
      • 
        
        ERROR = 'Some data'
        
        
        
        expected output in Excel in A1,B1,C1 and so on
        Some data
        Some data
        Some data
        Some data
        
        Thanks.

 

[Yongle]

Try this

Sub AjinkyaG()
Const FindText = "ERROR = "
Dim fData As String, fPath As String
fPath = GetPath
If fPath = "" Then GoTo TheEnd
    
Open fPath For Input As #1
    Do Until EOF(1)
        Line Input #1, fData
        fData = Trim(fData)
        If Left(fData, 8) = FindText Then Range("A" & Rows.Count).End(xlUp).Offset(1) = Replace(Replace(fData, FindText, ""), "'", "")
    Loop
Close #1
Exit Sub
TheEnd:
MsgBox "file not selected", , ""
End Sub

Private Function GetPath() As String
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Text", "*.txt"
    .Show
    If .SelectedItems.Count = 1 Then GetPath = .SelectedItems.Item(1)
End With
End Function

 

[AjinkyaG]

Thanks for the script but i am not able to see any output in sheet1 of my excel. There is no error after running the script.

 

[Yongle]

It worked for me, so there must be something different in your text file compared with what I pasted in from post#3
It is possible that the text file contains different space characters or a different hyphen character or extra spaces etc

Replace

        If Left(fData, 8) = FindText Then Range("A" & Rows.Count).End(xlUp).Offset(1) = Replace(Replace(fData, FindText, ""), "'", "")

with

        Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 2) = Array(fData, Left(fData, 8))

I expected the first 8 characters (in the releveant cells in column A) to be ERROR followed by SPACE followed by HYPHEN followed by SPACE
Which 8 characters are you seeing in column B for those lines?

 

[AjinkyaG]

Extremely sorry..my mistake!!,It worked flawlessly!! Actually it had one more space between ERROR and = . Can we paste the data where ever we want i.e- start from A3 till end or B3..

 

[Yongle]

Can we paste the data where ever we want i.e- start from A3 till end or B3

Amend Set cel = Range("A3") to desired start cell

Sub AjinkyaG()
Const FindText = "ERROR  = "                 'contains additional space before =
Dim fData As String, fPath As String, cel As Range
fPath = GetPath
If fPath = "" Then GoTo TheEnd
Set cel = Range("A3")
Open fPath For Input As #1
    Do Until EOF(1)
        Line Input #1, fData
        fData = Trim(fData)
        If Left(fData, 9) = FindText Then
            cel = Replace(Replace(fData, FindText, ""), "'", "")
            Set cel = cel.Offset(1)
        End If
    Loop
Close #1
Exit Sub
TheEnd:
MsgBox "file not selected", , ""
End Sub

Private Function GetPath() As String
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Add "Text", "*.txt"
    .Show
    If .SelectedItems.Count = 1 Then GetPath = .SelectedItems.Item(1)
End With
End Function

To allow user to select first cell at run time ...replace Set cel = Range("A3") with

Set cel = Application.InputBox("First cell?", "Select a Cell", "A3", , , , , 8)(1)

 

[AjinkyaG]

thanks a lot!! it worked!!!

 

[AjinkyaG]

Can you please help me with one more thing? it is extension to above thing:
Actually I wanted to search column of sheet in which some text will be there. If that text matches with the ERROR statement which we are taking as output then it will be pasted in front of that respective found text. if not found then at the end.
For more clarification I am uploading file:
INPUT:-TEXT FILE

#----------------------------------------------------------------------LIST

• 
  ERROR = 'DEAL WITH ONE'
  #----------------------------------------------------------------------LIST
  • 
    ERROR = 'DEAL WITH TWO'
    #----------------------------------------------------------------------LIST
    • 
      ERROR = 'DEAL WITH THREE'
      #----------------------------------------------------------------------LIST
      • 
        ERROR = 'DEAL WITH FOUR'
        #----------------------------------------------------------------------LIST
        • 
          ERROR = 'DEAL WITH FIVE'
          #----------------------------------------------------------------------LIST
          • 
            ERROR = 'DEAL WITH SIX'
            #----------------------------------------------------------------------LIST
            • 
              ERROR = 'DEAL WITH SEVEN'
              #----------------------------------------------------------------------LIST
              • 
                ERROR = 'DEAL WITH EIGHT'
                
                #----------------------------------------------------------------SETTINGS