Search in TXT file and return line to Excel

[jphumani]

Hi there to evereybody!

Im Juan and i'm writing from argentina.

I have a TXT file with millons o lines (literally) like this:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]22082019;01092019;30092019;20000163989;D;S;N;0,00;0,00;00;00;ETCHEVERRIGARAY JUAN CARLOS

I would like to use a command button to search for the number i've marked with bold. In addition i'd like to recieve the entire line in Excel.

Basically each line contains some tax info for argentinian members.

I've seacher the web and tried something like this:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Function buscar()
Open "D:\caba.txt" For Input As #1
Do While Not EOF(1)
Line Input #1 , dato
If dato = UserForm1.TextBox1 Then
Range("b3").Value = UserForm1.TextBox1
Range("c3").Value = dato
End Function

Wish you can help me.

Thanks in advantage

Juan Pablo
[/FONT]_{^{<strike>
</strike>}}[/FONT]

 

[jphumani]

Hi there...again!

Allright, i didnt know what was "inmediate window", that's why i skipped that part. Is there any chance to paste results in Excel? And what would be better, can i put a "search button" that launchs the code?

Im using Excel 2013 on Windows 10 64bits. That's my regular setup, but in other computers the 're still using Win7.

If i start the code once, it Works fine, but if i want to search for a second time, it says "not enough memory", halt options marks "[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Get #i , , res "

[/FONT][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Thanks again for all your help. Believe me that this tool can help a lot with the everyday work.

Cheers,

Juan Pablo
[/FONT]

 

[sandy666]

with Excel 2013 you'll need PowerQuery add-in from MS site (free)
after install open excel file, show queries, (you'll see error probably), dbl click on query, change path to your txt file in Advanced Editor then Refresh All

[SIZE=1]let
    SourceCSV = Csv.Document(File.Contents("[/SIZE][B][COLOR="#FF0000"]D:\test\jphumani\caba SAMPLE.txt[/COLOR][/B][SIZE=1]"),[Delimiter=";", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    TypeCSV = Table.TransformColumnTypes(SourceCSV,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type number}, {"Column9", type number}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", type text}}),
    SourceID = Excel.CurrentWorkbook(){[Name="ID"]}[Content],
    TypeID = Table.TransformColumnTypes(SourceID,{{"ID", type text}}),
    Result = Table.NestedJoin(TypeID,{"ID"},TypeCSV,{"Column4"},"caba SAMPLE",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Result, "caba SAMPLE", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),
    RC = Table.RemoveColumns(Expand,{"ID"})
in
    RC[/SIZE]

Example file

Edit:
more about PowerQuery

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference

 

[strooman]

#i[/URL] , , res "

Please confirm, does the code run the first time accurate? Are the results correct?

 

[jphumani]

Hi there! Yeah, it worked just fine, but only once.