CSV - How to Reference the Cell Above In a Column

This topic contains 7 replies, has 5 voices, and was last updated by Profile photo of Max Kozlov Max Kozlov 5 months, 2 weeks ago.

  • Author
    Posts
  • #59419
    Profile photo of Colter
    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
    Profile photo of Olaf Soyk
    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
    Profile photo of Monte Hazboun
    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
      Profile photo of Olaf Soyk
      Olaf Soyk
      Participant

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

    • #59434
      Profile photo of Monte Hazboun
      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
      Profile photo of Max Kozlov
      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
    Profile photo of Rob Simmers
    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
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

    I'm curious. Could you complete the task?

You must be logged in to reply to this topic.