Monday, November 28, 2022
HomePowershellPowerShell CSV Fast Hack – Redistributing Columns in CSV Information

PowerShell CSV Fast Hack – Redistributing Columns in CSV Information


I’m not an Excel guru, however I’m helpful with PowerShell, so I made a decision that I’d clear this up with PowerShell.

My authentic thought was this course of:

  1. Import the CSV file utilizing import-csv
  2. Have a foreach loop that checked out every entry within the CSV file, and created new entries as follows.

My code regarded like this:

$OriginalCSV = import-csv 'authentic.csv'
$NewCSV = @()
Foreach ($merchandise in $OriginalCSV) {
    $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise.Recipient}
    $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise.Recipient2}
    $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise.Recipient3}
    $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise.Recipient4}
    $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise.Recipient5}
    # ...
    $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise.Recipient23}
    $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise.Recipient24}
}
$NewCSV | Export-Csv -Path 'new.csv'

This code does what I wanted, however it’s removed from excellent. I’ve copy-pasted the identical code; it doesn’t look very re-usable.

How may I make this code extra reusable?

With PowerShell, I can dynamically construct a reference to an object’s strategies and members as so:

$Worth=2
$merchandise."Recipient$Worth"

By including a for loop, I can now move throw every of the Recipient* columns with out resorting to copy-pasting code. This allowed me to simplify my code all the way down to:

$OriginalCSV = import-csv 'authentic.csv'
$NewCSV = @()

Foreach ($merchandise in $OriginalCSV) {
    $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise.Recipient}

    for ($ii = 2; $ii -le 24; $ii++) {
        if ($merchandise."Recipient$ii" -ne '') {
            $NewCSV = $NewCSV + [PSCustomObject]@{Sender = $merchandise.Sender; Recipient=$merchandise."Recipient$ii"}
        }
    }
}
$NewCSV | Export-Csv -Path 'new.csv'

Until subsequent time,

Kieran

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments