CSV - How to Reference the Cell Above In a Column

This topic contains 7 replies, has 5 voices, and was last updated by  Max Kozlov 10 months, 1 week ago.

  • Author
    Posts
  • #59419

    Colter
    Participant

    Hi Folks,

    So I have a .csv file where the data is displayed like so:

    User,Role
    user1,view
    ,add
    user2,view
    ,edit
    user3,view
    ,add
    ,edit

    I need to fill in the user column so there is a 1 to 1 for both columns, like so:

    User,Role
    user1,view
    user1,add
    user2,view
    user2,edit
    user3,view
    user3,add
    user3,edit

    Here is what I have so far, but I don't know how exactly to reference the row above if the cell is open. Right now I'm just referecning the first cell in the column which isn't what I need.

    $Data = Import-Csv 'C:\test1.csv'
    
    $DataSelect = $data | Select-Object -ExpandProperty User
    
    foreach ($User in $Role) {
        $person= $User.User
    $User.Role= $User.Role
    $User.User= $User.User
        if (!$person) {
    $User.User= $dataselect[0]
        }
    }
    $Data | export-csv -NoTypeInformation 'C:\test2.csv'
    

    Any ideas?

  • #59422

    Olaf Soyk
    Participant

    You could try it this way:

    $Data = Import-Csv -Path 'C:\test1.csv'
    $Output = 'C:\test2.csv'
    foreach ($Row in $Data) {
        If($Row.User){
            $CurrentUser = $Row.User
        }
        Else{
            $Row.User = $CurrentUser
        }
        $Row | Export-Csv -Path $Output -Append -NoTypeInformation
    }
    
  • #59427

    Monte Hazboun
    Participant

    Have you tried using the indexof method? You can use indexof to get the index of the current element. Subtract one to get the previous element.

    $data = Import-CSV $path
    Foreach ($element in $data) {
      $index = $data.Indexof($element)
      $previous = $data[$index-1]
      #rest of your code here  
    }
    
    • #59430

      Olaf Soyk
      Participant

      Hmmm ... and what if you have 2 rows right next to each other with a missing user?

    • #59434

      Monte Hazboun
      Participant

      If he's updating the $data variable then the previous element will always have the user column filled in. Your way was more elegant, but this works all the same – try it 🙂

      $data = Import-Csv $path
      Foreach ($element in $data) {
        $index = $data.Indexof($element)
        $previous = $data[$index-1]
        if (!($element.User)) {
          $element.user = $previous.user
        }
        $element
      }
      
    • #59865

      Max Kozlov
      Participant

      I depends 😉

       D:\> #prepare data for sample
      >>> $path = 'D:\111.csv'
      >>> # 10000 users with random names
      >>> $data = {}.Invoke()
      >>> 1..10000 | Foreach-Object {
      >>> ^I$data.Add([PSCustomObject]@{User=(-join [char[]](65..90 | Get-Random -Count 10)); Role='Add'})
      >>> ^I$data.Add([PSCustomObject]@{User=''; Role='edit'})
      >>> }
      >>> # Export to csv
      >>> $data | Export-Csv -Delimiter ';' -Path $path
      
      #Olaf's code
      >>> $code1 = {
      >>>  foreach ($Row in $Data) {
      >>>    If($Row.User){
      >>>      $CurrentUser = $Row.User
      >>>    }
      >>>    Else{
      >>>      $Row.User = $CurrentUser
      >>>    }
      >>>  }
      >>> }
      #Monty's code
      >>> $code2 = {
      >>>  Foreach ($element in $data) {
      >>>   $index = $data.Indexof($element)
      >>>   $previous = $data[$index-1]
      >>>   if (!($element.User)) {
      >>>     $element.User = $previous.User
      >>>   }
      >>>   #$element
      >>>  }
      >>> }
      
      #Measurement
      >>> $Data = Import-CSV -Delimiter ';' -Path $path
      >>> $Data | Select -First 4 | Format-Table
      >>> Measure-Command $code1
      >>> $Data | Select -First 4 | Format-Table
      >>> $Data = Import-CSV -Delimiter ';' -Path $path
      >>> $Data | Select -First 4 | Format-Table
      >>> Measure-Command $code2
      >>> $Data | Select -First 4 | Format-Table
      >>>
      
      User       Role
      ----       ----
      AFRKVNYBIU Add
                 edit
      EYLIHQWJRU Add
                 edit
      
      Days              : 0
      Hours             : 0
      Minutes           : 0
      Seconds           : 0
      Milliseconds      : 78
      Ticks             : 785095
      TotalDays         : 9,08674768518518E-07
      TotalHours        : 2,18081944444444E-05
      TotalMinutes      : 0,00130849166666667
      TotalSeconds      : 0,0785095
      TotalMilliseconds : 78,5095
      
      User       Role
      ----       ----
      AFRKVNYBIU Add
      AFRKVNYBIU edit
      EYLIHQWJRU Add
      EYLIHQWJRU edit
      
      User       Role
      ----       ----
      AFRKVNYBIU Add
                 edit
      EYLIHQWJRU Add
                 edit
      
      Days              : 0
      Hours             : 0
      Minutes           : 0
      Seconds           : 3
      Milliseconds      : 700
      Ticks             : 37003530
      TotalDays         : 4,28281597222222E-05
      TotalHours        : 0,00102787583333333
      TotalMinutes      : 0,06167255
      TotalSeconds      : 3,700353
      TotalMilliseconds : 3700,353
      
      User       Role
      ----       ----
      AFRKVNYBIU Add
      AFRKVNYBIU edit
      EYLIHQWJRU Add
      EYLIHQWJRU edit
      

      On 10000 users your code is 47 times slower 🙂

      this is because you use IndexOf() and search all array on each element !
      and you code fall into error if the first element contain no user
      it not elegant but using simple index variable speed up this code variant alot

       D:\> $code3 = {
      >>>  $index = -1
      >>>  Foreach ($element in $data) {
      >>>   if (!($element.User) -and ($index -ge 0)) {
      >>>     $element.User = $data[$index].User
      >>>   }
      >>>   $index++
      >>>   #$element
      >>>  }
      >>> }
      >>> $Data = Import-CSV -Delimiter ';' -Path $path
      >>> $Data | Select -First 4 | Format-Table
      >>> Measure-Command $code3
      >>> $Data | Select -First 4 | Format-Table
      >>>
      
      User       Role
      ----       ----
      AFRKVNYBIU Add
                 edit
      EYLIHQWJRU Add
                 edit
      
      Days              : 0
      Hours             : 0
      Minutes           : 0
      Seconds           : 0
      Milliseconds      : 96
      Ticks             : 969243
      TotalDays         : 1,12180902777778E-06
      TotalHours        : 2,69234166666667E-05
      TotalMinutes      : 0,001615405
      TotalSeconds      : 0,0969243
      TotalMilliseconds : 96,9243
      
      User       Role
      ----       ----
      AFRKVNYBIU Add
      AFRKVNYBIU edit
      EYLIHQWJRU Add
      EYLIHQWJRU edit
      
  • #59433

    Rob Simmers
    Participant

    The only thing I would do a bit differently than Olaf is rather than building a CSV by appending, you're updating the $Data object with data, so you can just do the loop and then export the updated $data object:

    $Data = Import-Csv -Path 'C:\Users\Rob\Desktop\Archive\test.csv'
    
    foreach ($Row in $Data) {
        If($Row.User){
            $CurrentUser = $Row.User
        }
        Else{
            $Row.User = $CurrentUser
        }
    }
    
    $data | Export-CSV -Path 'C:\Users\Rob\Desktop\Archive\new.csv' -NoTypeInformation
    
  • #59800

    Olaf Soyk
    Participant

    I'm curious. Could you complete the task?

You must be logged in to reply to this topic.