Add a cell in a CSV file with PowerShell

PowerShell Help
1

I have to add a cell and move the cells of the second part of the array in my Excel table (CSV), this is an example of what i need to get :

Not in col1  Column2 Column3 Column4      Not in col2   Column1 Column3 Column4

                58     85     25                          75      85      25                                      
                10     10     50                          40      40      50

I would like to add the “Not in col” cell like this, before the columns and move the second part at the right like in the example up there. I will also remove the column reference, this is not important in this case.

This is the current result:

Column1 Column2 Column3 Column4
75        58      85     25
88        10      10     50
40          
16          
75        88      85     25
40        16      40     50
          58        
      10

The 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 “.\test3.csv” -NoTypeInformation -Delimiter “;”

$csv = Import-Csv .\test1.csv -Delimiter ‘;’

$ref = [ordered]@{}
$columns = foreach ($i in 0…7) { ,[Collections.ArrayList]@() }

foreach ($row in $csv) {
$value = $row.Column2
$ref[$value] = $true
$columns[0].add($value) >$null
}

foreach ($row in $csv) {
$i = 1
foreach ($col in ‘Column1’, ‘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[1][$i]
Column2 = $columns[0][$i]
Column3 = $columns[2][$i]
Column4 = $columns[3][$i]
}
}

$csv | Export-CSV -Path “.\test3.csv” -NoTypeInformation -Delimiter “;” -Append

2

I have to add a cell and move the cells of the second part of the array in my Excel table (CSV), this is an example of what i need to get :

Not in col1  Column2 Column3 Column4      Not in col2   Column1 Column3 Column4

                58     85     25                          75      85      25                                      
                10     10     50                          40      40      50

I would like to add the “Not in col” cell like this, before the columns and move the second part at the right like in the example up there. I will also remove the column reference, this is not important in this case.

This is the current result:

Column1 Column2 Column3 Column4
75        58      85     25
88        10      10     50
40          
16          
75        88      85     25
40        16      40     50
          58        
          10

In this context I import then compare then export the result and I need to display this cell to complete this.
The 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 ".\test3.csv" -NoTypeInformation -Delimiter ";"



$csv = Import-Csv .\test1.csv -Delimiter ';'

$ref = [ordered]@{}
$columns = foreach ($i in 0..7) { ,[Collections.ArrayList]@() }

foreach ($row in $csv) {
    $value = $row.Column2
    $ref[$value] = $true
    $columns[0].add($value) >$null
}

foreach ($row in $csv) {
    $i = 1
    foreach ($col in 'Column1', '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[1][$i]
        Column2 = $columns[0][$i]
        Column3 = $columns[2][$i]
        Column4 = $columns[3][$i]
    }
}


$csv | Export-CSV   -Path ".\test3.csv" -NoTypeInformation -Delimiter ";" -Append
3

From a quick review of your code, it seems like you would just add the property to your PSCustomObject, unless I’m not completely grasping your goal here.

At line 26

    [PSCustomObject]@{
        "Not in col1" = ""
        Column1 = $columns[0][$i]
        Column2 = $columns[1][$i]
        Column3 = $columns[2][$i]
        Column4 = $columns[3][$i]
    }

and At line 63

    [PSCustomObject]@{
        "Not in col2" = ""
        Column1 = $columns[0][$i]
        Column2 = $columns[1][$i]
        Column3 = $columns[2][$i]
        Column4 = $columns[3][$i]
    }