How to fix Cannot index into a null array in PowerShell

Question

I'm trying to convert my CSV file to Excel file with some Table format and style but I'm getting "Cannot index into a null array" for some reason. I'll be really appreciated if I can get any help or suggestion. Thanks

function Convert-to-Excel{
  $params = @{
        AutoSize      = $true
        TableStyle    = 'Medium6'
        BoldTopRow    = $true
        WorksheetName = 'Audit Log'
        PassThru      = $true
        Path          = "C:\AuditLogSearch\$((Get-Date).AddDays(-7).ToString('yyyy-MM-dd')) _ $(Get-Date -Format "yyyy-MM-dd") Audit-Log-Records11.xlsx"
    }

    $modifiedFile = Import-Csv "C:\AuditLogSearch\Modified-Audit-Log-Records.csv"
    $actionReference = Import-Csv "C:\AuditLogSearch\Reference\Action.csv"

    $xlsx = foreach ($u in $modifiedFile) {
        $u.User = (Get-AzureADUser -ObjectId $u.User).DisplayName
        New-Object PsObject -Property @{
            User              = $u.User
            "Search Criteria" = $u."Search Criteria"
            "Result Status"   = $u."Result Status"
            "Date & Time"     = $u."Date & Time"
            "Type of Action"  = if (($actionReference | where-object { $_.Name -eq $u."Type of Action" }).Value -ne $null) { ($actionReference | where-object { $_.Name -eq $u."Type of Action" }).Value }
            else { $u."Type of Action" }
        } | Export-Excel @params

        $ws = $xlsx.Workbook.Worksheets[$params.Worksheetname]
        $ws.View.ShowGridLines = $false # => This will hide the GridLines on your file
        Close-ExcelPackage $xlsx
    } 
}

@MathiasR.Jessen Hi, I updated the posted so pls check. $xlsx is defined for the foreach. — ziico
– ziico, Commented Dec 2, 2021 at 23:22
foreach doesn't return anything. What are you trying to set $xlsx to? — Jesse
– Jesse, Commented Dec 2, 2021 at 23:34
You should at the very least be setting it to a new instance of an excel object. — Jesse
– Jesse, Commented Dec 2, 2021 at 23:36
@Jesse can you pls show me how?. I'm been stuck with this error for the past few hrs :( — ziico
– ziico, Commented Dec 2, 2021 at 23:44

Santiago Squarzon · Accepted Answer · 2021-12-03 00:23:12Z

2

You're closing the Excel Package on the first iteration of your loop hence why when it goes to the next it's trying to do something like this:

$null[$null] # => InvalidOperation: Cannot index into a null array

Try modifying your function so it looks like this instead:

First, construct the object[]:

$result = foreach ($u in $modifiedFile) {
    $u.User = (Get-AzureADUser -ObjectId $u.User).DisplayName
    New-Object PsObject -Property @{
        User              = $u.User
        "Search Criteria" = $u."Search Criteria"
        "Result Status"   = $u."Result Status"
        "Date & Time"     = $u."Date & Time"
        "Type of Action"  = if (($actionReference.........
        else { $u."Type of Action" }
    }
}

Then export it to Excel:

$xlsx = $result | Export-Excel @params
$ws = $xlsx.Workbook.Worksheets[$params.Worksheetname]
$ws.View.ShowGridLines = $false # => This will hide the GridLines on your file
Close-ExcelPackage $xlsx

One thing to note, PassThru = $true on the $params means that instead of saving the Excel directly we want to save the object on a variable for "further manipulation" and by further manipulation what I mean is, in this case, hiding the GridLines of the worksheet ($ws.View.ShowGridLines = $false) and then closing the package (store it on the disk).

If you don't require to perform any modifications over the worksheet you can just remove the PassThru altogether and do:

$result | Export-Excel @params

Which will close the package and store the Excel on your disk.

edited Dec 3, 2021 at 0:23

answered Dec 2, 2021 at 23:53

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

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

2 Comments

Santiago Squarzon Over a year ago

@aaselab happy to help :) please see my last update, I feel responsible for pointing this out since I introduced you to the ImportExcel Module.

ziico Over a year ago

Thank you so much. :)

Collectives™ on Stack Overflow

How to fix Cannot index into a null array in PowerShell

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related