CODEWITHSUNDEEP
powershellcsv
Roi Samagoue
Roi Samagoue

Reputation: 45

Add Unique ID to CSV with Powershell

I have a CSV with Server names in 1 column and Software running on the Server in another. Because the server is repeated multiple times (due to the distinct Software associated with it), I can't just do a count++ for my ID, so basically, while automatically assigning IDs, the ID has to change only when Server name changes, how can I accomplish this?

Desired output

╔════╦═════════╦═══════════╗
║ ID ║ Server  ║ Software  ║
╠════╬═════════╬═══════════╣
║  1 ║ Server1 ║ Software1 ║
║  1 ║ Server1 ║ Software2 ║
║  2 ║ Server2 ║ Software1 ║
║  3 ║ Server3 ║ Software1 ║
║  3 ║ Server3 ║ Software2 ║
╚════╩═════════╩═══════════╝

Upvotes: 0

Views: 508

Answers (2)

JND
JND

Reputation: 1

Another solution :

#Generate the array for the example
$ServerArray = @()
$ServerArray += [PsCustomObject][Ordered]@{Server = "Server1";Software = "Software1"}
$ServerArray += [PsCustomObject][Ordered]@{Server = "Server1";Software = "Software2"}
$ServerArray += [PsCustomObject][Ordered]@{Server = "Server2";Software = "Software1"}
$ServerArray += [PsCustomObject][Ordered]@{Server = "Server3";Software = "Software1"}
$ServerArray += [PsCustomObject][Ordered]@{Server = "Server3";Software = "Software2"}

#Property to be used for incrementing the Id
$Property = "Server"
#Intiate the Id and the Value, Global variable are required for being able to keep the value of the previous iteration
$Global:Id=0
$Global:Value=$null

#Define the expression to be used for the additional Id Property
$ExpId = @{E={if($Global:Value -ne $_."$($Property)"){$Global:Value=$_."$($Property)";$Global:Id++};$Global:Id};L="Id"}

#Add an Id property for each item
$ServerArray = $ServerArray | Sort-Object Server | select $ExpId,*

#Display Result
$ServerArray | ft *

#Export result with Id in csv file
$CsvFilePath = Join-Path $Env:TEMP "tempfile.csv"
$ServerArray | Export-Csv -Path $CsvFilePath -NoTypeInformation

Upvotes: 0

Theo
Theo

Reputation: 61178

You can use Group-Object for this like below:

$data = Import-Csv -Path 'D:\Test\input.csv'  # enter the path to your input csv file here
$id   = 1  # initial server ID

$result = $data | Group-Object -Property Server | ForEach-Object {
    # get the current id value and increment $id for the next server group
    $svrId = $id++
    foreach ($item in $_.Group) {
        # output an object with ID property included
        [PsCustomObject]@{
            ID = $svrId
            Server = $item.Server
            Software = $item.Software
        }
    }
}

# output on screen
$result

# save as CSV file
$result | Export-Csv -Path 'D:\Test\output.csv' -NoTypeInformation

Upvotes: 1

Related Questions