Inserting data from text file in respective excel columns

Question

Many Many thanks in advance I have a .txt file that contains data that needs to be extracted and placed into respective columns in Excel. I am very much new to VBA coding and tried a lot, but having difficulty in making this work... below shows the code I have thus far but when run, it is working differently. Actually Data needs to be placed in their respective field as sample in the excel. In the Excel file I have already kept the data as how it has to be fetched and filled in the respective headed column.

TYPE;ACCOUNT NUMBER:BANK REFERENCE;BENEFICIARY NAME ;DATE ;AMOUNT ;BENE ACCOUNT NUMBER ;BENE IFSC ;BENE BANK NAME ;REFERENCE ;BENE MAIL ID IMPS;45605104698 ;60062000057200 ;ABCDEF ;12122016;0000000001.00;10304060176 ;STRK0002018;STATE BANK OF INDIA ;5110845 ;abce@gmail.com ;

The code which I am using for extracting this above data and to putting in their respective columns is as below:-

Option Explicit

Sub importTXT()
Dim r As Range, myfile As Variant
Dim qt As QueryTable, i As Integer
Dim del As Range

'where myfile needs to select manually
myfile = Application.GetOpenFilename("All Files (*.*), **.*", _
, "Select TXT file", , False)
If myfile = False Then Exit Sub

'elseif its fixed
'myfile = "D:\sample student file"

Application.ScreenUpdating = False

With ActiveSheet
.Range("E7").CurrentRegion.Cells.Clear
With .QueryTables.Add(Connection:="TEXT;" & myfile, Destination:=.Range("$E$7"))
        .Name = "MST"
        .TextFilePlatform = 437
        .TextFileStartRow = 2
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileTabDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
'delete query tables if found any.
    For Each qt In ActiveSheet.QueryTables
        qt.Delete
    Next qt
'Delete the Data Connections
If .Parent.Connections.Count > 0 Then
    For i = 1 To .Parent.Connections.Count
        .Parent.Connections.Item(i).Delete
    Next i
End If

For Each r In .Range("E7:X" & .UsedRange.Rows.Count)
    If InStr(r, "Title = ") > 0 Then
        r.Offset(, 1) = Mid(r.Value, InStr(r, " ") + 8, InStrRev(r.Value, " "))
        r.Offset(, 2) = Mid(r.Value, InStrRev(r.Value, " ") + 2, Len(r.Value) - InStrRev(r.Value, " ") - 2)
    Else
        If del Is Nothing Then
            Set del = r
        Else
            Set del = Union(del, r)
        End If
    End If
Next
End With
Application.ScreenUpdating = False
End Sub

The sample excel file where data needs to be inserted is as below:-

Answer 1

I used a different approach but I think this will do what you need:

1. import CSV

2. store it in array

3. setup array with new col nrs based on mapping array

4. paste to sheet

    Sub ImportCsv()
        'load the source file based on user input to an array
        Dim filename As String, Data
        filename = Application.GetOpenFilename
        Data = openfile(filename)
   
        'spitting first line to get nr of columns
        Dim cls, Data2, j As Long, i As Long, newcls
        cls = Split(Data(1, 1), ";")
   
        'Re-Order columns - You can just change to nr according to your mapping => first column mapped to col 5 etc...
        newcls = Array(5, 3, 10, 14, 8, 6, 13, 19, 18, 9, 22)
   
        'Setup reformated array, make sure the Ubound of columns corresponds to the max col in your mapping
        ReDim Data2(1 To UBound(Data), 1 To 22)
        For j = 1 To UBound(Data, 1)
            cls = Split(Data(j, 1), ";")
            For i = 1 To UBound(cls)
                Data2(j, newcls(i)) = Trim(cls(i - 1))
            Next i
        Next j
   
        'paste to sheet
        Worksheets("Sheet1").Range("A1").Resize(UBound(Data2), UBound(Data2, 2)).Value2 = Data2
    End Sub
   
    Private Function openfile(filename As String) As Variant
        'import External
        Dim wbExt As Workbook, Data, FilePath As String
        'FilePath = Application.ActiveWorkbook.Path & filename => alternative if you just ask a filename to the user. this will set the path.
        Set wbExt = Workbooks.Open(filename:=filename) 'replace filename with filepath if you choose above approach
        With wbExt: Data = .Sheets(1).UsedRange.Value: .Close: End With 'get data from source and close
        openfile = Data 'send array back to main sub
    End Function
   

best of luck,