Add a cell in a CSV file with PowerShell

This topic contains 2 replies, has 2 voices, and was last updated by  Curtis Smith 3 months, 3 weeks ago.

  • Author
    Posts
  • #73826

    Suli
    Participant

    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

  • #73832

    Suli
    Participant

    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
  • #73856

    Curtis Smith
    Participant

    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]
        }

You must be logged in to reply to this topic.