Powershell: Replace headers while using Import-CSV

Question

I found a related answer here that is really helpful, but not quite what I'm looking for. There are also a number of other questions I've looked at, but I can't figure out how to get this to work unfortunately and it seems rather simple.

Basically, I'm using Import-Csv and manipulating a lot of data; but the names of the headers can sometimes change. So instead of re-writing my code, I'd like to map the headers I'm given to the headers that are used in my code blocks. Outputting the final data as a CSV, I can leave it using the 'updated headers' or, if I can figure out how to swap headers easily, I could always swap them back to what they were.

So let's say I have a mapping file in Excel. I can do the mapping in rows or columns, whichever will be easier. For this first example, I have the mapping in rows. When I use Import-CSV, I want to use the Headers from Row #2 instead of the headers in Row #1. Here's the content of the mapping file:

So basically if I hard coded this all, I'd have something like:

$null, $headerRow, $dataRows = (Get-Content -Raw foo.csv) -split '(^.+\r?\n)', 2

ConvertFrom-Csv ($headerRow.Trim() -replace 'Identification', 'ID' -replace 'Revenue Code', 'Revenue_Code' -replace 'Total Amount for Line', 'Amount' -replace 'Total Quantity for Line', 'Qty'), $dataRows

Except I don't want to hard code it, I am basically looking for a way to use Replace with a mapping file or hashtable if I can create one.

#Pseudo code for what I want
$hashtable = Get-Content mapping.xlsx
ConvertFrom-Csv ($headerRow.Trim() -replace $hashtable.Name, $hashtable.Value), $dataRows

I'm probably failing and failing to find similar examples since I'm trying to be flexible on the format of the mapping file. My original idea was to basically treat the 1st row as a string, and to replace that entire string with the second row. But the hashtable idea came from likely restructuring the mapping to look like this:

Here I would basically -replace each Source value with the corresponding Target value.

EDIT If you need to convert back, give this a shot - but keep in mind it'll only work if you have a one-to-one relationship of Source:Target values.

#Changing BACK to the original Headers...
$Unmap = @{}
(Import-Csv MappingTable.csv).ForEach({$Unmap[$_.Target] = $_.Source})

#Get string data from CSV Objects
$stringdata = $outputFixed | ConvertTo-CSV -NoTypeInformation
$headerRow = $stringdata[0]
$dataRows = $stringdata[1..($stringdata.Count-1)] -join "`r`n"

#Create new header data
$unmappedHeaderRow = ($headerRow -replace '"' -split ',').ForEach({'"' + $Unmap[$_] + '"'}) -join ','

$newdata = ConvertFrom-Csv $unmappedHeaderRow, $dataStrings

Answer 1

Here's a complete example that builds on your original attempt:

• It provides the column-name (header) mapping via (another) .csv file, with columns Source and Target, where each row maps a source name to a target name, as (also) shown in your question.

• The mapping CSV file is transformed into a hashtable that maps source names to target names.

• The data CSV file is then read as plain text, as in your question - efficiently, but in full - split into header row and data rows, and a new header row with the mapped names is constructed with the help of the hashtable.

• The new header row plus the data rows are then sent to ConvertFrom-Csv for to-object conversion based on the mapped column (property) names.

# Create sample column-name mapping file.
@'
Source,Target
Identification,Id
Revenue Code,Revenue_Code
'@ > mapping.csv

# Create a hashtable from the mapping CSV file
# that maps each Source column value to its Target value.
$map = @{}
(Import-Csv mapping.csv).ForEach({ $map[$_.Source] = $_.Target })

# Create sample input CSV file.
@'
Revenue Code,Identification
r1,i1
r2,i2
'@ > data.csv

# Read the data file as plain text, split into a header line and
# a multi-line string comprising all data lines.
$headerRow, $dataRows = (Get-Content -Raw data.csv) -split '\r?\n', 2

# Create the new header based on the column-name mapping.
$mappedHeaderRow =
  ($headerRow -replace '"' -split ',').ForEach({ $map[$_] }) -join ','

# Parse the data rows with the new header.
$mappedHeaderRow, $dataRows | ConvertFrom-Csv

The above outputs the following, showing that the columns were effectively mapped (renamed):

Revenue_Code Id
------------ --
r1           i1
r2           i2

Answer 2

The easiest thing to do here is to process the CSV and then transform each row, from whatever format it was, into a new desired target format.

Pretend we have an input CSV like this.

RowID,MayBeNull,MightHaveAValue
1,,Value1
2,Value2,
3,,Value3

Then we import the csv like so:

#helper function for ugly logic
function HasValue($param){
    return -not [string]::IsNullOrEmpty($param)
}

$csv = import-csv C:\pathTo\this.csv

foreach($row in $csv){
   if (HasValue($row.MayBeNull)){
       $newColumn = $row.MayBeNull
    }
    else{
       $newColumn = $row.MightHaveAValue
    }
    #generate new output
    [psCustomObject]@{
       Id = $row.RowId;
     NewColumn = $newColumn
    }
}

Which gives the following output:

This is an easy pattern to follow for a data migration script, then you just need to scale it up to fix your problem.