ForEach-Object Where

I’m working on a script that will:

  1. Query a SQL table that shows what people have access to, and who they report to (total result 160,000 rows).
  2. Query a SQL table that shows all the distinct manager names (total result 400 rows).
  3. For each manager, create a separate XLSX file.
  4. Add the header row to each XLSX file.
  5. Place all results of query #1 into the XLSX file, but only where the manager name in Query 1 matches the first manager name.
  6. Iterate through the list of managers and repeat step #5 for manager #2, manager #3, etc.
  7. Save each file as “Access Review - [manager name].xlsx”

The following script does everything, except it places the total result of 160,000 rows into each manager’s XLSX file, rather than the subset of just that manager’s data. The script seems so close, but I need step #5 to limit the result of each iteration based on a match with the manager’s name. I hope this explanation makes sense. Any guidance is much appreciated.

#server and db variables 
$serverName = "ServerName"; 
$databaseName = "Database"; 
#the save location for the new Excel file
$filepath = "C:\users\desktop\reviews\Access Review - " 
       
#create two Datasets to store the DataTables for Access and Manager  
$accessDataSet = new-object "System.Data.DataSet" "Access"
$managerDataSet = new-object "System.Data.DataSet" "Manager"
    
#create a Connection to the SQL Server database
$cn = new-object System.Data.SqlClient.SqlConnection "server=$serverName;database=$databaseName;Integrated Security=sspi"
$queryAccess = "SELECT * FROM AccessReviewTable ORDER BY Manager, Associate, LdapID, [Order], [Application], Permission_Code;"
$queryManager = "SELECT [Manager Name] FROM ManagerTable ORDER BY [Manager Name];"
#Create a SQL Data Adapter to place the access data into the first DataSet
$dataAdapter1 = new-object "System.Data.SqlClient.SqlDataAdapter" ($queryAccess, $cn) 
    $dataAdapter1.Fill($accessDataSet) | Out-Null
#Create a SQL Data Adapter to place the manager data into the second DataSet
$dataAdapter2 = new-object "System.Data.SqlClient.SqlDataAdapter" ($queryManager, $cn) 
    $dataAdapter2.Fill($managerDataSet) | Out-Null
#close the connection 
$cn.Close()
      
$dataTable1 = new-object "System.Data.DataTable" "Access"
$dataTable1 = $accessDataSet.Tables[0]
$dataTable2 = new-object "System.Data.DataTable" "Manager"
$dataTable2 = $managerDataSet.Tables[0]
    
    $dataTable2 | ForEach-Object{
        #create excel object
        $excel = New-Object -ComObject Excel.Application
        $excel.visible = $True
        $workbook = $excel.Workbooks.add()
        #create and name worksheet 
        $sheet1 = $workbook.worksheets.Item(1)
        $sheet1.name = "Access Review"
        #Add the Row Header
        $sheet1.Cells.Item(1,1) = 'LdapID'
        $sheet1.Cells.Item(1,2) = 'Associate'
        $sheet1.Cells.Item(1,3) = 'Platform'
        $sheet1.Cells.Item(1,4) = 'Application'
        $sheet1.Cells.Item(1,5) = 'Permission_Code'
        $sheet1.Cells.Item(1,6) = 'Permission_Desc'
        $sheet1.Cells.Item(1,7) = 'Read'
        $sheet1.Cells.Item(1,8) = 'Add'
        $sheet1.Cells.Item(1,9) = 'Update'
        $sheet1.Cells.Item(1,10) = 'Delete'
        $sheet1.Cells.Item(1,11) = 'Manager'
        $sheet1.Cells.Item(1,12) = 'Authorized Y/N'
            
            $x = 2
            $dataTable1 | ForEach-Object (Where {$_."Manager Name" -eq $_.Manager}){
                $sheet1.cells.item($x, 1) =  $_.LdapID
                $sheet1.cells.item($x, 2) =  $_.Associate
                $sheet1.cells.item($x, 3) =  $_.Platform
                $sheet1.cells.item($x, 4) =  $_.Application
                $sheet1.cells.item($x, 5) =  $_.Permission_Code
                $sheet1.cells.item($x, 6) =  $_.Permission_Desc
                $sheet1.cells.item($x, 7) =  $_.Read
                $sheet1.cells.item($x, 8) =  $_.Add
                $sheet1.cells.item($x, 9) =  $_.Update
                $sheet1.cells.item($x, 10) =  $_.Delete
                $sheet1.cells.item($x, 11) =  $_.Manager
                $sheet1.cells.item($x, 12) =  $_.Authorized
                $range1 = $sheet1.UsedRange
                $range1.EntireColumn.AutoFit()
                $x++
            }
        $excel.ActiveWorkbook.SaveAs("$filepath" + $_."Manager Name" + ".xlsx")
        $excel.quit()
    }

