Search and copy data from text to Excel

AjinkyaG

New Member
Joined
Jun 20, 2019
Messages
15
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 20, 2019
Messages
15

  • $ 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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Try this


VBA Code:
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

New Member
Joined
Jun 20, 2019
Messages
15

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
        If Left(fData, 8) = FindText Then Range("A" & Rows.Count).End(xlUp).Offset(1) = Replace(Replace(fData, FindText, ""), "'", "")
with
VBA Code:
        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

New Member
Joined
Jun 20, 2019
Messages
15

ADVERTISEMENT

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

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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
VBA Code:
Set cel = Application.InputBox("First cell?", "Select a Cell", "A3", , , , , 8)(1)
 

AjinkyaG

New Member
Joined
Jun 20, 2019
Messages
15
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
 

Attachments

  • Untitled.png
    Untitled.png
    120.2 KB · Views: 10
Last edited:

Forum statistics

Threads
1,136,302
Messages
5,674,965
Members
419,537
Latest member
ucatchy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top