# Extract specific data from text file

#### mahesh402

##### New Member
I have this text file which shows data like
GL CODE. GL DISCRIPTION. DEBIT. CREDIT
14355 Mt two wheele. 67895000. 9876555
15367 MT CON LOAN. 8769762 7655444
14567. MT NON CON. 4356278. 4526727

but the problem is there is too much unwanted data prior to this and after this. I tried text import wizard but there are too many files that makes it time consuming...I also tried the code suggested in similar post and even tried to modify it but no succese. I want code discription debit and credit in different column...GL CODE is of five digits and then there is text again there are figures in debit and credit
And there is also one thing there are total of 4 column sometimes I require Code debit and credit and sometimes all four so code must be flexible

#### Marc L

##### Excel Ninja
Restart from here :​

#### Pavan Kumar Sistla

##### Member
Dear Mahesh,

Not sure if Flash Fill feature (Ctrl+E) helps your requirement. Please try.

Thanks,
Pavan.

#### mahesh402

##### New Member
Thanks for your reply but data is to be imported from text file into excel.

#### Pavan Kumar Sistla

##### Member
Please post your query in Excel section. Currently it is under VBA Macros and also try to attach txt file which give more clarity please.

#### mahesh402

##### New Member
Restart from here :​
Thanks for reply sir.... I have read all the things in the post and I am sorry that I could not represent myself better. But what I have to do now....repost it..?? Sorry to bother you but please help me out here

#### mahesh402

##### New Member
Please post your query in Excel section. Currently it is under VBA Macros and also try to attach txt file which give more clarity please.
Thanks for reply. But I need a vba macro code for extracting a particular data from text file. That is why I have posted it here.secondly text file is confidential and company policy do not allow me to share it. But I already have tried to show what data looks like in text file and what kind of output I require. Please look and thanks again

#### Marc L

##### Excel Ninja
But what I have to do now....repost it..?
Once you well read at least the post #2 link in particular the part How to get best results then,​
as after all we are not on a mind readers forum, just well explain here (not in the Excel section) your need​
with all the necessary files attached, both explanation & attachment smart enough …​

#### mahesh402

##### New Member
I am attaching a text file please have look at it..I am trying to fetch only 5 or 7 records from the file with codes 33052, 34232,18113,39001 and 39011.thanks any waz

#### Attachments

• 14.1 KB Views: 4

#### Marc L

##### Excel Ninja
Attach the expected result workbook saved as binary .xlsb with two sheets :​
• a Settings sheet with the list of codes to import (this info should have be given in the initial post ‼)

• a filled result sheet with the exact layout expected result according to the Settings sheet and your previous text file attachment …
As a reminder : you will have to amend the code (just for starters) for all you forgot to elaborate & attach …​

#### mahesh402

##### New Member
Here it is..thanks in advance
Result sheet is expected result

#### Attachments

• 14.1 KB Views: 2
• 12.4 KB Views: 2
• 16.1 KB Views: 2

#### Marc L

##### Excel Ninja
Attach the expected result workbook saved as binary .xlsb with two sheets
Can't the sheets be located within the same workbook as I asked for ?​
If yes, attach the workbook saved as binary .xlsb …​
I give it a try and it's easy with the text import wizard, no issue …​

#### mahesh402

##### New Member
Thanks for reply ....I made the result sheet with text import wizard but the real issue is text file I attached I already have deleted the data prior to this codes and also I only need few of the codes after importing data I have to manually cut the unwanted data and also there are around 718 text files ...that is why I humbly request you to please help me
Thanks in advance and I am sorry about binary.xls I will upload it tomorrow as u asked right now I am little bit away from my pc

#### Marc L

##### Excel Ninja
After the text import wizard it's easy just using other Excel basics via a formula and a filter or an advanced filter​
like an Excel beginner can yet operate manually, you will see tomorrow (after your new upload) …​

#### mahesh402

##### New Member
This the code I am trying to workout with

Code:
Sub Import_File()
Dim strFilename As String
Dim strFileContent As String
Dim iFile As Integer
Dim MyTxtFile() As String

Dim MyFolder As String
Dim myFile As String
Dim cline As Integer
Dim arr() As String

MyFolder = "C:\Users\Administrator\Desktop\MANAGER SAHAB\project\project for extracting specific data from txt to excel\trial2\text" 'Change as appropriate

myFile = "test.txt" 'Select Txt file

ChDir MyFolder

Do While myFile <> ""

