Using Awesome Export-Excel Cmdlet (Part 4)

This is part 4 of our mini-series about the awesome and free “ImportExcel” PowerShell module by Doug Finke. Make sure you install the module before you play with this tip:

 
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force
 

In part 3, we looked at misinterpreted data due to automatic formula conversion, and examined your options to post-process individual cell formats. Let’s examine issues caused by arrays.

Here is some code that reproduces the problem. In our example, it is event log data from the last 10 system events, which happens to contain an array (ReplacementStrings), and displays completely incorrectly:

# get some raw data that contains arrays
$rawData = Get-EventLog -LogName System -Newest 10 | 
            Select-Object -Property TimeWritten, ReplacementStrings, InstanceId


# create this Excel file
$Path = "$env:temp\report.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'
$rawData |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -Show

When Excel opens, you can see that the column “ReplacementStrings” just shows the data type and not the actual data (“System.String[]”). This is the normal Excel behavior when it encounters arrays, so there is nothing Export-Excel can do about this.

Instead, it is your responsibility to convert the array to a string before you pipe it to Export-Excel – which is pretty easy using the -join operator:

# get some raw data that contains arrays
$rawData = Get-EventLog -LogName System -Newest 10 | 
            Select-Object -Property TimeWritten, ReplacementStrings, InstanceId


# create this Excel file
$Path = "$env:temp\report.xlsx"
# make sure the file is deleted so we have no
# effects from previous data still present in the
# file. This requires that the file is not still
# open and locked in Excel:
$exists = Test-Path -Path $Path
if ($exists) { Remove-Item -Path $Path}

$sheetName = 'Testdata'
$rawData |
  ForEach-Object {
    # convert column "ReplacementStrings" from array to string
    $_.ReplacementStrings = $_.ReplacementStrings -join ','
    # return the changed object
    $_
  } |
  Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -Show

Once you do this, even properties containing arrays will display correctly in Excel. -join works for any object. Simply make sure you specify the delimiter you want to use to separate array elements.

ReTweet this Tip!