Powershell : how to pass a hashtable as args for parameter -PivotData with Export-Excel command (ImportExcel Module v7.8.6)?

Question

I could manage to build a simple script that collects some system metrics from different computer as a scheduled task, and export them into CSV files (one per computer). I am now trying to complete that first script with a second-stage script that works on all CSV files, turning each one into an XLSX file with two sheets : "DATA" and "CHARTS", using the Export-Excel command. Then come troubles...

Within the "CHARTS" sheet in every XLSX file, the three first clomuns have to be "TIMESTAMP", "CPU" and "RAM", and the others must be "DISK-?1", "DISK-?2"... ("?" being put here for drive letter). As you may imagine, not all systems have the same number of drives/partitions, so not all CSV files have the same number of columns, nor all drives/partitions the same names. With the following code snippet, I found a rough way to dynamically determine the columns names to be considered for each file, columns names that will be passed as args to the -PivotData parameter of Export-Excel command:

# Get list of CSV files to be processed
$CSVList = Get-ChildItem -Path "\Collection\Folder" -Filter "*.csv"
$CSVList | ForEach-Object {
    # Import CSV file as in-memory object for processing
    $CurrentFile = (Get-Content -Path $_.FullName) | ConvertFrom-Csv
    # Get list of disks/partitions to be reported in XLSX file
    $DiskList = New-Object -TypeName System.Collections.ArrayList
    $DiskList = $CurrentFile[0].PSObject.Properties.Name | Select-String -Pattern "DISK-"
    # Define static args for -PivotData parameter
    $PivotDataArgs = [Ordered]@{
        "CPU" = "None"
        "RAM" = "None"
    }
    # Add dynamic args (DISK-?) according to $DiskList
    for ($i=0; $DiskList[$i] -ne $null; $i++) {
       $PivotDataArgs.Add("$($DiskList[$i])","None")
    }
    #
    # "Breakoint", to be explained later on...
    #
    # Export $CurrentFile to XLSX with selected columns
    # Code to be continued normally if "Breakpoint" solved
}

Basically, the Export-Excel documentation states this:

-PivotData <Object>
        In a PivotTable created from command line parameters,
        the fields to use in the table body are given as a Hash-table
        in the form ColumnName = Average\|Count\|Max\|Min\|Product\|None...

If I had to write an individual second-stage script for each CSV file (not having to build a dynamic list of args for -PivotData parameter), then the following code snippet would work perfectly, with a single chart on sheet "CHARTS", combining the metrics levels of CPU, RAM and the differents DISK-? all-in-one:

    # Import CSV file as in-memory object for processing
    $CurrentFile = (Get-Content -Path $_.FullName) | ConvertFrom-Csv
    # Export $CurrentFile to XLSX with selected columns
    $CurrentFile | Export-Excel -Path "\Results\Folder\$($_.BaseName).xlsx" `
        -WorksheetName "DATA" -AutoSize -AutoFilter `
        -IncludePivotTable -PivotTableName "CHARTS" `
        -PivotRows "TIMESTAMP" -PivotDataToColumn `
        -PivotData @{"CPU"="None";"RAM"="None";"DISK-C:"="None";"DISK-D:"="None"} `
        -IncludePivotChart -ChartType Line -Activate

Now time to explain the "Breakpoint", and this is where the troubles deepen...

At this time in code (Breakpoint), $PivotDataArgs has *.GetType().Name = [OrderedDictionary] and *.GetType().BaseType = [System.Object]. Because the documentation states that only a hashtable can be passed as argument to the -PivotData parameter, then I need to cast my ordered list in order to get an hashtable. So, by doing $PivotDataArgs = [hashtable]$PivotDataArgs, I can now have *.GetType().Name = [Hashtable] with *.GetType().BaseType = [System.Object]. But exporting my CSV with a parameter line like -PivotData @PivotDataArgs throws an error stating Missing an argument for parameter 'PivotData'. Specify a parameter of type 'System.Object' and try again..

Having this error, then an idea comes to me : making $PivotDataArgs a string that looks exactly like when written manually. So, back from my ordered dictionary, I transform it into an identical string with $PivotDataArgs = ($PivotDataArgs | ConvertTo-Json -Compress).Replace('":"','"="').Replace('","','";"'). Now $PivotDaraArgs is a sting almost identical to the one manually written, with only the '@' missing. as our parameter only accepts hashtable as args, I then tried to make my almost-identical-json string become a hashtable with $PivotDataArgs = $PivotDataArgs | ConvertFrom-Json -AsHashtable... but no, it didn't work neither. Trying with ConvertFrom-StringData? No way to make it work...

As a newbie in Powershell coding and automation, I tried everything that came to my mind, and I'm absolutely lost... your help and guidance would be more appreciated, please (Im dead if I don't get a solution asap)!

Answer 1

I see a mistake in your script, you typed '-PivotData @PivotDataArgs' instead of '-PivotData $PivotDataArgs' is this your problem?