Worksheets(myFile).Select

Range("A1").Value = "code"
Range("B1").Value = "loan"
Range("D1").Value = "amount"

iFile = FreeFile

'Open TXT File and load it to an array
Open myFile For Input As #iFile
strFileContent = Input(LOF(iFile), iFile)
Close #iFile

'Split array to another array
MyTxtFile = Split(strFileContent, vbLf)
cline = 2
'Loop through each line of the TXT File
For i = 1 To UBound(MyTxtFile, 1)
Debug.Print i, MyTxtFile(i)

If InStr(1, MyTxtFile(i), "18113") Then
ActiveSheet.Cells(cline, 1) = Left(MyTxtFile(i), Len(MyTxtFile(i)) - 0)
arr = Split(MyTxtFile(i))

ActiveSheet.Cells(cline, 2) = arr(UBound(arr, 1) - 9)
ActiveSheet.Cells(cline, 3) = arr(UBound(arr, 1) - 1)
ActiveSheet.Cells(cline, 4) = arr(UBound(arr, 1) - 0)

cline = cline + 1
End If
Next i
Loop

Columns("A:C").EntireColumn.AutoFit

End Sub

But the problem is I am only able to get only last column.
Please look into it. Thanks in advance

#### Attachments

• 14.1 KB Views: 3
• 16.9 KB Views: 7
Last edited by a moderator:

#### Marc L

##### Excel Ninja
I can't open your workbook : how did you exactly create it ?​

#### mahesh402

##### New Member
Sorry for replying late...I again created the binary.xlsb.

#### Attachments

• 14.1 KB Views: 4
• 17.3 KB Views: 8

#### rahulshewale1

##### Active Member
Hi @mahesh402 ,

Could you provide desire output with sample data?

What is "Setting Sheet" ?

Regard
Rahul Shewale

#### mahesh402

##### New Member
Here the desired output (result.xlsx) which I require from test.txt fil.e. I only need 5 or 7 of records with specific codes in first column

#### Attachments

• 16.1 KB Views: 9
• 14.1 KB Views: 1

#### Marc L

##### Excel Ninja
'Binary' just stated for the file format to use with the Save As Excel command …​
Anyway, according to your post #17 attachment and with the help of a beginner activating the Macro Recorder​
and just operating Excel basics (text import wizard, a formula and an advanced filter),​
try this demonstration you must first paste to the Sheet2 (result) worksheet module :​
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 17/02/2020 par  The Very Noob Simulator !
'

'
Dim V
V = Application.GetOpenFilename("Text Files (*.txt), *.txt"):  If V = False Then Exit Sub
Me.UsedRange.Clear
With Me.QueryTables.Add("TEXT;" & V, [A1])
.RefreshStyle = xlOverwriteCells
.TextFileDecimalSeparator = "."
.TextFileFixedColumnWidths = [{9,53,21}]
.TextFileParseType = xlFixedWidth
.TextFileThousandsSeparator = ","
.Refresh False:  .Delete
End With
With Me.UsedRange
V = Application.Match("GL CODE", .Columns(1), 0):  If IsError(V) Then Beep: Exit Sub
If V > 1 Then Rows("1:" & V).Delete
[K2].Formula = "=ISNA(MATCH(A2," & Sheet1.UsedRange.Address(External:=True) & ",0))"
If Me.FilterMode Then .Offset(1).EntireRow.Delete: Me.ShowAllData
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​

#### mahesh402

##### New Member
Thanks for your help. But it shows compile error : Invalid use of Me keyword

#### Marc L

##### Excel Ninja
Your bad ! Read again and apply at least the dark red direction ! Or replace Me statement by your worksheet reference …​

#### mahesh402

##### New Member
Thanks for your help I did as per your directions..but the output still has unwanted data like the dotted line ,sum income head,liability in between the sheet and other unwanted data..is there any way that I could fetch the data of only prespecified GL codes.. Thanks again for the code

#### Marc L

##### Excel Ninja
No issue on my side !​
So as I wrote « according to your post #17 attachment » so according to its 'Setting' worksheet​
I can't guess what you are doing bad, just well read again the post #20 …​

#### mahesh402

##### New Member
Thankyou very much ....took me little bit time to understand but it does the job perfectly according to my requirement.
Thanks once again.
Just a last thing how to modify it so that it can work on any active sheet.I mean you already told me in post 22 but I am new to excel vba coding.so could you please tell me what I have to repalce with me statement to make it work in active sheet

Last edited: