Today we take just a moment and show you mapping from a CSV to XLSX file. Let’s start.
A Fantastic Module for Excel Work: https://github.com/dfinke/ImportExcel
It was asked, “how do you map fields from a CSV file to an XLSX file”? to make matters worse can we do it to a file that does not already exist? I modified the original script to work as the script was not populating the results from the CSV. Here is the final result.
#Variable assignation $dateString = (Get-Date -Format yyyyMMdd) $userFunds = "XXXX" $original = "c:\scripts\XXXXXX\ActiveDirectory_$dateString.csv" $path = "c:\scripts\XXXXXX\Test\DeltaFeed_$dateString.xlsx" $users = Import-Csv -Path $original $Excel = New-Object -ComObject excel.application $Excel.visible = $false $workbook = $Excel.workbooks.add() $excel.cells.item(1,1) = "Funds" $excel.cells.item(1,2) = "FirstName" $excel.cells.item(1,3) = "LastName" $excel.cells.item(1,4) = "Employee Number" $excel.cells.item(1,5) = "Email" $excel.cells.item(1,5) = "Action" $i=2 foreach($user in $users) { $action = if ($_.UserStatus -eq 'A') {'A'} elseif ($_.UserStatus -eq 'T') {'D'} $excel.cells.item($i,1) = $userFunds $excel.cells.item($i,2) = $user.PreferredName $excel.cells.item($i,3) = $user.Last $excel.cells.item($i,4) = $user.EmployeeID $excel.cells.item($i,5) = $user.Email $excel.cells.item($i,6) = $user.UserStatus $excel.cells.item($i,6) = $action $i++ } #end foreach user $workbook.saveas($path) $Excel.Quit() Remove-Variable -Name excel [gc]::collect() [gc]::WaitForPendingFinalizers()
Want to read more? Checkout our post on Backup GPO Settings.