CSV - How to Reference the Cell Above In a Column

PowerShell Help
1

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?

2

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

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

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

5

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
6

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 :slight_smile:

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

I’m curious. Could you complete the task?

8

I depends :wink:

 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 :slight_smile:

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