Import text file into excel

GreatEagle · Apr 27th 2016

Hi all,

can you please help me to import the text file as the below? Thank you so much

StudentAlternateId : 17829903
* Warning : The Previous Institution External Organisation 'OTHER' has a status of 'Terminated'.
* Warning : The Previous Institution External Organisation 'OTHER' has a status of 'Terminated'.
* Warning : The Previous Institution External Organisation 'OTHER' has a status of 'Terminated'.
* Warning : Citizenship data will be updated.
* Error : The selected Liability Category is not valid for this student's citizenship.
Status - Error

Student Alternate Id : 18002867
* Warning : The Previous Institution External Organisation 'OTHER' has a status of 'Terminated'.
* Warning : The Previous Institution External Organisation 'OTHER' has a status of 'Terminated'.
* Warning : Citizenship data will be updated.
* Error : The selected Liability Category is not valid for this student's citizenship.
Status - Error

To display in Excel as:

[TABLE="width: 524"]

[tr]

[td]

SID

[/td]

[td]

Error

[/td]

[/tr]

[tr]

[TD="align: right"]17829903[/TD]

[td]

The selected Liability Category is not valid for this student's citizenship.

[/td]

[/tr]

[tr]

[TD="align: right"]18002867[/TD]

[td]

The selected Liability Category is not valid for this student's citizenship.

[/td]

[/tr]

[/TABLE]

**pike** · Apr 27th 2016

Re: Import text file into excel

Hello GreatEagle,
welcome to the forum
use

Code

Option Explicit
Sub ptest()
    Dim strFile As String, rLine As String, strStudentId As String, strComment As String
    Dim i As Integer, iTargetRow As Integer


    strFile = ThisWorkbook.Path & "\test.txt" ' change to suit
    i = 0
    iTargetRow = 2


    Cells(2, 1).Resize(, 2) = VBA.Array("SID", "Error")


    Open strFile For Input As #1
    Do While Not EOF(1)
              Line Input #1, rLine
        If rLine Like "*Student*:*" And strComment = vbNullString Then
            strStudentId = Mid(rLine, InStr(rLine, ":") + 1, Len(rLine))
        End If
        If rLine Like "*Error :*" And strStudentId <> vbNullString Then
            iTargetRow = iTargetRow + 1
            strComment = Mid(rLine, InStr(rLine, ":") + 1, Len(rLine))
            Cells(iTargetRow, 1).Resize(, 2) = VBA.Array(strStudentId, strComment)
         i = i + 1
        strStudentId = vbNullString
        strComment = vbNullString
        End If


    Loop
    MsgBox i & " lines were read."
    Close #1
    Exit Sub


End Sub

Display More

GreatEagle · Apr 29th 2016

Re: Import text file into excel

Thank you Pike! It works perfectly

Import text file into excel

Participate now!

Share