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]

 

[Fennek]

Hi, try this in CMD:

 find "20000163989" test.txt

regards

 

[Fennek]

a few decades later: Powershell

 get-content test.txt |  foreach { $_ -match "20000163989" }

 

[strooman]

I'm not sure if you only want to find this number: 20000163989
The code prints the lines containing that number to the Immediate window.
Pay special attention to the comment in Red.

Option Explicit

Sub Find_20000163989()
[COLOR="#FF0000"]'Requires a reference to the Microsoft Scripting Runtime.
'Tools | References -> Microsoft Scripting Runtime[/COLOR]

Dim fso As FileSystemObject: Set fso = New FileSystemObject
Dim str_To_Find, strNextLine As String
Dim txtStream

str_To_Find = ";20000163989;"
Set txtStream = fso.OpenTextFile("c:\temp\textfile.txt", ForReading, False)

Do While Not txtStream.AtEndOfStream
    strNextLine = txtStream.ReadLine
    If InStr(1, strNextLine, str_To_Find) > 0 Then
        Debug.Print strNextLine
    End If
Loop
txtStream.Close
End Sub

Adjust the file path to your needs: c:\temp\textfile.txt

 

[jphumani]

Hi there!!!! Thanks for your answer.

I want to find that number, but for each line there is one unique number. How can i say...in Argentina when you receive an invoice, each customer has a sort of identification number. So what i need to do is to search that identification number and calculate the exact tax for that person. Each state gives you a LARGE txt file with millons of "identification numbers". Thats why i need something simple to track the number. And Thats also why after the search i need to copy the entiry line.

Im sorry i dont understand your line in red...

Wait for yout next answer. Thanks a lot for your help.

I'm not sure if you only want to find this number: 20000163989
The code prints the lines containing that number to the Immediate window.
Pay special attention to the comment in Red.

Option Explicit

Sub Find_20000163989()
[COLOR=#ff0000]'Requires a reference to the Microsoft Scripting Runtime.
'Tools | References -> Microsoft Scripting Runtime[/COLOR]

Dim fso As FileSystemObject: Set fso = New FileSystemObject
Dim str_To_Find, strNextLine As String
Dim txtStream

str_To_Find = ";20000163989;"
Set txtStream = fso.OpenTextFile("c:\temp\textfile.txt", ForReading, False)

Do While Not txtStream.AtEndOfStream
    strNextLine = txtStream.ReadLine
    If InStr(1, strNextLine, str_To_Find) > 0 Then
        Debug.Print strNextLine
    End If
Loop
txtStream.Close
End Sub

Adjust the file path to your needs: c:\temp\textfile.txt

 

[jphumani]

Please have a loot (if you can) to my last answer. I've detailed what i need. Thanks again.

 

[Fennek]

Hi,

the code of "strooman" will work, but for this amount of data it could be fairly slow. It is better to read the data at once (if there is enough RAM)

Set FSO = CreateObject("Scripting.FileSystemObject")
Tx = Split(FSO.opentextfile("z:\data.txt").readall, vbCrLf)

regards

(please test it with a few hunderts lines first)

 

[jphumani]

Hi there again! I fell a Little stupid. But where should i paste this part? I understand what you mean, but i have small experience in VBA.

So, how should i put the code?

And in the command button, should i put something? i've only put "search"

And for last, strooman said:

Sub Find_20000163989()
​

'Requires a reference to the Microsoft Scripting Runtime.
'Tools | References -> Microsoft Scripting Runtime​

What should i put there?

Hi,

the code of "strooman" will work, but for this amount of data it could be fairly slow. It is better to read the data at once (if there is enough RAM)

Set FSO = CreateObject("Scripting.FileSystemObject")
Tx = Split(FSO.opentextfile("z:\data.txt").readall, vbCrLf)

regards

(please test it with a few hunderts lines first)

 

[strooman]

OK, first things first. I'm not the best coder so the code below is a little bit cumbersome but I hope it get's you going.

- Open your Visual Basic Editor with the shortcut Alt+F11. When you have a laptop you probably should give the shortcut Fn+Alt+F11. The Fn button on a laptop is called the function key and is used to activate a second function on the top buttons.
- In the top menubar click Insert and then choose Module.
- Copy and Paste the VBA code wich you can find below.
- To activate the red part:

'Requires a reference to the Microsoft Scripting Runtime.
'Tools | References -> Microsoft Scripting Runtime

click in the top menubar Tools and then References. Scroll through the very long list and search for "Microsoft Scripting Runtime" check the checkbox and click OK.
- Make adjustments to the path name in this line: Set txtStream = fspenTextFile("c:\temp\textfile.txt", ForReading, False). Point to the location where your text file is and you want to search.
- Return to your workbook.

Now you have to realize that we don't see your setup. How that looks like in your workbook. So we have to guess. You say: "So what i need to do is to search that identification number and calculate the exact tax for that person. Each state gives you a LARGE txt file with millons of "identification numbers".

Now let's assume that you have a bunch of your identification numbers in Column A of Sheet1 and run the code.

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]

Option Explicit

Sub Find_20000163989()
'Requires a reference to the Microsoft Scripting Runtime.
'Tools | References -> Microsoft Scripting Runtime

Dim fso As FileSystemObject: Set fso = New FileSystemObject
Dim str_To_Find, strNextLine As String
Dim txtStream, LastRow
Dim IdNumber, rngCell As Range
 
'Assume your id numbers in Column A. Find last empty row.
LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Assign Variable IdNumber to Range
Set IdNumber = Sheets("Sheet1").Range("A2:A" & LastRow - 1)

'Cycle through every cell of the Range -> IdNumber
For Each rngCell In IdNumber
    
    'Open your file with the one million lines ! ! !
    Set txtStream = fso.OpenTextFile("c:\temp\textfile.txt", ForReading, False)
    
    'Assign the value of the cell to variable str_To_Find
    str_To_Find = rngCell.Value
    
    'Loop through the lines to find the Identification Number
    'And print the line to Immediate window
    Do While Not txtStream.AtEndOfStream
        strNextLine = txtStream.ReadLine
        If InStr(1, strNextLine, str_To_Find) > 0 Then
            Debug.Print strNextLine
            
            'If Identification Number found exit the Do Loop.
            Exit Do
        End If
    Loop
    'Close the stream
    txtStream.Close
Next
End Sub

 

[Fennek]

Hi,

looking for ONE number, this code should be fairly fast (if excel doesn't crash)

Const iPath As String = "c:\temp\"
Const iFile As String = "AR_Tax-code.txt"
Const Key As String = "20000163989"

Sub F_en()

'read data
Set FSO = CreateObject("Scripting.FileSystemObject")
    Tx = Split(FSO.opentextfile(iPath & iFile).readall, vbCrLf)
Set FSO = Nothing

'select lines with keyword
With CreateObject("Scripting.Dictionary")
    For i = 0 To UBound(Tx)
        If InStr(1, Tx(i), Key) > 0 Then .Add (.Count), Tx(i)
    Next i

'write in Sheet from cells(1, 1)
Cells(1, 1).Resize(.Count) = Application.Transpose(.items)

End With
End Sub