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!

I got Error 13 on this line:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Do While y <= UBound(myArr)[/FONT]

Error says "Type Mismatch"[/FONT]

I've tried with small txt file, didnt work.

If you want i can upload both files.

Any ideas?

You can paste both macro's in a standard module.



This is correct. I didn't implement a search button. Just put some Identification Numbers in column A (starting in A2) and adjust the file path and point to your file with all those lines you want to search. The result will be printed to the Immediate window of the Visual Basic Editor. This is just a basic setup to see if everything is working correctly.

 

[strooman]

Hi there!

I got Error 13 on this line:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Do While y <= UBound(myArr)[/FONT]

Error says "Type Mismatch"[/FONT]

This problem occurs if your range in column A has only one cell with data. In this case, VBA does not convert myArr to an array.
If you try to use it as an array you will get the Type mismatch error.

Put some (more then 1) Identification Numbers in your Column A starting at A2 and then down like the table below.

Excel 2016 Professional (Windows) 64 bit

	A​
1​	Identification_Numbers
2​	20000163989​
3​	20000164626​
4​	20000164625​
5​	20000164840​
6​	20000164161​
7​	20000164087​
8​	20000164187​
9​	20000164197​
10​	20000164667​
11​	20000164263​
12​	20000164913​
13​	20000164521​
14​	20000164541​
15​	20000164762​

Sheet: Sheet1

 

[Rick Rothstein]

@jphumani,

I have a question I am not too clear on... can the Identification Number appear more than once in any single text file?

 

[jphumani]

Hello again my friend, was a little busy with other stuff. I did tried your code, but no luck. It does not give any feedback. I dont know what else to do...

When i paste any identification number, using a model "txt" with little amount of information, column B stays in blank. Any ideas?

You can paste both macro's in a standard module.



This is correct. I didn't implement a search button. Just put some Identification Numbers in column A (starting in A2) and adjust the file path and point to your file with all those lines you want to search. The result will be printed to the Immediate window of the Visual Basic Editor. This is just a basic setup to see if everything is working correctly.

 

[jphumani]

Hi there Rick! Sorry i did not answer before. The identification number is always unique. It's like the passport number, but we you use it for commercial purpose.

Thats why the archive is soooo heavy (millons of records) and what i was looking for was a "search" button, but i couldnt solve it yet.

@jphumani,

I have a question I am not too clear on... can the Identification Number appear more than once in any single text file?

 

[jphumani]

Hi Leith! could you find another approach to this? ive tried your code. Works fine with small txt, but everytime i try to use "the big file" Excel freezes.

Hi Leith! allright! I've tested and...

With the sample, just fine.
With the complete file...i let it run about 1 hour and still "proccesing". So, maybe the file is to big for this code.

Could not solve it.

I wanted to let you know.

 

[sandy666]

maybe Power Query:
(with your small sample txt file)

ID

ID

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Column11

Column12

20000691810​

20000691810​

22082019​

1092019​

30092019​

20000691810​

D

S

N

0​

0​

0​

0​

DE BENEDETTI ISIDORO

[SIZE=1]// ID
let
    Source = Excel.CurrentWorkbook(){[Name="ID"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}})
in
    #"Changed Type"

// caba SAMPLE
let
    Source = Csv.Document(File.Contents("D:\test\jphumani\caba SAMPLE.txt"),[Delimiter=";", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type number}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type number}, {"Column9", type number}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", type text}})
in
    #"Changed Type"

// Merge1
let
    Source = Table.NestedJoin(ID,{"ID"},#"caba SAMPLE",{"Column4"},"caba SAMPLE",JoinKind.LeftOuter),
    #"Expanded caba SAMPLE" = Table.ExpandTableColumn(Source, "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"})
in
    #"Expanded caba SAMPLE"[/SIZE]

this is NOT vba

 

[jphumani]

Hello! How u doing? Thanks for your answer. This is totally new for me. Should i download Power Query and then paste the code? How and where? haha. Sorry...totally rookie.

maybe Power Query:
(with your small sample txt file)

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column2[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column3[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column4[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column5[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column6[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column7[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column8[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column9[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column10[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column11[/COLOR]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column12[/COLOR]

20000691810​

20000691810​

22082019​

1092019​

30092019​

20000691810​

D

S

N

0​

0​

0​

0​

DE BENEDETTI ISIDORO

<tbody>
</tbody>

[SIZE=1]// ID
let
    Source = Excel.CurrentWorkbook(){[Name="ID"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}})
in
    #"Changed Type"

// caba SAMPLE
let
    Source = Csv.Document(File.Contents("D:\test\jphumani\caba SAMPLE.txt"),[Delimiter=";", Columns=12, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type number}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type number}, {"Column9", type number}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", type text}})
in
    #"Changed Type"

// Merge1
let
    Source = Table.NestedJoin(ID,{"ID"},#"caba SAMPLE",{"Column4"},"caba SAMPLE",JoinKind.LeftOuter),
    #"Expanded caba SAMPLE" = Table.ExpandTableColumn(Source, "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"})
in
    #"Expanded caba SAMPLE"[/SIZE]

this is NOT vba

 

[sandy666]

1. what is your excel version (not a build number)
2. don't quote whole post. use Reply not Reply with Quote - or quote only necessary lines

 

[strooman]

I did tried your code, but no luck. It does not give any feedback. I dont know what else to do...

When i paste any identification number, using a model "txt" with little amount of information, column B stays in blank. Any ideas?

I'm not sure if you follow the instructions I gave. For instance:
Do you know what the Immediate Window is?
Do you understand the setup I gave?
Please, can you answer these questions?

Below is an image of the Visual Basic Editor. In the red rectangle you see the Immediate Window. This is the place where the results are displayed (as you can see from the screenshot). So the results are NOT displayed in column B. Above the Immediate Window you see a part of the code with the Sub name:
Sub Read_Large_File()

Then, in Column A I have your Identification Numbers:

When I put the cursor within the code with the name, Read_Large_File() and I press F5 the code executes and gives me the desired results in the Immediate Window. I used your file:
https://drive.google.com/file/d/13IExv1GqdYa_5YKRzswjExcY4zm3uh2v/view
with the name cabaSAMPLE.txt and the matching Identification Numbers.

I don't know how to explain better.
Does this code execute?
Do you have the correct file path? In my code it's c:\temp\cabaSAMPLE.txt. Perhaps you have to adjust this path.