uhm… line 52 should use -eq operator not = sign…

Thanks for catching that, Sam. Yes, I updated = to the -eq operator, but I’m getting the same results. Looks like the Where filter isn’t doing anything. I even tried modifying it to (Where {$_.Manager -eq ‘Doe, Jane’}) to try and filter the first query results to manager name “Doe, Jane” but the script is still adding all 160k rows of data into each XLSX.

seens you try to compare $.“Manager Name” from one datatable with $.Manager from another datatable. use temp variable for first $_

and why you do not do correlation by sql query ?

something like

"select .... join .... sort by manager, date" ... foreach table.line if (currentmanager != previousmanager) createnewmanagerxls() savelinetoxls()

I’ll try to explain differently. I have a large set of data from SQL, that looks something like this:

UserID Access Manager
ajones Admin Joe Smith
bjones Admin Joe Smith
cjones Admin Joe Smith
djones Admin Joe Smith
asdfg Standard Jane Doe
asasf Standard Jane Doe
wdxd Standard Jane Doe
zcsqw Standard Jane Doe

I need to create separate XLSX reports for every manager. In the above example, Joe Smith would have his own report showing only the first 4 rows. Jane Doe would have her own report, showing only the last 4 rows.

What I do understand, is that line 69 works to iterate through the list of managers, creating a separate XLSX file with each manager’s name after the filepath, like “Access Review - Joe Smith.xlsx”, “Access Review - Jane Doe.xlsx”

What I don’t understand is how to use that same manager’s name to filter the results on line 52, to only write the data for manager Joe Smith on that XLSX file, next time through only write the data for manager Jane Doe, etc.

Max, I’m not sure why I would use a temp variable, since $_.“Manager Name” is currently working to create separate filenames for each manager (line 69). I don’t need SQL to correlate the data, I can join the data in SQL all day long. I’m trying to get PowerShell to separate the 160k total rows of data into separate blocks of data for each manager.

Solved!

I figured out that I had to set the position of $datatable2.“Manager Name”[$y] using a number I could increment through 0,1,2,3 etc. I also moved the Where method in front of the ForEach method, and it worked.

Here’s the corrected code if anyone is interested:

#server and db variables 
$serverName = "ServerName"; 
$databaseName = "Database"; 
#the save location for the new Excel file
$filepath = "C:\users\desktop\reviews\Access Review - " 
       
#create two Datasets to store the DataTables for Access and Manager  
$accessDataSet = new-object "System.Data.DataSet" "Access"
$managerDataSet = new-object "System.Data.DataSet" "Manager"
    
#create a Connection to the SQL Server database
$cn = new-object System.Data.SqlClient.SqlConnection "server=$serverName;database=$databaseName;Integrated Security=sspi"
$queryAccess = "SELECT * FROM AccessReviewTable ORDER BY Manager, Associate, LdapID, [Order], [Application], Permission_Code;"
$queryManager = "SELECT [Manager Name] FROM ManagerTable ORDER BY [Manager Name];"
#Create a SQL Data Adapter to place the access data into the first DataSet
$dataAdapter1 = new-object "System.Data.SqlClient.SqlDataAdapter" ($queryAccess, $cn) 
    $dataAdapter1.Fill($accessDataSet) | Out-Null
#Create a SQL Data Adapter to place the manager data into the second DataSet
$dataAdapter2 = new-object "System.Data.SqlClient.SqlDataAdapter" ($queryManager, $cn) 
    $dataAdapter2.Fill($managerDataSet) | Out-Null
#close the connection 
$cn.Close()
      
