I’m working on a script that will:
- Query a SQL table that shows what people have access to, and who they report to (total result 160,000 rows).
- Query a SQL table that shows all the distinct manager names (total result 400 rows).
- For each manager, create a separate XLSX file.
- Add the header row to each XLSX file.
- Place all results of query #1 into the XLSX file, but only where the manager name in Query 1 matches the first manager name.
- Iterate through the list of managers and repeat step #5 for manager #2, manager #3, etc.
- 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() }