# Powershell Regex and Excel

**URL:** <https://forums.powershell.org/t/powershell-regex-and-excel/4227>\
**Category:** PowerShell Help\
**Created:** [May 19, 2015, 5:40am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227 "2015-05-19T05:40:37Z")\
**Posts on this page:** 13\
**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 19, 2015, 5:40am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/1 "2015-05-19T05:40:37Z")

</div>

I have a user who gets a file via email and has to take certain parts from the email and insert it into Excel. So for example: the email come is with the following info:

User: John Doe  
Purchase: Laptop  
Cost: 1,300.00

For now I am saving the contents of the email into a text file and using Regex to get the pattern and save the value into excel. The problem is that it I have more than one user who purchased a computer, it just reads to first user and that’s it.

This is the code I am using  
#start Excel

Add-Type -AssemblyName Microsoft.Office.Interop.Excel  
$excel = New-Object -ComObject Excel.Application  
$excel.Visible = $true  
$workbook = $excel.Workbooks.add()  
$sheet1 = $workbook.Worksheets.Item(“Sheet1”)  
$sheet1.Name = “Week ending in April”  
$sheet2 = $workbook.Worksheets.Item(“Sheet2”)  
$sheet2.Name = “April 2015”  
$sheet3 = $workbook.Worksheets.Item(“Sheet3”)  
$sheet3.Name = “Total by Funds”

#Add Heading Names  
$sheet1.Cells.item(1,1) = ‘User’  
$sheet1.Cells.item(1,2) = ‘Purchase’  
$sheet1.Cells.item(1,3) = ‘Cost’

#Format Spreadsheet  
$sheet1.Columns.Item(“A”).columnwidth=“25”  
$sheet1.Columns.Item(“B”).columnwidth=“15”  
$sheet1.Columns.Item(“C”).columnwidth=“15”

$range = $excel.Range(“A1”, “O1”)  
$range.font.bold=$true  
#$range.Columns(“A1”, “O1”).HorizonTalAlignment = -4108

#$range.ColumnWidth =“30”

$introw = 2

# Start reading the file\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

$file = Get-Childitem C:\Test\FinancialStatements\*.\* -Include \*.txt  
foreach ($file in $files)  
{

$sheet1.Cells.item($intRow,1) = gc -Path $file | Select-String -Pattern ‘User: (?[a-z]})’ | select -Expand Matches | foreach {$_.groups[“digit”].value}  
$sheet1.Cells.item($intRow,2) = gc -Path $file | Select-String -Pattern ‘Purchase: (?\d{2}[/]\d{2}[/]\d{2})’ | select -Expand Matches | foreach {$_.groups[“digit”].value}  
$sheet1.Cells.item($intRow,3) = gc -Path $file | Select-String -Pattern ‘Cost: (?\d[0-9a-z]+)’ | select -Expand Matches | foreach {$\_.groups[“digit”].value}  
}

---

<div class="post-metadata">

**Author:** ![matt-mcnabb](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matt-mcnabb](https://forums.powershell.org/u/matt-mcnabb)\
**Post date:** [May 19, 2015, 6:00am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/2 "2015-05-19T06:00:41Z")

</div>

Looks like you’re running everything against the second row. You’ll need to increment the $introw variable in each iteration of the foreach loop:

```
foreach ($file in $files)
{
    $sheet1.Cells.item($intRow,1) = gc -Path $file | Select-String -Pattern 'User: (?[a-z]})' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $sheet1.Cells.item($intRow,2) = gc -Path $file | Select-String -Pattern 'Purchase: (?\d{2}[/]\d{2}[/]\d{2})' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $sheet1.Cells.item($intRow,3) = gc -Path $file | Select-String -Pattern 'Cost: (?\d[0-9a-z]+)' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $introw++
}
```

---

<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 19, 2015, 6:40am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/3 "2015-05-19T06:40:49Z")

</div>

I do have $introw++ at the end. It did not copy over

---

<div class="post-metadata">

**Author:** ![matt-mcnabb](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matt-mcnabb](https://forums.powershell.org/u/matt-mcnabb)\
**Post date:** [May 19, 2015, 9:33am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/4 "2015-05-19T09:33:11Z")

</div>

Can you edit your OP so we can see exactly what the script looks like?

Also, can you use the code formatting tags so that we can read this a little easier? It’s in the top ribbon of the post editor - just put all your code between the ‘pre’ tags.

---

<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 20, 2015, 12:20am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/5 "2015-05-20T00:20:03Z")

</div>

I would parse the file into a Powershell object and then just select the first line using Select-Object. There is probably a regex that could capture the data, but this works too. Simply split the lines at the semi-colon. If it’s USER it is the start of new record, so initialize the hashtable. If it’s COST, it’s the last part of a record, so generate an object and return to $result. Anything in the middle, add it to the hashtable:

```
$content = Get-Content C:\Users\Rsimmers\Desktop\test.txt.txt

$results = foreach ($line in $content) {
    $arr = $line -split ":"
    switch ($arr[0].ToUpper()){
        "USER" {
            $props = @{}
            $props.Add($arr[0].Trim(),$arr[1].Trim())
        }
        "COST" {
            $props.Add($arr[0].Trim(),$arr[1].Trim())
            New-Object -TypeName PSObject -Property $props
        }
        default {
            $props.Add($arr[0].Trim(),$arr[1].Trim())
        }
    }
}

$results | Select User, Purchase, Cost | Format-Table -AutoSize
```

Output:

```
User Purchase Cost    
---- -------- ----    
John Doe Laptop 1,300.00
Sally Smith Desktop 800.00
```

Then you would just need to do something like this:

```
foreach ($record in $results | Select -First 1) {

    $sheet1.Cells.item($intRow,1) = $record.User
    $sheet1.Cells.item($intRow,2) = $record.Purchase
    $sheet1.Cells.item($intRow,3) = $record.Cost
}
```

---

<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 20, 2015, 4:24am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/6 "2015-05-20T04:24:20Z")

</div>

I have three text file in a dir and I am using this code to parse each file

```
$files = Get-ChildItem C:\Test\FinancialStatements\*.* -Include *.txt 
foreach($file in $files)
{

Get-Content $file | Select-String -Pattern 'Transaction Type: (?[a-z]})' | select -Expand Matches | foreach {$_.groups["digit"].value}
Get-Content $file | Select-String -Pattern 'Settlement Date: (?\d{2}[/]\d{2}[/]\d{2})' | select -Expand Matches | foreach {$_.groups["digit"].value}
Get-Content $file | Select-String -Pattern 'CUSIP Number: (?\d[0-9a-z]+)' | select -Expand Matches | foreach {$_.groups["digit"].value}
}
```

\*\*It reads all three text files

But when try to write to excel, it only read one text file and closes.

```
$fileDir = Get-ChildItem C:\Test\FinancialStatements\
foreach($file in $files)
{
    $files = Get-ChildItem C:\Test\FinancialStatements\*.* -Include *.txt 
    $sheet1.Cells.item($intRow,1) = Get-Content -Path $file | Select-String -Pattern 'User Name: (?[a-z]})' | select -Expand Matches | foreach {$_.groups["digit"].value} 
    $sheet1.Cells.item($intRow,2) = Get-Content -Path $file | Select-String -Pattern 'Purchase: (?\d{2}[/]\d{2}[/]\d{2})' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $sheet1.Cells.item($intRow,3) = Get-Content -Path $file | Select-String -Pattern 'Cost:: (?\d[0-9a-z]+)' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $introw ++
    
}
```

---

<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 20, 2015, 4:56am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/7 "2015-05-20T04:56:23Z")

</div>

Debug one thing at a time. First you need to determine if your for loop is returning the information you expect and each file is being enumerated, so try this:

```
$files = Get-ChildItem C:\Test\FinancialStatements\*.* -Include *.txt 
foreach($file in $files) {
    "Processing {0}" -f $file.Name
    $TransType = Get-Content $file | Select-String -Pattern 'Transaction Type: (?[a-z]})' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $SettlementDate = Get-Content $file | Select-String -Pattern 'Settlement Date: (?\d{2}[/]\d{2}[/]\d{2})' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $CUSIP = Get-Content $file | Select-String -Pattern 'CUSIP Number: (?\d[0-9a-z]+)' | select -Expand Matches | foreach {$_.groups["digit"].value}

    "Transaction Type: {0}" -f $TransType
    "Settlement Date: {0}" -f $SettlementDate
    "CUSIP Number: {0}" -f $CUSIP
}
```

Are you seeing each file you expect and the expected results?

---

<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 20, 2015, 5:08am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/8 "2015-05-20T05:08:11Z")

</div>

Rob,

I can see that it’s reading all three text files beacuse of the info it writes to the console. The problem is when I start to write to excel, it only looks at one text file and closes. It does not finish reading the text file and moves to the next text file.

Thanks  
Freddy

---

<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 20, 2015, 6:19am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/9 "2015-05-20T06:19:21Z")

</div>

The code you posted above looks incorrect, it should look like this:

```
#$fileDir = Get-ChildItem C:\Test\FinancialStatements
$intRow = 0
$files = Get-ChildItem C:\Test\FinancialStatements\*.* -Include *.txt 
foreach($file in $files) {  
    $sheet1.Cells.item($intRow,1) = Get-Content -Path $file | Select-String -Pattern 'User Name: (?[a-z]})' | select -Expand Matches | foreach {$_.groups["digit"].value} 
    $sheet1.Cells.item($intRow,2) = Get-Content -Path $file | Select-String -Pattern 'Purchase: (?\d{2}[/]\d{2}[/]\d{2})' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $sheet1.Cells.item($intRow,3) = Get-Content -Path $file | Select-String -Pattern 'Cost:: (?\d[0-9a-z]+)' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $intRow ++
}
```

---

<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 20, 2015, 7:23am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/10 "2015-05-20T07:23:53Z")

</div>

Rob,  
Can you try the below script.

```
#start Excel

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.add()
$sheet1 = $workbook.Worksheets.Item("Sheet1")
$sheet1.Name = "Week ending in April"
$sheet2 = $workbook.Worksheets.Item("Sheet2")
$sheet2.Name = "April 2015"
$sheet3 = $workbook.Worksheets.Item("Sheet3")
$sheet3.Name = "Total by Funds"

#Add Heading Names
$sheet1.Cells.item(1,1) = 'Week Ending Date'
$sheet1.Cells.item(1,2) = 'Trade Date'
$sheet1.Cells.item(1,3) = 'Cusip'
$sheet1.Cells.item(1,4) = 'SSID'
$sheet1.Cells.item(1,5) = 'Fund Name and Class'
#$sheet1.Cells.item(1,6) = 'Class'
$sheet1.Cells.item(1,6) = 'Affiliate'
$sheet1.Cells.item(1,7) = 'Type of Trade'
$sheet1.Cells.item(1,8) = 'Subscriptions'
$sheet1.Cells.item(1,9) = '# of Sub Trades'
$sheet1.Cells.item(1,10) = 'Redemptions'
$sheet1.Cells.item(1,11) = '# of Red Trades'
$sheet1.Cells.item(1,12) = 'Net Flows'
$sheet1.Cells.item(1,13) = 'Dealer'
$sheet1.Cells.item(1,14) = '# of Trades'
$sheet1.Cells.item(1,7).HorizonTalAlignment = -4108
#$sheet1.Cells.Item(1,1).font.bold=$true

#Format Spreadsheet
$sheet1.Columns.Item("A").columnwidth="25"
$sheet1.Columns.Item("B").columnwidth="15"
$sheet1.Columns.Item("C").columnwidth="15"
$sheet1.Columns.Item("D").columnwidth="10"
$sheet1.Columns.Item("E").columnwidth="60"
$sheet1.Columns.Item("F").columnwidth="25"
$sheet1.Columns.Item("G").columnwidth="15"
$sheet1.Columns.Item("H").columnwidth="20"
$sheet1.Columns.Item("I").columnwidth="20"
$sheet1.Columns.Item("J").columnwidth="20"
$sheet1.Columns.Item("K").columnwidth="20"
$sheet1.Columns.Item("L").columnwidth="15"
$sheet1.Columns.Item("M").columnwidth="30"
$sheet1.Columns.Item("N").columnwidth="15"

$range = $excel.Range("A1", "O1")
$range.font.bold=$true
#$range.Columns("A1", "O1").HorizonTalAlignment = -4108

#$range.ColumnWidth ="30"

$introw = 2

$files = Get-ChildItem C:\Test\*.* -Include *.txt 
$txtFile = gc $files.
Foreach ($file in $files)
{
    Foreach($txtfile in $file){
           $sheet1.Cells.item($intRow,2) = Get-Content $txtfile | Select-String -Pattern 'CUSIP Number: (?\d[0-9a-z]+)' | select -Expand Matches | foreach {$_.groups["digit"].value}
    $introw++
    $txtFile 
    }

}
```

It give me the name of the three text files, but its not reading each text from the beginning to end. It reads the first few line than it goes to the next file and so on. I need it to read the whole text file. I think the excel part is the problem. I am trying different things.

---

<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 21, 2015, 1:41am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/11 "2015-05-21T01:41:03Z")

</div>

Your logic is still not correct. I think what you are not understanding is that Get-Content returns an array of lines, not the entire text file, so you can’t search for the pattern in the entire file.

-Get the files  
–for each file  
—get-content of file  
----for each line in content  
-----if this line has foo in it, parse with this regex

This goes back to my original post, I would parse the files and create objects FIRST and then process with Excel. With that logic, you can also validate if files exist and content exists prior to opening excel and creating all of those sheets to put nothing in it. Re-read my first post and see how I’m checking what is in each line before I create the object.

---

<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 21, 2015, 1:45am UTC](https://forums.powershell.org/t/powershell-regex-and-excel/4227/12 "2015-05-21T01:45:49Z")

</div>

OK, I will try it

---

<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/powershell-regex-and-excel/4227/13 "2024-05-16T20:45:15Z")

</div>