Search in TXT file and return line to Excel

J

jphumani

New Member
Joined
Jan 21, 2019
Messages
18
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]
 
Sort by date Sort by votes
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?

strooman said:
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.
Click to expand...
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
jphumani said:
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]
Click to expand...

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td=bgcolor:#FFFF00]Identification_Numbers[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
20000163989​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
20000164626​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
20000164625​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
20000164840​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
20000164161​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
20000164087​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
20000164187​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
20000164197​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
20000164667​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
20000164263​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
20000164913​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
20000164521​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]
20000164541​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]
20000164762​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
@jphumani,

I have a question I am not too clear on... can the Identification Number appear more than once in any single text file?
 
Upvote 0
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?

strooman said:
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.
Click to expand...
 
Upvote 0
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.

Rick Rothstein said:
@jphumani,

I have a question I am not too clear on... can the Identification Number appear more than once in any single text file?
Click to expand...
 
Upvote 0
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.

jphumani said:
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.
Click to expand...
 
Upvote 0
maybe Power Query:
(with your small sample txt file)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td][/td][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column3[/td][td=bgcolor:#70AD47]Column4[/td][td=bgcolor:#70AD47]Column5[/td][td=bgcolor:#70AD47]Column6[/td][td=bgcolor:#70AD47]Column7[/td][td=bgcolor:#70AD47]Column8[/td][td=bgcolor:#70AD47]Column9[/td][td=bgcolor:#70AD47]Column10[/td][td=bgcolor:#70AD47]Column11[/td][td=bgcolor:#70AD47]Column12[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
20000691810​
[/td][td][/td][td=bgcolor:#E2EFDA]
20000691810​
[/td][td=bgcolor:#E2EFDA]
22082019​
[/td][td=bgcolor:#E2EFDA]
1092019​
[/td][td=bgcolor:#E2EFDA]
30092019​
[/td][td=bgcolor:#E2EFDA]
20000691810​
[/td][td=bgcolor:#E2EFDA]D[/td][td=bgcolor:#E2EFDA]S[/td][td=bgcolor:#E2EFDA]N[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]
0​
[/td][td=bgcolor:#E2EFDA]DE BENEDETTI ISIDORO[/td][/tr]
[/table]

Code: 
[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
 
Last edited:
Upvote 0
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.

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

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]ID[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column1[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column2[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column3[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column4[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column5[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column6[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column7[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column8[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column9[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column10[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column11[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Column12[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]
20000691810​
[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]
20000691810​
[/TD]
[TD="bgcolor: #E2EFDA"]
22082019​
[/TD]
[TD="bgcolor: #E2EFDA"]
1092019​
[/TD]
[TD="bgcolor: #E2EFDA"]
30092019​
[/TD]
[TD="bgcolor: #E2EFDA"]
20000691810​
[/TD]
[TD="bgcolor: #E2EFDA"]D[/TD]
[TD="bgcolor: #E2EFDA"]S[/TD]
[TD="bgcolor: #E2EFDA"]N[/TD]
[TD="bgcolor: #E2EFDA"]
0​
[/TD]
[TD="bgcolor: #E2EFDA"]
0​
[/TD]
[TD="bgcolor: #E2EFDA"]
0​
[/TD]
[TD="bgcolor: #E2EFDA"]
0​
[/TD]
[TD="bgcolor: #E2EFDA"]DE BENEDETTI ISIDORO[/TD]
[/TR]
</tbody>[/TABLE]


Code: 
[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
Click to expand...
 
Upvote 0
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
 
Upvote 0
jphumani said:
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?
Click to expand...

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()

MZEcOT4.png


Then, in Column A I have your Identification Numbers:

AJ4Jahc.png


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
VBA: open and copy data from multiple (txt. files) to excel
Replies
1
Views
618
NdNoviceHlp
NdNoviceHlp
J
  • Solved
Excel vba: Browse to text file and split at each ~ on new row
Replies
3
Views
604
JEC
JEC
F
  • Question Question
How to find an exact number match in a text file?
Replies
9
Views
1K
frankT68
F
F
  • Solved
Extract specific lines from a TXT file to an Excel Sheet
Replies
2
Views
4K
frerofrero
F
A
  • Question Question
Search for text in txt file and copy the words to excel sheet
Replies
4
Views
901
aejaz7
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top