Export CSV PowerShell : I have to export my result in an Excel table but everything goes in one cell. I would like to have a table with columns and rows.


The current excel file (Excel table):
<pre>
Column1	     Column2	Column3	    Column4
infra-12   	infra-88	infra-52	infra-55
infra-2	    infra-3	    infra-2	    infra-12
infra-8	    infra-71	infra-45	infra-71
infra-45	infra-2	    infra-3	    infra-52
infra-3  	infra-27	infra-99	infra-2
</pre>

Output without the export (in PowerShell ISE):

<pre>
Column1  Column2  Column3  Column4 
-------  -------  -------  ------- 
infra-12 infra-88 infra-52 infra-55
infra-2  infra-71 infra-99 infra-71
infra-8  infra-27          infra-52
infra-45                           
infra-3 
</pre>



Result when I export in an Excel table (this is in the table and everything is in one cell):
<pre>
Column1,"Column2","Column3","Column4"
infra-12,"infra-88","infra-52","infra-55"
infra-2,"infra-71","infra-99","infra-71"
infra-8,"infra-27",,"infra-52"
infra-45,,,
infra-3,,,
</pre>


Code:

    $csv = Import-Csv .\test1.csv -Delimiter ';'
    
    $ref = [ordered]@{}
    $columns = foreach ($i in 0..7) { ,[Collections.ArrayList]@() }
    
    foreach ($row in $csv) {
        $value = $row.Column1
        $ref[$value] = $true
        $columns[0].add($value) >$null
    }
    
    foreach ($row in $csv) {
        $i = 1
        foreach ($col in 'Column2', 'Column3', 'Column4') {
            $value = $row.$col
            if (!$ref[$value]) {
                $columns[$i].add($value) >$null
            }
            $i++
        }
    }
    
    $maxLine = ($columns | select -expand Count | measure -Maximum).Maximum - 1
    $csv = foreach ($i in 0..$maxLine) {
        [PSCustomObject]@{
            Column1 = $columns[0][$i]
            Column2 = $columns[1][$i]
            Column3 = $columns[2][$i]
            Column4 = $columns[3][$i]
        }
    }
    
    
    $csv | Export-CSV -Path ".\test1.csv" -NoTypeInformation

The code allow to remove cells which are matching (same values) and get display the others.
