• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract specific data from text file

mahesh402

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
 
Restart from here :​
 
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
 
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
 
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 …​
 
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

  • test.txt
    14.1 KB · Views: 4
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 …​
 
Here it is..thanks in advance
Result sheet is expected result
 

Attachments

  • test.txt
    14.1 KB · Views: 2
  • setting.xlsx
    12.4 KB · Views: 3
  • result.xlsx
    16.1 KB · Views: 3
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 …​
 
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
 
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) …​
 
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 <> ""
  'Add new worksheet
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = 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

  • test.txt
    14.1 KB · Views: 4
  • binary.xlsb
    16.9 KB · Views: 8
Last edited by a moderator:
Sorry for replying late...I again created the binary.xlsb.
 

Attachments

  • test.txt
    14.1 KB · Views: 4
  • binary.xlsb
    17.3 KB · Views: 9
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

  • result.xlsx
    16.1 KB · Views: 11
  • test.txt
    14.1 KB · Views: 2
'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])
           .AdjustColumnWidth = True
           .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))"
        .AdvancedFilter xlFilterInPlace, [K1:K2]
         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 !​
 
Your bad ! Read again and apply at least the dark red direction ! Or replace Me statement by your worksheet reference …​
 
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
 
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 …​
 
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:
Back
Top