none
Add a cell in a CSV file and move some cells with PowerShell RRS feed

  • Question

  • 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 present in col1   Column2 Column3 Column4      Not present in col2   Column1 Column3 Column4

                                            58          85           25                                                 75           85           25

                                           10          10            50                                                 40           40           50

    I would like to add the "Not present in col"  like this before the columns with the values.

    My current exported file :

    This the current result of my script and I would like to get the result up there. I know how to remove the column reference (the longest one).

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


    My imported file:

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

    The following code allows to remove the same values (matching cells) in the different columns compared to the column reference:

    $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



    Wednesday, June 28, 2017 6:30 AM

All replies

  • The code you posted here is everything on one line. That cannot work this way and it's quite impossible to read and to understand. Could correct the code and maybe explain more detailed what you try to do? With the given explanation I did not get it.

    Grüße - Best regards

    PS:> (79,108,97,102|%{[char]$_})-join''

    Monday, June 26, 2017 8:30 PM
  • $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 ";" There is two parts but the other one is the same except the column has the ID 0

    Please do not post code that is unreadable.  What you have posted cannot be used or read by anyone without a lot of pointless work.

    If you have something to add please post it correctly.


    \_(ツ)_/

    Tuesday, June 27, 2017 6:27 AM
  • I have one file which is imported then I run the script to export in another file this is written on the top my output is under the code and I want to export it in the other file.





    • Merged by jrv Tuesday, June 27, 2017 7:57 AM DUPLICATE
    • Edited by PowerShell nooby Tuesday, June 27, 2017 8:15 AM
    Tuesday, June 27, 2017 7:52 AM
  • Are you asking how to combine two CSV files into one?

    Your question is very hard to understand.  Just state what you are trying to do in plain English.

    It sounds like you want to concatenate the files and then it sounds like you want to join the files.  You have to be clear.


    \_(ツ)_/


    • Edited by jrv Tuesday, June 27, 2017 8:04 AM
    Tuesday, June 27, 2017 8:00 AM
  • I have one file which is imported then I run the script to export in another file this is written on the top my output is under the code and I want to export it in the other file.





    You are still not saying what the purpose is.  Why do you have to import and output again?  What is it that you are trying to do?


    \_(ツ)_/

    Tuesday, June 27, 2017 8:18 AM
  • I have to do this because I must compare the columns then update the result each time I use this code so I prefer to export it then use an updated file to work. If you prefer make a loop of import export with the output
    Tuesday, June 27, 2017 8:21 AM
  • Your explanation is still way too vague.  What do you have to update?  The code you posted is useless.  Whatever you are trying to do is impossible to determine.


    \_(ツ)_/

    Tuesday, June 27, 2017 8:25 AM
  • I'm gonna explain step by step to be sure.

    First: I import my csv file 

    Second: I create the columns and compare the cells and remove the cells which have the same values.

    Third: I return and export the output in another file 

    Fourth: I use the exported result in the file to import it in another script then export it again and again to update the situation.

    Objective: Update my datas and see what is not in the column reference.

    This is only a test file, IRL I work with four columns of 220 PC and I must find the computers which aren't in the AD WSUS ESET or GLPI. If the compter isn't in the column reference, it's display in the other columns. 



    Tuesday, June 27, 2017 8:35 AM
  • You cannot remove "cells" from a CSV.

    Don't explain what you are coding since you already know the code doesn't work.  Explain what you are starting with and what needs to happen to it to get the desired output.  Do not mention code or how you want to code it.  Just say what you need to do in plain English.


    \_(ツ)_/

    Tuesday, June 27, 2017 8:38 AM
  • I want to export the output in an Excel table but when I export, only the last result is displayed, I need to have all the results like I show in my question. What I'vs explained before is my project. The problem is this:

    Only the second result is displayed in the file:

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

    I need to get this in the SAME table:

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

    16


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

    Tuesday, June 27, 2017 8:44 AM
  • To combine two files:

    Import-Csv file1 | Export-Csv output.csv
    Import-Csv file2.Csv | Export-Csv -Append

    This will combine the two files with no loss.


    \_(ツ)_/

    Tuesday, June 27, 2017 8:58 AM
  • Thanks a lot! Is possible to get the result with the others columns on the right ?

    like this :

    Colum Colum2 Colum3 Colum4          Column1 Column2 Column3 Column4
         


    Tuesday, June 27, 2017 9:03 AM
  • Repeatedly posting the same question will not get you an answer.  If you cannot accuratekly describe what you are trying to do then asking the exact same question is not going to get you an answer.

    DUPLICATE: https://social.technet.microsoft.com/Forums/windowsserver/en-US/6c176b0e-f130-48d4-bb35-153af1ededac/export-an-output-in-an-excel-table?forum=winserverpowershell#5161fd77-33c9-4a73-b8ea-2c13f225ba35


    \_(ツ)_/

    • Proposed as answer by Hello_2018 Friday, July 7, 2017 2:44 AM
    Wednesday, June 28, 2017 6:41 AM
  • Thanks a lot! Is possible to get the result with the others columns on the right ?

    like this :

    Colum Colum2 Colum3 Colum4          Column1 Column2 Column3 Column4
         


    You would have to merge the two files.  What criteria tells you which lines should be joined?  A join requires a key.


    \_(ツ)_/

    Wednesday, June 28, 2017 6:46 AM