$dataTable1 = new-object "System.Data.DataTable" "Access"
$dataTable1 = $accessDataSet.Tables[0]
$dataTable2 = new-object "System.Data.DataTable" "Manager"
$dataTable2 = $managerDataSet.Tables[0]
$y=0    
    $dataTable2 | ForEach-Object{
        #create excel object
        $excel = New-Object -ComObject Excel.Application
        $excel.visible = $True
        $workbook = $excel.Workbooks.add()
        #create and name worksheet 
        $sheet1 = $workbook.worksheets.Item(1)
        $sheet1.name = "Access Review"
        #Add the Row Header
        $sheet1.Cells.Item(1,1) = 'LdapID'
        $sheet1.Cells.Item(1,2) = 'Associate'
        $sheet1.Cells.Item(1,3) = 'Platform'
        $sheet1.Cells.Item(1,4) = 'Application'
        $sheet1.Cells.Item(1,5) = 'Permission_Code'
        $sheet1.Cells.Item(1,6) = 'Permission_Desc'
        $sheet1.Cells.Item(1,7) = 'Read'
        $sheet1.Cells.Item(1,8) = 'Add'
        $sheet1.Cells.Item(1,9) = 'Update'
        $sheet1.Cells.Item(1,10) = 'Delete'
        $sheet1.Cells.Item(1,11) = 'Manager'
        $sheet1.Cells.Item(1,12) = 'Authorized Y/N'
            
            $x = 2
            $dataTable1.Where{$_.Manager -eq $dataTable2."Manager Name"[$y]} | ForEach-Object {
                $sheet1.cells.item($x, 1) =  $_.LdapID
                $sheet1.cells.item($x, 2) =  $_.Associate
                $sheet1.cells.item($x, 3) =  $_.Platform
                $sheet1.cells.item($x, 4) =  $_.Application
                $sheet1.cells.item($x, 5) =  $_.Permission_Code
                $sheet1.cells.item($x, 6) =  $_.Permission_Desc
                $sheet1.cells.item($x, 7) =  $_.Read
                $sheet1.cells.item($x, 8) =  $_.Add
                $sheet1.cells.item($x, 9) =  $_.Update
                $sheet1.cells.item($x, 10) =  $_.Delete
                $sheet1.cells.item($x, 11) =  $_.Manager
                $sheet1.cells.item($x, 12) =  $_.Authorized
                $range1 = $sheet1.UsedRange
                $range1.EntireColumn.AutoFit()
                $x++
            }
            $y++
        $excel.ActiveWorkbook.SaveAs("$filepath" + $_."Manager Name" + ".xlsx")
        $excel.quit()
    }

Thank you, Max and Sam for thinking through this with me.

so you did almost as I say: you use temp variable for "other $" but in different form
but
instead of $dataTable1.Where{$
.Manager -eq $dataTable2.“Manager Name”[$y]} | ForEach-Object {
you can write

$dataTable2 | ForEach-Object{
$currentmanager = $_ # save current iterator from outer cycle to temp variable
#create excel object
# ...
# ...
# ...
$dataTable1.Where{$_.Manager -eq $currentmanager."Manager Name"} | ForEach-Object {
# ...

and achieve the same results with lesser overhead and faster speed
for 10 managers you cannot see the delay, but for 1000…

what you do now: on every $datatable2’s item you create temporary array with “Manager name” in it and get $y’th value
which already equal current item in outer cycle. but you can’t access it because you have inner cycle (foreach in first code version and .where in second)

Thanks for the suggestion, Max! I agree that only 10 managers was no big deal, but switching to all 400 managers had some serious performance problems. It would have taken 20 hours to run 400 reports. I decided to make some changes and begin with creating the Manager’s datatable, then within the first ForEach, I go back to SQL and run the query for each manager name instead of using PowerShell’s Where method. The results of each query goes into the second ForEach loop. This took the 20 hour script down to 2 hours. Removing the $excel.visible = $True took it down to 1 hour & 20 minutes. Then I added your suggestion by creating that temp variable and reduced the total script run time down to 1 hour for all 400 reports. The rest of the performance problem is likely in SQL where each query can take 8-10 seconds. Once I fix the SQL query down to 1 second or less, I should be able to run all reports in 5-10 minutes, which would be just fine for my purpose.

I think if you switch from launching excel to export-csv it can be even better (if you can accept report in csv form)

Update: I’d like to only allow recipients to edit the last column of the xlsx report. I tried the following code:

$excel.Worksheets("Access Review").Protect = $True

This throws an error but it still works to protect the entire sheet. When I tried variations of protecting only a range of cells or columns, nothing worked. Any suggestions?

I don’t need to password-protect the sheet, I just want to discourage people from modifying any cell except the last column.

Almost found the solution. Since cells are locked by default, I needed to unlock the column and protect the sheet:

$excel.Worksheets("Access Review").Columns("M").Locked = $false
$excel.Worksheets("Access Review").Protect = $true  #this works, but throws "Error while invoking Protect. Could not find member."

Unlocking the last column works, but now the AutoFilter function for each column is locked. I need to allow users to AutoFilter the columns.

And it’d be nice to fix the script so it doesn’t show, “Error while invoking Protect. Could not find member.” But the script works regardless of the error. Any insight is much appreciated.

I found the answer. After unlocking the columns or cells, use the protect method below:

$excel.Worksheets("Access Review").Columns("M").Locked = $false
$excel.Worksheets("Access Review").Protect('',0,1,0,0,0,0,0,0,0,0,0,0,0,1,1) #1=true, 0=false, ''=noPwd

Short description of the protect parameters:
Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

Additional description on the protect method: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-protect-method-excel?f=255&MSPPError=-2147217396