Extracting data from Text file and then converting it to text file in different format

[Rahulkr]

Dear Friends,

I have a .txt file that contains specific data that needs to be extracted and placed into respective columns in Excel. I am very much new to VBA coding and tried, but having difficulty in making this work... below shows the code I have thus far but when run, it is working differently. I want those data to be included 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 and then it has to be converted in the different format, which is in the attachment. The following is sample data from where the data needs to be extracted:-

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 for extracting this above data 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:\windowsupdate1.txt"

Application.ScreenUpdating = False

With ActiveSheet
.Range("A7").CurrentRegion.Cells.Clear
With .QueryTables.Add(Connection:="TEXT;" & myfile, Destination:=.Range("$A$7"))
        .Name = "windowsupdate1"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileTabDelimiter = True
        .TextFileColumnDataTypes = Array(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("F1:F" & .UsedRange.Rows.Count)
    If InStr(r, "Title = ") > 0 Then
        r.Offset(, 1) = Mid(r.Value, InStr(r, "Title = ") + 8, InStrRev(r.Value, " (KB"))
        r.Offset(, 2) = Mid(r.Value, InStrRev(r.Value, " (KB") + 2, Len(r.Value) - InStrRev(r.Value, " (KB") - 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 data after extraction it should be filled in their respective column as below:-

SCHOOL CODE

SCHOOL NAME

STUDENT ACCOUNT NO.

Debit Account Currency

Payment Method

Remittance Amount

Remittance Currency

Value Date

Customer Ref No.

Reference Authority

Payment Details

Contract Number1

Beneficiary Account No / Transfer Account No

Beneficiary's Name 1

Beneficiary's Name 2

Beneficiary Address 1

Beneficiary Address 2

Beneficiary Bank Name 1

Bene Bank Code

Bank Charges

Charge Account

E-mail address

Debit Type

Invoice Details

1452889

XYZ

45605104698

USD

IMPS

0000000001.00

USD

12122016

5110845

60062000057200

45869974582

ABCDEF

STRK0002018

abce@gmail.com

But it is not coming like as what I want.

After that the data needs to be again converted in the text file as below:-
“1452889”;“XYZ”;“45605104698”;“USD”;“IMPS”;“0000000001.00”;“USD”;“12122016”;“5110845”;“60062000057200”;“”;“”;“45869974582”;“ABCDEF”;“”;“”;“”;“”;“STRK0002018”;“”;“”;“abce@gmail.com”;“”;“”

And for doing so I am using the below code, but still not able to achieve the goal:-

Sub CopyDataToTextFile()
Dim X As Long, FF As Long, LastCopyRow As Long, DataToOutput As String
'First row of data to be extracted
Const StartRow As Long = 1
On Error Resume Next
LastCopyRow = Columns("A").SpecialCells(xlBlanks)(1).Row - 1
If Err.Number Then LastCopyRow = Cells(Rows.Count, "X").End(xlUp).Row
On Error GoTo 0
For X = StartRow To LastCopyRow
DataToOutput = DataToOutput & vbNewLine & Application.Trim(Join(Application.Index(Cells(X, "A").Resize(, 24).Value, 1, 0), vbTab))
Next
FF = FreeFile
Open "D:\ " & Range("A2").Value & ".txt" For Output As #FF
Print #FF, DataToOutput
Close #FF


End Sub

It would be highly Appreciate any help with this... many many thanks in advance. For better understanding I have attached the all files.

 

[pjmorris]

my suggestion for what its worth:

1. ditch VBA - its not what its good at, is a 'p.g' to maintain, and simply too inflexible.
2. On the 'Get & Transform' tab of the Data ribbon select 'New Query', 'From File', 'From Text',
3. Navigate to your source file.
4. Click on Import.
5. Click on transform Data (it may well have already tried to organise the data into columns).
6. Use the PowerQuery editor to transform columns or add columns as necessary.
7. Click on 'Close & Load' to add the data to a new sheet in your workbook.

HTH.

 

[Rahulkr]

my suggestion for what its worth:

1. ditch VBA - its not what its good at, is a 'p.g' to maintain, and simply too inflexible.
2. On the 'Get & Transform' tab of the Data ribbon select 'New Query', 'From File', 'From Text',
3. Navigate to your source file.
4. Click on Import.
5. Click on transform Data (it may well have already tried to organise the data into columns).
6. Use the PowerQuery editor to transform columns or add columns as necessary.
7. Click on 'Close & Load' to add the data to a new sheet in your workbook.

HTH.

Morris,
Thanks for the response, but Morris I want it to be done through VBA code cause after sometime, may be other can not able to use this. So, it is required.

 

[pjmorris]

Morris,
Thanks for the response, but Morris I want it to be done through VBA code cause after sometime, may be other can not able to use this. So, it is required.

Powerquery is a relatively new addition to Excel and is unlikely to ever disappear (I think VBA would go first!). It allows development of solutions for PowerBi as well. It can be a challenge to learn a new environment, but I haven't looked back since I adopted PowerQuery. For certain, VBA has its place, but its not for importing data.

Hope you're successful whichever route you take.

All the best

 

[Rahulkr]

Below is the text file data and one by one data which is separated by semicolon needs to be inserted in the respective assigned cell no.

IMPS;45605104698 ;60062000057200 ;ABCDEF ;12122016;0000000001.00;10304060176 ;STRK0002018;STATE BANK OF INDIA ;5110845 ;abce@gmail.com ;



From text file, the first data IMPS in cell E7, second data 45605104698 in cell C7, third data 60062000057200 in cell J7, similarly ABCDEF in N7, 12122016 in H7, 0000000001.00 in F7, 10304060176 in M7, STRK0002018 in S7, STATE BANK OF INDIA in R7, 5110845 in I7, and abce@gmail.com in V7.



This is what I required to be inserted from text file into the excel cells.