Need to Extract Specific Data out of a .txt file

[Zmorris]

Hello,

I am looking to track specific information in regards to a text file. I really only need one piece of information from all the text that is generated in my file. I am specifically looking to put the value after every "s/n=" in an excel spread sheet. I want this text to start in A2 and go on from there. Any help would be greatly appreciated. I have placed a sample text file below. Thanks.

 

[Hui]

Zmorris

Firstly, Welcome to the Chandoo.org Forums

Please try the following code
Copy and paste it into a Code Module in VBA
Change the path/filename to suit

Sub Sample()
Dim MyData As String
Dim strData() As String
Dim i As Long

Dim Ret

Ret = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If Ret = "" Then Exit Sub

Open Ret For Binary As #1
  MyData = Space$(LOF(1))
  Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)

Line = 2
For i = LBound(strData, 1) To UBound(strData, 1)
If InStr(1, strData(i), "s/n=") Then
  Cells(Line, 1) = Right(strData(i), Len(strData(i)) - InStr(1, strData(i), "="))
  Line = Line + 1
End If

Next i
Columns("A:A").EntireColumn.AutoFit

End Sub

I get:


See attached file:

 

[Muhammad Affan]

Zmorris

Firstly, Welcome to the Chandoo.org Forums

Please try the following code
Copy and paste it into a Code Module in VBA
Change the path/filename to suit

Sub Sample()
Dim MyData As String
Dim strData() As String
Dim i As Long

Dim Ret

Ret = Application.GetOpenFilename("Text Files (*.txt), *.txt")

If Ret = "" Then Exit Sub

Open Ret For Binary As #1
  MyData = Space$(LOF(1))
  Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)

Line = 2
For i = LBound(strData, 1) To UBound(strData, 1)
If InStr(1, strData(i), "s/n=") Then
  Cells(Line, 1) = Right(strData(i), Len(strData(i)) - InStr(1, strData(i), "="))
  Line = Line + 1
End If

Next i
Columns("A:A").EntireColumn.AutoFit

End Sub

I get:
View attachment 34485

See attached file:

Dear hui please help me too as i have a similar problem actually i want to extract data from MT103 Swift Message which is exported from Swift.
i need the clauses as headers and the data is under the clause in row as a tabular form.

 

[vletm]

Muhammad Affan
As You have opened a new thread:

Extract Specific Text from Text Output File from SWIFT

Hello, Can anyone help me out to find the way to extract data from Swift output message MT103, i am attaching the source file and want to make a spreadsheet with the info existing in the source file.

chandoo.org

This thread is closed.