Author Posts

July 16, 2014 at 5:01 pm

I'm trying to automate an ad import using an unedited csv file nightly. The problem is the data base I am exporting from has its own headers. Is it possible to replace the entire header row with another set of values?

Example of current values Last Name,First Name, and so on.....

Want to replace it with Ad attribute names.

Then save the csv file out to a different file name.

I'm kinda new to scripting so any help will be greatly appreciated!

Thanks

July 16, 2014 at 5:33 pm

Sure. There are a couple of ways to go about that; I prefer to import the old CSV file, then use Select-Object to change the property names of objects in memory, and export back to CSV with the new property names (which become the header row). Something like this:

$propertyTranslation = @(
    @{ Name = 'givenName'; Expression = { $_.'First Name' } }
    @{ Name = 'surName';   Expression = { $_.'Last Name'  } }
    # And so on
)

(Import-Csv -Path 'yourCsvFile.csv') |
Select-Object -Property $propertyTranslation |
Export-Csv -Path 'yourCsvFile.csv' -NoTypeInformation

Note: The parentheses around the Import-Csv command are important, if you want to write back to the same file in a single pipeline. However, this holds the entire file in memory, which may be a problem if your CSV is very large. If you want to stream one line at a time instead, you need to read and write from different files, then move the new file back over top of the original afterward.

July 17, 2014 at 2:34 am

I typically build a custom psobject and fill it with the desired property names and values before exporting to CSV. Don't know if it's applicable in your specific case though.

$result = foreach($item in $dataSource) {
    Write-Output (New-Object psobject -Property @{
        FirstName = $item.givenName
        LastName = $item.surName
        # And so on..
    })
}

Export-Csv -InputObject $result -Path 'C:\CSV\result.csv' -NoTypeInformation

July 17, 2014 at 4:46 am

I usually use Dave's method using calculated properties which will replace the column name. Just thought I would mention another method is using a Alias for the property. In a situation where you want to bring it into Powershell, do some work and export it back to the other process with the same column names, it's easier to assign an Alias rather than rename the columns coming in and then rename them back when exporting:

$processes = Get-Process
$processes | Add-Member -MemberType AliasProperty -Name ProcessID -Value Id
$processes | Select Id, ProcessID

In the above example, you can reference the Id as either ID or ProcessID (A.K.A)