Map columns to new column names based on mapping file

user_5445457 0
Viewed 194

UPDATE: Went with the marked preferred solution based on other project needs. I also modified it slightly to throw out any column provided that isn't in the source, again, due to some other project needs.

$refTable = @'
SourceColumn,TargetColumn
LastName,Last_Name
DOB,Date_Of_Birth
FirstName,First_Name
'@ | ConvertFrom-Csv

$source = @'
FirstName,DOB,NotInRefCol,LastName
Tom,06/07/1940,1,Jones
Bill,11/27/1955,2,Nye
William,04/01/1564,3,Shakespeare
'@ | ConvertFrom-Csv

$map = @{}
foreach($line in $refTable) {
    $map[$line.SourceColumn] = $line.TargetColumn
}

foreach($line in $source) {
    $out = [ordered]@{}
    foreach($prop in $line.PSObject.Properties) {
        if (!($refTable.SourceColumn.Contains($prop.Name))) {
            continue
        }
        if($newCol = $map[$prop.Name]) {
            $out[$newCol] = $prop.Value
            continue
        }
        $out[$prop.Name] = $prop.Value
    }
    [pscustomobject]$out
}

I have a set of data in CSV format, and I'm trying to output the same data with different headers based on column names in a mapping document. The column names below are just an example for clarity, but the names can be drastically different, and the order may not match the order of the columns in the mapping file. I haven't done much Powershell work lately, so I'm having a tough time getting started. I can't seem to figure out the logic for looking up column names based on the data I'm looking at. For instance, if I do a foreach ($row in $source) how would I know how to order the columns? Any assistance would be appreciated!

Examples: map.csv

SourceColumn,TargetColumn
LastName,Last_Name
DOB,Date_Of_Birth
FirstName,First_Name

source.csv

FirstName,LastName,DOB
Tom,Jones,06/07/1940
Bill,Nye,11/27/1955
William,Shakespeare,04/01/1564

Desired output: output.csv

Last_Name,Date_Of_Birth,First_Name
Jones,06/07/1940,Tom
Nye,11/27/1955,Bill
Shakespeare,04/01/1564,William
csv powershell
4 Answers

Note: The following preserves the column order in source.csv and simply substitutes new column names. This enables relatively fast plain-text processing of the data CSV.

# Initialize an ordered hashtable.
$map = [ordered] @{}
# Fill it with the mapping of source to target column names.
Import-Csv map.csv | ForEach-Object { $map[$_.SourceColumn] = $_.TargetColumn }

# Read the data CSV into header line and all data lines.
# Note: You can speed this up with a somewhat obscure optimization:
#       $sourceHeader, $sourceData = (Get-Content -ReadCount 0 source.csv)
$sourceHeader, $sourceData = Get-Content source.csv

# Construct the new header line via the previously constructed
# map (ordered hashtable).
$newSourceHeader = ($sourceHeader -split ',' | ForEach-Object { $map[$_] }) -join ','

# Save the new header followed by the data lines to a (new) output
# file. Adjust the target file and -Encoding as needed.
Set-Content -Encoding utf8 output.csv -Value $newSourceHeader, $sourceData

If you do need the specific column ordering in map.csv, an inefficient, but simple solution is to apply the following to the output.csv file that resulted from the code above:

Import-Csv output.csv |
  Select-Object (Import-Csv map.csv).TargetColumn |
    Export-Csv output_new.csv -Encoding utf8 -NoTypeInformation
user_453750

Here is one way you could do it looping through the object's PSObject.Properties, this should work even if the reference table has only a specific set of new column names and you want to also keep those columns not in the reference table.

$refTable = @'
SourceColumn,TargetColumn
LastName,Last_Name
DOB,Date_Of_Birth
FirstName,First_Name
'@ | ConvertFrom-Csv

$source = @'
FirstName,LastName,DOB,NotInRefCol
Tom,Jones,06/07/1940,1
Bill,Nye,11/27/1955,2
William,Shakespeare,04/01/1564,3
'@ | ConvertFrom-Csv

$map = @{}
foreach($line in $refTable) {
    $map[$line.SourceColumn] = $line.TargetColumn
}

foreach($line in $source) {
    $out = [ordered]@{}
    foreach($prop in $line.PSObject.Properties) {
        if($newCol = $map[$prop.Name]) {
            $out[$newCol] = $prop.Value
            continue
        }
        $out[$prop.Name] = $prop.Value
    }
    [pscustomobject]$out
}

Result of the example above would be:

First_Name Last_Name   Date_Of_Birth NotInRefCol
---------- ---------   ------------- -----------
Tom        Jones       06/07/1940    1
Bill       Nye         11/27/1955    2
William    Shakespeare 04/01/1564    3
user_153395440

Build a hash between the two sets of headers, using the Source's column as the key and the destination column as the value:

$myHash = @{
    "LastName" = "Last_Name"
}

$myHash[$key]

produces the value for the destination.

user_67501820

Something along these lines would work:

$map = [ordered] @{}
Import-Csv C:\scripts\map.csv | ForEach-Object { $map[$_.SourceColumn] = $_.TargetColumn }  
$source = Get-Content C:\scripts\source.csv
# Replace names in the 1st line of your csv.
$source[0] = ($source[0] -split ',' | ForEach-Object { $map[$_] }) -join ','
$source | Out-File -FilePath C:\scripts\output.csv

Ensures only your header line is updated (no accidental values in other cells that may match header names) Obviously not a problem for your current data set but could be for other data sets. Though my output does keep your header in the order you input it. Rather than the output example you have that changed the order of your columns.

user_30377710
Related