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:-
The data after extraction it should be filled in their respective column as below:-
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:-
It would be highly Appreciate any help with this... many many thanks in advance. For better understanding I have attached the all files.
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:-
Code:
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 SubThe 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:-
Code:
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 SubIt would be highly Appreciate any help with this... many many thanks in advance. For better understanding I have attached the all files.
Last edited:
