Need to output multiple rows to CSV file

Question

I am using the following script that iterates through hundreds of text files looking for specific instances of the regex expression within. I need to add a second data point to the array, which tells me the object the pattern matched in.

In the below script the [Regex]::Matches($str, $Pattern) | % { $_.Value } piece returns multiple rows per file, which cannot be easily output to a file.

What I would like to know is, how would I output a 2 column CSV file, one column with the file name (which should be $_.FullName), and one column with the regex results? The code of where I am at now is below.

$FolderPath = "C:\Test"

$Pattern = "(?i)(?<=\b^test\b)\s+(\w+)\S+"
$Lines = @()

Get-ChildItem -Recurse $FolderPath -File | ForEach-Object {
    $_.FullName
    $str = Get-Content $_.FullName
    $Lines += [Regex]::Matches($str, $Pattern) |
              % { $_.Value } |
              Sort-Object |
              Get-Unique
}

$Lines = $Lines.Trim().ToUpper() -replace '[\r\n]+', ' ' -replace ";", '' |
         Sort-Object |
         Get-Unique # Cleaning up data in array

Please show sample input as well as the desired and actual output corresponding to that input. — Ansgar Wiechers
– Ansgar Wiechers, Commented Oct 18, 2017 at 1:27
I agree because the " and one column with the regex results" is very confusing — Alex Sarafian
– Alex Sarafian, Commented Oct 18, 2017 at 5:12
Lets say the script searches through 2 files, file1.txt and file2.txt. The regex pattern is looking for the word after the word 'test'. If there are multiple instances within a file, the regex expression will return multiple results. So the output would be a csv file with 2 columns; column 1 would be the file the script found the results in, and column 2 would be the actual matches from the regex pattern. So if there were 3 matches in file1.txt the intention is for the script to have 3 rows, col1 says file1.txt for each row, and col2 would be 'match1', 'match2' etc. — shannonjk
– shannonjk, Commented Oct 18, 2017 at 17:47

Parrish · Accepted Answer · 2017-10-18 03:23:35Z

1

I can think of two ways but the simplest way is to use a hashtable (dict). Another way is create psobjects to fill your Lines variable. I am going to go with the simple way so you can only use one variable, the hashtable.

$FolderPath = "C:\Test"  

$Pattern = "(?i)(?<=\b^test\b)\s+(\w+)\S+"                     
$Results =@{}                                                                                 

    Get-ChildItem -Recurse $FolderPath -File |                                              
    ForEach-Object {                                                                                         
        $str = Get-Content $_.FullName                                                          
        $Line =  [regex]::matches($str,$Pattern) | % { $_.Value } | Sort-Object | Get-Unique
        $Line = $Line.Trim().ToUpper() -Replace '[\r\n]+', ' ' -Replace ";",'' | Sort-Object | Get-Unique # Cleaning up data in array
        $Results[$_.FullName] = $Line
    }
    $Results.GetEnumerator() | Select @{L="Folder";E={$_.Key}}, @{L="Matches";E={$_.Value}} | Export-Csv -NoType -Path <Path to save CSV>

Your results will be in $Results. $Result.keys contain the folder names. $Results.Values has the results from expression. You can reference the results of a particular folder by its key $Results["Folder path"]. of course it will error if the key does not exist.

edited Oct 18, 2017 at 3:23

answered Oct 18, 2017 at 1:27

Parrish

1775 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shannonjk Over a year ago

Thank you Parrish, this is very close to what I need! The only thing left is that this outputs the results as a string instead of separate rows. Is there any way to achieve this?

Parrish Over a year ago

Now that I look, your line if it is an array will not return the array but the object name. Looks like you are trying to put a CSV inside a CSV. Unless you process the lines as a here string or some kind of separator it will not work. If you want separate rows in (sounds like embedded CSV) then I would just save out to JSON. CSV takes just string and integers but does not understand multiline values. JSON understand arrays.

Collectives™ on Stack Overflow

Need to output multiple rows to CSV file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related