Changing Data in Columns in a CSV

Question

I have a PowerShell script pulling data in from a CSV. What I am trying to do is "replace" the data in the Account column based on the value. For example, Account 001 = Hardware, Account 002 = Software, etc. The data in the CSV is being pulled from a SQL database so if it would be easier for me to change it in the SQL script, I can do that easily. The Account column in the CSV has 001, 002, etc. I want to change those values to Hardware, Software, etc. Thanks for the help.

$Results = import-csv Expenses.csv

$Array = @()       
Foreach($R in $Results)
{
        $Object = [pscustomobject][ordered] @{
 
            Account = $R.Account
            Vendor = $R.Desc1
            Item = $R.Desc2
            Amount = $R.Amount
            
 
        }
        $Array += $Object
}

$Array

Answer 1

If your CSV looks anything like this:

Account,Vendor,Item,Amount
001,Some Vendor,Something expensive, 1
002,Another Vendor,Something cheapish,26

you can update without a loop:

# create a lookup hashtable where you combine the account values with the wanted replacement
$lookup = @{
    '001' = 'Hardware'
    '002' = 'Software'
    # etcetera
}
# import the csv and update the `Account` column
$Results = Import-Csv D:\Test\Expenses.csv | Select-Object @{Name = 'Account'; Expression = {$lookup[$_.Account]}}, * -ExcludeProperty Account

# display on screen
$Results
# output to (new) csv file
$Results | Export-Csv -Path D:\Test\Expenses_Updated.csv -NoTypeInformation

Result:

Account  Vendor         Item                Amount
-------  ------         ----                ------
Hardware Some Vendor    Something expensive 1     
Software Another Vendor Something cheapish  26   

---

As per the comment of not2qubit some explanation about the Select-Object statement used.

Because the result should reflect all fields in the csv, where the existing field value named Account needs to be replaced, the code uses a Calculated property to set the Account field values using whatever was stored in the lookup Hashtable.

This is done with @{Name = 'Account'; Expression = {$lookup[$_.Account]}}

Next, all other fields contained in the csv are selected unchanged using the asteriks *.

Because we're overwriting the Accound field, but keep its name, the line ends with -ExcludeProperty Account in order to remove the original Account field in the output. If we don't do that, PowerShell will show an error: Select-Object : The property cannot be processed because the property "Account" already exists.

Answer 2

If I have understood what you require correctly, you just want to change "001" to "Hardware" and so on in the object imported by the Import-Csv cmdlet. You can create a ScriptBlock with a switch that will return a value based off the value you have searched for. I could have recommended a Hashtable here too, but the benefit of a switch over a Hashtable, in this case, is that you can return the value using the default option if it is not specified. For example:

$Lookup = {
    Param ([string]$Value)
    
    switch ($Value) {
        "001" { "Hardware" }
        "002" { "Software" }
        default { $Value }
    }
}

$Results = Import-Csv Expenses.csv

foreach($R in $Results)
{
    # Invoke the scriptblock with the named parameter.
    $R.Account = & $Lookup -Value $R.Account
}

# Do stuff with $Results