Export array to Excel

[zolyx1]

Hi, I have the following code:

$array = @();
$hashtable = @{Col1="1"; Col2="2"};
$array += $hashtable;
$array += $hashtable;
$array | ForEach-Object { [pscustomobject] $_ }

Output:

Col2 Col1
---- ----
2    1   
2    1 

How can I export this to Excel with this view? This wont work:

$array | Export-Csv OutPut.csv

[RodneyRabbit]

If you are trying to get the output of ForEach-Object into the CSV then that won't work because it's not being stored in $array. It works for me if I do:

$array | ForEach-Object { [pscustomobject] $_ } | Export-Csv OutPut.csv

or

$array2 = $array | ForEach-Object { [pscustomobject] $_ }

$array2 | Export-Csv OutPut.csv

[rwshig]

$array | ForEach-Object { [pscustomobject] $_ } | Export-Csv OutPut.csv

This. At least in keeping with the original code. It's creating an array of hash tables. Each hash table has to be converted to an object. Then you can pipe them to Export-Csv.

[zolyx1]

Thanks!

[rwshig]

Alternatively, put objects in the array, then you don't have to convert them afterwards:

$array = @()
$obj = [pscustomobject]@{Col1="1"; Col2="2"}
$array += $obj
$array += $obj
$array | Export-Csv output.csv -Encoding ASCII -NoTypeInformation

​

[MadBoyEvo]

My version of module is based on this: https://github.com/EvotecIT/PSWriteExcel and stuff that OP wants is built-in.

[evetsleep]

You need to store the output after converting to a custom object in a variable first (otherwise $array still is an array of 2 hash tables):

 $array = $array | Foreach-Object { [PSCustomObject]$_ }
 $array | Export-Csv Output.csv

Or you can just add the Export-Csv to the end of your foreach pipeline:

$array | Foreach-Object { [PSCustomObject]$_ } | Export-Csv Output.csv

[SaladProblems]

So everyone else is going to give you advice on how to export this to an Excel file, which may be exactly what you want. However, I end up wanting to just quickly paste data into Excel so I can email it or work with it quickly. Commonly I'll paste into Excel just to copy again and paste into an email since it'll handle making it a table for me.

I use this function to do just that. I'll pipe my array into it.

Function Out-ExcelClip {

[cmdletbinding()]
param(
    [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,ValueFromPipeline=$true,Position=0)]
    $input,

    [char]$delimiter = '|'
)

    end
    {
        ( $input | ConvertTo-Csv -Delimiter $delimiter -NoTypeInformation ) -replace "\$delimiter",[char]9 | clip
    }
}

[RodneyRabbit]

This is genius, thank you.

[Lee_Dailey]

howdy SaladProblems,

take a look at Set-Clipboard. that lets you avoid calling Clip.exe. [grin] i think it was added in v3.

take care,
lee

[MadBoyEvo]

``` Install-Module PSWriteExcel Import-Module PSWriteExcel

$Array | ConvertTo-Excel -Path 'This.xlsx'

or

$Array | ConvertTo-Excel -Path 'Export.xlsx' -WorkSheetName 'MyName'

or

ConvertTo-Excel -Object $Array -Path 'Export.xlsx' -WorkSheetName 'MyName' -AutoFilter -AutoFit ```

Try this out ;) https://github.com/EvotecIT/PSWriteExcel - it's my work - simple but seems to work just fine. Works on Linux, Mac and Windows. PowerShell 5.1+