# Read text file and find match

**URL:** <https://forums.powershell.org/t/read-text-file-and-find-match/4265>\
**Category:** PowerShell Help\
**Created:** [May 26, 2015, 6:28am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265 "2015-05-26T06:28:33Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![wilfredo-perez](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@wilfredo-perez](https://forums.powershell.org/u/wilfredo-perez)\
**Post date:** [May 26, 2015, 6:28am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/1 "2015-05-26T06:28:33Z")

</div>

Here is the code I am using

```
$Gettext = Get-ChildItem C:\Test\FinancialStatements\*.* -Include *.txt 

Foreach ($text in $Gettext){
$txtline = Get-Content -Path $text 
   
    Foreach($line in $txtline){
    $line1 = Get-Content -Path $line | select-string -Pattern 'Transaction Type:\s(?[a-z].+)' | select -expand Matches | foreach {$_.groups["digit"].value}
    $line1
    }

}
```

$line1 should give me a match but instead it give me the content of the file.

---

<div class="post-metadata">

**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\
**Post date:** [May 26, 2015, 7:48am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/2 "2015-05-26T07:48:03Z")

</div>

You are calling Get-Content too many times:

```
$Gettext = Get-ChildItem C:\Test\FinancialStatements\*.* -Include *.txt 
 
Foreach ($text in $Gettext){
$txtline = Get-Content -Path $text 
 
    Foreach($line in $txtline){
        $line1 = $line | select-string -Pattern 'Transaction Type:\s(?[a-z].+)' | select -expand Matches | foreach {$_.groups["digit"].value}
        $line1
    }
}
```

Keep in mind that if there are 3 lines and line 2 has what you are looking for, $line1 would be blank. You are overwriting $line1 for each line, so on the third line there is no match and it would write nothing.

---

<div class="post-metadata">

**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\
**Post date:** [May 26, 2015, 8:31am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/3 "2015-05-26T08:31:57Z")

</div>

I wanted to show you an example of search the entire contents of a file. Here is what is in the text file:

```
Some Column: Some Data
Transaction Type: 3523tergdg
Another Column: More Data
```

Next we can use the FileSystemObject which was popular in vbScript days to do a .ReadAll() method and get all of the content and search for a regex match:

```
$objFSO = New-Object -ComObject Scripting.FileSystemObject
$textFile = $objFSO.OpenTextFile("C:\Users\Rob\Desktop\test.txt", 1, $false)
$textFile.ReadAll() -match "Transaction Type:\s+(.*)"
$textFile.Close()

$Matches
```

---

<div class="post-metadata">

**Author:** ![wilfredo-perez](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@wilfredo-perez](https://forums.powershell.org/u/wilfredo-perez)\
**Post date:** [May 26, 2015, 11:12am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/4 "2015-05-26T11:12:27Z")

</div>

Rob,  
I would like to use the select-string in the Foreach loop. I will try your way first. So if I have 14 different matches can I use the select-string in the loop?

Thanks  
Freddy

---

<div class="post-metadata">

**Author:** ![wilfredo-perez](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@wilfredo-perez](https://forums.powershell.org/u/wilfredo-perez)\
**Post date:** [May 27, 2015, 1:59am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/5 "2015-05-27T01:59:28Z")

</div>

I did try your script and once it finds a match it exits. How can I make a loop so it can find more matches

---

<div class="post-metadata">

**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\
**Post date:** [May 27, 2015, 2:47am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/6 "2015-05-27T02:47:50Z")

</div>

I think you are overcomplicating this. Do all of the files have the same “headers” in them with the data separated by colons like the example file I used above? You are trying to use a regex solution which I think is overboard to just pull data out of colon-separated data. If your files had no similarity and say Transaction Type was just hidden in all of this random junk:

```
Foo DataFoo DataFoo DataFoo DataFoo DataFoo
Foo DataFoo DataFoo Data Transaction Type: 3523tergdgFoo DataFoo DataFoo DataFoo Data
Foo DataFoo DataFoo DataFoo DataFoo DataFoo DataFoo DataFoo Data
Foo DataFoo DataFoo DataFoo DataFoo DataFoo DataFoo DataFoo Data

Foo DataFoo DataFoo DataFoo DataFoo DataFoo DataFoo Data
Foo DataFoo DataFoo DataFoo DataFoo DataFoo Data
```

In the above, I would use a regex pattern, which is what you are doing in Select-String. However, if your files have the same headers with a colon to separate data, then I would really consider something like this:

```
#Get all text files
$results = Get-ChildItem C:\Users\rsimmers\Desktop\* -Include *.txt -File | foreach {
    #Get the content of the file
    $content = Get-Content $_.FullName
    #Create a blank hash table for properties
    $props = @{}
    #Add a property with the File Name
    $props.Add("File Name", $_.Name)
    foreach($line in $content) {
        #Split the line at the colon to make an array
        $arrLine = $line.Split(":")
        #Add the header ($arrLine[0]) and data ($arrLine[1]) into a hash table
        $props.Add(($arrLine[0]).Trim(), ($arrLine[1]).Trim())
    }
   #Create a new object with the properties and store in $results
   New-Object -TypeName PSObject -Property $props
}

$results | Format-Table -AutoSize
```

I placed 3 text files with the same headers with different data on my desktop and run the script would produce something like this:

```
File Name Another Column Transaction Type Some Column   
--------- -------------- ---------------- -----------   
test.txt More Data 3523tergdg Some Data     
test1.txt Foo Data 35w46235ythdg Different Data
test2.txt Data Foo o68rg352ty34tyr Junk Data
```

Now that the files are parsed, you know what file the data came from, you can do whatever you want with it. If you are convinced you have to use regex, to get multiple matches you would do something like this:

```
$objFSO = New-Object -ComObject Scripting.FileSystemObject
$textFile = $objFSO.OpenTextFile("C:\Users\rsimmers\Desktop\test.txt", 1, $false)
$content = $textFile.ReadAll()
$transaction = ([regex]"Transaction Type:\s+(.*)").match($content).groups[1].value
$anothercolumn = ([regex]"Another Column:\s+(.*)").match($content).groups[1].value
$textFile.Close()
 
$transaction
$anothercolumn
```

There are a lot of ways to do things. If you really want help, you need to be specific with what you are trying to do and the challenges you are having. Provide examples and expected results. Tell us how you want to use the data.

---

<div class="post-metadata">

**Author:** ![wilfredo-perez](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@wilfredo-perez](https://forums.powershell.org/u/wilfredo-perez)\
**Post date:** [May 27, 2015, 4:31am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/7 "2015-05-27T04:31:39Z")

</div>

Rob,

I have 3 + text files in a folder. Each text file have the same info like Transaction Type: Purchase, Cost: 250.00, etc. I already have the code to open Excel and format the sheets. My problem is that it loops through a file once and it goes to the next text file.

This is a sample code:

```
#start Excel

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.add()
$wrksht = $workbook.Worksheets.Item("sheet1")
$wrksht.name = "Hello"
$excel.Visible = $true

$introw = 2

$files = Get-ChildItem -Path C:\Test\FinancialStatements\*.* -Include *.txt
foreach ($txtfile in $files){
     $wrksht.Cells.item($intRow,3) = Get-Content -Path $txtfile | select-string -Pattern 'Transaction Type:\s(?[a-z].+)' | select -expand Matches | foreach {$_.groups["digit"].value}
     $introw ++
}
```

So if a have more than 1 Transaction Type: Purchase it will only match the first one and move to the next Text file. I think I need another foreach loop

---

<div class="post-metadata">

**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\
**Post date:** [May 27, 2015, 5:02am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/8 "2015-05-27T05:02:54Z")

</div>

Does this code work?

```
#Get all text files
$results = Get-ChildItem C:\Users\rsimmers\Desktop\* -Include *.txt -File | foreach {
    #Get the content of the file
    $content = Get-Content $_.FullName
    #Create a blank hash table for properties
    $props = @{}
    #Add a property with the File Name
    $props.Add("File Name", $_.Name)
    foreach($line in $content) {
        #Split the line at the colon to make an array
        $arrLine = $line.Split(":")
        #Add the header ($arrLine[0]) and data ($arrLine[1]) into a hash table
        $props.Add(($arrLine[0]).Trim(), ($arrLine[1]).Trim())
    }
   #Create a new object with the properties and store in $results
   New-Object -TypeName PSObject -Property $props
}
 
$results | Format-Table -AutoSize
```

---

<div class="post-metadata">

**Author:** ![wilfredo-perez](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@wilfredo-perez](https://forums.powershell.org/u/wilfredo-perez)\
**Post date:** [May 27, 2015, 5:54am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/9 "2015-05-27T05:54:22Z")

</div>

This script is some what working for me:

```
#start Excel

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.add()
$wrksht = $workbook.Worksheets.Item("sheet1")
$wrksht.name = "Hello"
$excel.Visible = $true

$introw = 2
$files = Get-ChildItem -Path C:\Test\FinancialStatements\*.* -Include *.txt
Foreach($txtfile in $files){
   Foreach ($line in [System.IO.File]::OpenRead($txtfile)){
   $Trans = $line| select-string -Pattern 'Transaction Type:\s(?[a-z].+)' | select -expand Matches | foreach {$_.groups["digit"].value}
   $Trans
   #$wrksht.Cells.item($intRow,3) = $Trans
   $introw ++

   }
   
}
```

Sample file

Transaction Type: Purchase  
Cost:250.00

Transaction Type: Purchase  
Cost:250.00

Transaction Type: Purchase  
Cost:250.00

Instead of writing to excel line after line it skips two lines and write Transaction Type: Purchase, skips two lines and write. I would like it like so"  
Transaction Type: Purchase  
Transaction Type: Purchase  
Transaction Type: Purchase

Not like:  
Transaction Type: Purchase

Transaction Type: Purchase

Transaction Type: Purchase  
.

---

<div class="post-metadata">

**Author:** ![simon-wahlin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/simon-wahlin/32/407_2.png) [@simon-wahlin](https://forums.powershell.org/u/simon-wahlin)\
**Post date:** [May 27, 2015, 6:12am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/10 "2015-05-27T06:12:01Z")

</div>

Hi Wilfredo,

The regular expression in your code is invalid.  
This part of the expression:

```
(?[a-z].+)
```

In a regular expression, the match of something within parenthesis is called a group.  
By default, each match of a group is assigned a number as an index. To be able to refer  
to a specific group by name you can add the following to the beginning of the group:

```
?
```

This is what your regex almost does. Since you later on refer to the group as “digit” this is propably the regex you need:

```
"Transaction Type:\s(?[a-z].+)"
```

This will match any row containing "Transaction Type:" followed by a whitespace (like space or tab) followed by one or more of any char. The group “digit” will contain the part after the whitespace.

Regarding the looping, if you want multiple matches from one file to appear in the same cell, you need to combine the values to one string. For example like this:

```
foreach ($txtfile in $files){
     $Strings = Get-Content -Path $txtfile | 
     	select-string -Pattern "Transaction Type:\s(?[a-z].+)" | 
     		select -expand Matches | 
     			foreach {$_.groups["digit"].value}
     $wrksht.Cells.item($intRow,3) = $Strings -join ","
     $introw ++
}
```

If your file contains two matching rows like this:  
Transaction Type: Purchase  
Transaction Type: Sale  
The cell would then have the value: Purchase,Sale

If you would like to add each value to its own row, you should probably do something like this:

```
foreach ($txtfile in $files){
    Get-Content -Path $txtfile | 
    	select-string -Pattern "Transaction Type:\s(?[a-z].+)" | 
    		select -expand Matches | 
    			foreach {
    				$wrksht.Cells.item($intRow,3) = $_.groups["digit"].value
        			$intRow++
				}
}
```

Note how I"ve added a line break after each pipechar (|) which I think makes the code a little easier to read.

EDIT: Tried to get code to display properly but I give up

---

<div class="post-metadata">

**Author:** ![simon-wahlin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/simon-wahlin/32/407_2.png) [@simon-wahlin](https://forums.powershell.org/u/simon-wahlin)\
**Post date:** [May 27, 2015, 6:16am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/11 "2015-05-27T06:16:26Z")

</div>

Ops, I just realized that the part of the regex that I thought was missing from your code was just parsed away by the HTML, sorry!

I’ll try to edit my post to get it correct.

---

<div class="post-metadata">

**Author:** ![wilfredo-perez](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@wilfredo-perez](https://forums.powershell.org/u/wilfredo-perez)\
**Post date:** [May 27, 2015, 6:32am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/12 "2015-05-27T06:32:55Z")

</div>

The code you posted worked. but I need to match around 14 different words

---

<div class="post-metadata">

**Author:** ![simon-wahlin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/simon-wahlin/32/407_2.png) [@simon-wahlin](https://forums.powershell.org/u/simon-wahlin)\
**Post date:** [May 27, 2015, 6:43am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/13 "2015-05-27T06:43:36Z")

</div>

13 alternatives to “Transaction Type”?

Does the files you read only contain things you want or alot of other data too?  
It is hard to design a search pattern for a file without knowing the whole content of the file.

---

<div class="post-metadata">

**Author:** ![wilfredo-perez](https://avatars.discourse-cdn.com/v4/letter/w/e99b99/32.png) [@wilfredo-perez](https://forums.powershell.org/u/wilfredo-perez)\
**Post date:** [May 27, 2015, 7:02am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/14 "2015-05-27T07:02:41Z")

</div>

Here are the contents:

Please see the large trade(s) below:

Transaction Type: PURCHASE  
Trade Amount: 250.00  
Trade Date: 05/14/15  
Settlement Date: 05/15/15  
Fund Name and Class: ABCDEFG  
Symbol: ABCD  
CUSIP Number: 12345  
Transfer Agent Fund Number: 123  
State Street Fund Number: 1111

* * *

Transaction Type: PURCHASE  
Trade Amount: 250.00  
Trade Date: 05/14/15  
Settlement Date: 05/15/15  
Fund Name and Class: ABCDEFG  
Symbol: ABCD  
CUSIP Number: 12345  
Transfer Agent Fund Number: 123  
State Street Fund Number: 1111

In some text files there is more of the some data. So I may have 5 text files with the same king of data. I need to match the Trade Date: and input the value “05/14/15” in Excel and do the same for each text file.

---

<div class="post-metadata">

**Author:** ![simon-wahlin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/simon-wahlin/32/407_2.png) [@simon-wahlin](https://forums.powershell.org/u/simon-wahlin)\
**Post date:** [May 27, 2015, 8:17am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/15 "2015-05-27T08:17:18Z")

</div>

Ok, so each files contains many transactions and you want to break out Transaction Type and Trade Date from each transaction?

I would suggest looking into the new cmdlet ConvertFrom-String in PowerShell V5.

If that is not an option, try to read each file and split it in a way that each part contains just one transaction, for that part, replace : with = and use ConvertFrom-StringData to create objects.

---

<div class="post-metadata">

**Author:** ![rob-simmers](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/rob-simmers/32/1010_2.png) [@rob-simmers](https://forums.powershell.org/u/rob-simmers)\
**Post date:** [May 28, 2015, 12:19am UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/16 "2015-05-28T00:19:32Z")

</div>

If each transaction always starts and finishes with the same data, I would use what I posted in the other post you opened for the same thing. Understand that putting the data into Excel is trivial, the important part of this process is parsing the data and making it useable to place in Excel. Your feeding small pieces of information instead of providing examples and requirements up front, so it’s really hard for anyone to actually help you. With that said, here is modified code from the other post:

```
$results = foreach ($file in (Get-ChildItem -Path C:\Users\rsimmers\Desktop\* -Include *.txt -File )) {
    $content = Get-Content $file.FullName
    #Start a record number, so if there are 4 entries in a file, you would see what
    #was the first entry in the file
    $intRecordNum = 1
    foreach ($line in $content) {
        #Process any line that has a colon in it
        if ($line -like "*:*") {
            #Split the line at the colon to create an array with the header and data
            $arr = $line -split ":"
            #Create a switch with the header
            switch ($arr[0].Trim().ToUpper()){
                "TRANSACTION TYPE" {
                    #Transaction Type is the start of a new record, so we create a blank property hashtable
                    $props = @{}
                    #Add the transaction type data
                    $props.Add($arr[0].Trim(),$arr[1].Trim())
                    #Add the file name for reference
                    $props.Add("FileName", $file.Name)
                    #Add the record number to indicate the sequence of data in the file
                    $props.Add("RecordNumber", $intRecordNum)

                } #TRANSACTION TYPE
                "STATE STREET FUND NUMBER" {
                    #State Street is the last entry of a record, so add the data
                    $props.Add($arr[0].Trim(),$arr[1].Trim())
                    #Since it's the last record, we want to actually create the object
                    #with all of the properties we've gathered. This is returned to the $results variable
                    New-Object -TypeName PSObject -Property $props
                    #Again, this our last record, so we increment the record number
                    $intRecordNum++
                } #STATE STREET FUND NUMBER
                default {
                    #This is anything in between the start and finish headers, we're just creating a property for it
                    $props.Add($arr[0].Trim(),$arr[1].Trim())
                } #default
            }#switch header name
        } #if line contains a colon
    } #foreach line
} #foreach file
 
$results | Select * | Format-Table -AutoSize
```

I have 3 text files, one with two transactions, one with 4 and another with 3 using the format you posted above. When I run this against the files, I get the following:

```
Symbol RecordNumber FileName State Street Fund Number Settlement Date CUSIP Number Trade Amount Transaction Type Trade Date Fund Name and Class
------ ------------ -------- ------------------------ --------------- ------------ ------------ ---------------- ---------- -------------------
ABCD 1 test.txt 1111 05/15/15 12345 250.00 PURCHASE 05/14/15 ABCDEFG            
ABCD 2 test.txt 1111 05/15/15 12345 1354.00 PURCHASE 05/14/15 ABCDEFG            
RVFE 1 test1.txt 5342 05/15/15 463432 451.00 PURCHASE 05/14/15 REGFSFVF           
RVFE 2 test1.txt 5342 05/15/15 463432 4845.00 PURCHASE 05/14/15 ABCDEFG            
RVFE 3 test1.txt 5342 05/15/15 463432 5621.00 PURCHASE 05/14/15 REGFSFVF           
RVFE 4 test1.txt 5342 05/15/15 463432 250.00 PURCHASE 05/14/15 ABCDEFG            
GFGE 1 test2.txt 8634 05/15/15 789453 1434.00 PURCHASE 05/14/15 LIOBDMSS           
GFGE 2 test2.txt 8634 05/15/15 789453 3351.00 PURCHASE 05/14/15 ABCDEFG            
GFGE 3 test2.txt 8634 05/15/15 789453 121.00 PURCHASE 05/14/15 LIOBDMSS
```

We now have the filename the transactions came from, the order of the transactions in the file (i.e.RecordNumber) and all of the data for each transaction. Now I highly recommend that you do NOT try to rip this apart and put it in Excel. Validate that that this code correctly parses that data in your files. You should see similar output as above. Again, no Excel, does the data parse correctly? If the data parses, I would convert this code into a function, like Get-TransactionData. Then it’s a simple to get it into Excel.

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:45pm UTC](https://forums.powershell.org/t/read-text-file-and-find-match/4265/17 "2024-05-16T20:45:13Z")

</div>