Getting Data from text file and to represent the same in Worksheet in particular structure. code written but somehow stuck

[SamDsouza]

Hello a bit of help required to help me get data from text file to Worksheet

My Text.Txt File in notepad is read as
19-09-2020
Reference Number NZ26X201249Y75140
Amt 2000.00

28-09-2020
Reference Number MD00X1485Y47366
Amt 8600.00

10-10-2020
Reference Number YN27220X1562M9109
Amt 7650.00

Same to be read as in Worksheet as

	A	B	C
1	Date	Reference Number	Amt
2	19-09-2020	NZ26X201249Y75140	2000.00
3	28-09-2020	MD00X1485Y47366	8600.00
4	10-10-2020	YN27220X1562M9109	7650.00

I tried below code but somehow got stuck to represent in Worksheet format

Private Sub CommandButton1_Click()
Call txtDataToWorkSheetRange
End Sub

Private Sub txtDataToWorkSheetRange()

Dim txtFile As String, txtLine As String
Dim wks As Worksheet, rwNo As Long

Set wks = Woksheets("Sheet1")
txtFile = "C\ABC\Text.txt"

rwNo = 2

Open txtFile For Input As #1

Do Until EOF(1)
Line Input #1, txtLine
wks.Range("A1") = "Date"
wks.Range("B1") = "ReferenceNumber "
wks.Range("C1") = "Amt"

wks.Range("A" & rwNo) = "How to get individual date"
wks.Range("B" & rwNo) = "How to get individual Reference Number"
wks.Range("C" & rwNo) = "How to get individual Amt"

rwNo = rwNo + 1
Loop
Close #1
End Sub

SamD
155

 

[alansidman]

Created a Text file called: Test.txt
Opened a blank Excel Sheet and imported the text file into Power Query.
Here is the Mcode for the steps taken

let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\alans\Downloads\Test.txt"), null, null, 1252)}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if Text.StartsWith([Column1], "Amt") then [Column1] else null),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each if Text.StartsWith([Column1],"Reference") then [Column1] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom.1", "Custom"}),
    #"Added Custom2" = Table.AddColumn(#"Filled Up", "Custom.2", each if Text.StartsWith ([Column1],"Ref") then null else if Text.StartsWith([Column1],"Amt") then null else [Column1]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each ([Custom.2] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom.2"})
in
    #"Removed Columns"

Book4
	A	B	C
1	Column1	Custom.1	Custom
2	19-09-2020	Amt 2000.00	Reference Number NZ26X201249Y75140
3	28-09-2020	Amt 8600.00	Reference Number MD00X1485Y47366
4	10-10-2020	Amt 7650.00	Reference Number YN27220X1562M9109
Sheet2

 

[SamDsouza]

Alansidman

Well not at all famous with PowerQuery. Hardly i must have used for studying some examples.

What is Mcode ? Does it need to be written in VBA editor, if not then where does it have to be written.

Just require a simple correction in my VBA code. After Line Input #1, statement not able to get the respective data.

BTW your excel representation has occured mistake as data of column B and Col C needs to be interchanged as per #1 of this thread

Regds
SamD
156

 

[sandy666]

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[SamDsouza]

Sandy666

Thanks for sending the link of Power Query Documentation. This will take sometime for me to absorb

and Alansidman Thanks for suggesting altogether new method for particular structured data of Text file to get represented in Worksheet.

What if there was no Power Query at all and how would one help me with VBA coding ?

SamD
157

 

[alansidman]

To get the order of the columns as you desire, just switch these two lines of code around

   #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if Text.StartsWith([Column1], "Amt") then [Column1] else null),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each if Text.StartsWith([Column1],"Reference") then [Column1] else null),

 

[alansidman]

Option Explicit

Sub TestA()
    Dim i As Long
    Dim lr As Long, lr2 As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If Range("A" & i) = "" Then
            Range("A" & i).EntireRow.Delete
        End If
    Next i

    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lr Step 3
        Range("A" & i & ":A" & i + 2).Copy
        lr2 = Range("F" & Rows.Count).End(xlUp).Row + 1
        Range("F" & lr2).PasteSpecial Transpose:=True
    Next i
End Sub

 

[SamDsouza]

After some efforts i tried below but now I get Error No 62 Input Past End of File.
For above Error what is observed that it incorporates 2 records in Worksheet but before 3rd record uploads in worksheet. Error is generated

at Line Input #1, strBlankLine

The idea of incorporating strBlankline was as per the Structure in Text File displayed in #1 of this thread

Any ideas How to overcome the error without using On Local Error Resume Next?

Private Sub Trial4()

Dim txtFile As String, txtLine As String, refTxt As String
Dim strDate As String, strRefNo As String, strAmt As String, strBlankLine As String

Dim wks As Worksheet, rwNo As Long

Dim i As Integer, j As Integer

Set wks = Worksheets("Sheet1")

txtFile = "C\ABC\Text.txt"

rwNo = 2

Open txtFile For Input As #1
  Do Until EOF(1)

      Line Input #1, strDate
      strDate = Replace(strDate, "Date ", "")

      Line Input #1, strRefNo
      strRefNo = Replace(strRefNo, "Reference Number ", "")
      
      Line Input #1, strAmt
      strAmt = Replace(strAmt, "Amt ", "")

      Line Input #1, strBlankLine
      strBlankLine = Replace(strBlankLine, "", "")
        
         wks.Range("A" & rwNo).Value = strDate
         wks.Range("B" & rwNo).Value = strRefNo
         wks.Range("C" & rwNo).Value = strAmt
               
        rwNo = rwNo + 1
  Loop
 Close #1
End Sub

SamD
158

 

[NimishK]

SamD
Hope you had used "On local error" sometimes in unexpected results

Sending you the below link from vbaexpress.com
Find last Blank Line as well First character as vbcrlf after blank line

The link will show you 2 things Last blank line of text file as well last character as vbrclf of text file.
You need to incorporate the function of First character as vbcrlf of text file. ie vbcrlf character after the Last Blank Line.

This function will return true if the last character is vbCrlf after the last blank line which is immediately after "Amt 7650".

HTH

NimishK

 

[SamDsouza]

NimishK
Thanks for the link. Already i had gone through the same. Nevertheless you gave the hint on

This function will return true if the last character is vbCrlf after the last blank line which is immediately after "Amt 7650".

which made me move on to achieve what i desired

So i did incorporate the function and works well without using On Local Error Resume Next

Manytimes one should achieve the result without using On Local Error Resume Next

Thank you so much ?
SamD
159