Using CSV header names from one file to create new file with different headers name

Question

Good day, all!

I am total noob to powershell scripting. My manager has tasked me with working on an integration script for a new system we will be using. I want to piggyback off of the headers in our HR integration file that we generate nightly and feed those values into a CSV file that has different headers. The new system requires these specific headers.The reason for piggybacking, is because we don't have an attribute in AD defined for UserStatus. The value in that column is critical to the new system. It will determine if a user is added or deleted from the system.

Here is my current script:

$dateString = Get-Date -Format yyyyMMdd

$users = Import-Csv -Path "c:\scripts\XXXXXX\ActiveDirectory_$dateString.csv" -Header `
'PreferredName,Last,EmployeeID,Email,UserStatus'

$OutFile = "C:\Scripts\XXXXXX\Test\DeltaFeed_$dateString.csv"

$Outheader =  "Funds,Firstname,Lastname,Employeenumber,Email,Action"

Add-Content -Path $OutFile -Value $Outheader

$userFunds = "XXXX"

$action = if ($_.UserStatus -eq 'A')
{'A'}
elseif ($_.UserStatus -eq 'T')
{'D'}

ForEach ($user in $users)
{
$outstring = $userFunds + "," + $_.PreferredName + "," + $_.Last + "," + $_.EmployeeID + "," + $_.Email + "," + $action
Add-Content -Path $OutFile -Value $outstring
}

The problem I am running into is the out file has the headers, but no values in each column. I am not quite sure what I am doing incorrectly here. I am not used to this syntax.

Answer 1

Your immediate problem is that you're passing a single string rather than an array of headers (column names) to Import-Csv -Header.

That is, instead of:

# WRONG: Single string.
#        If you do this, the objects returned will have a SINGLE property
#        literally named 'PreferredName,Last,EmployeeID,Email,UserStatus'
-Header 'PreferredName,Last,EmployeeID,Email,UserStatus'

you must use:

# OK: ARRAY of names.
-Header PreferredName, Last, EmployeeID, Email, UserStatus

Note that in argument-parsing mode (passing an argument to a command), simple array elements without spaces needn't be quoted; in expression-parsing mode, quoting is required, e.g. when you assign to a variable ($array = 'PreferredName', 'Last', ...); see this answer for more information about PowerShell's two parsing modes.

---

However:

• It sounds like using -Header with Import-Csv isn't actually what you need, because you cannot use it select only a subset of columns. Instead, -Header is used to specify column names for input data that lacks a header row. Import-Csv imports all columns^[1], and you can just restrict yourself to only accessing those properties (columns) that are of interest.

• As Lee_Dailey soundly advises, you should generally work with objects, using Import-Csv, Select-Object / [pscustomobject] instances and Export-Csv, which greatly simplifies your task. Plain-text processing is only ever required if the performance of the object-oriented processing is insufficient.

In your case, you can combine Select-Object with calculated properties, which can be used to both rename properties of the input objects and to provide dynamic values:

# Determine in- and output files.
$dateString = Get-Date -Format yyyyMMdd
$inFile = "C:\scripts\XXXXXX\ActiveDirectory_$dateString.csv"
$outFile = "C:\Scripts\XXXXXX\Test\DeltaFeed_$dateString.csv"

# Import, rename properties and add additional ones, then export.
#  * -NoTypeInformation is only needed in Windows PowerShell.
#  * Adjust the -Encoding argument as needed; Windows PowerShell defaults
#    to ASCII(!), PowerShell [Core] v6+ to BOM-less UTF-8.
Import-Csv -Path $inputFile | 
  Select-Object @{ n='Funds'; e={ 'XXXX' } },
                @{ n='Firstname'; e='PreferredName' },
                @{ n='Lastname'; e='Last' },
                @{ n='Employeenumber'; e='EmployeeID' },
                Email, # no renaming required
                @{ n='Action'; e = { @{ A='A'; T='D' }[$_.UserStatus] } } |
    Export-Csv $outFile -NoTypeInformation -Encoding utf8

---

^{[1] If you use -Header and the specified number of column names is less than the number of data columns, then you can technically return a subset of columns, but (a) only starting from the first column, and (b) as stated, only if the input data has no header row.}

Answer 2

I took did this a bit differently. The immediate issue I see is passing the single variable to the csv. Second I dislike using CSV's when you really want to use Excel. I rewrote your original to do what you were saying.

 #Variable assignation
 $dateString = (Get-Date -Format yyyyMMdd)
 $userFunds = "XXXX"

 $original = “c:\scripts\XXXXXX\ActiveDirectory_$dateString.csv”
 $path = “c:\scripts\XXXXXX\Test\DeltaFeed_$dateString.xlsx”
 
 $users = Import-Csv -Path $original
 $Excel = New-Object -ComObject excel.application
 $Excel.visible = $false #True if you want it to popup before saving.
 $workbook = $Excel.workbooks.add()
 $excel.cells.item(1,1) = “Funds”
 $excel.cells.item(1,2) = “FirstName”
 $excel.cells.item(1,3) = “LastName”
 $excel.cells.item(1,4) = “Employee Number”
 $excel.cells.item(1,5) = “Email”
 $excel.cells.item(1,5) = “Action”
 $i=2
 foreach($user in $users)
 {
   $action = if ($user.UserStatus -eq 'A')
   {'A'}
   elseif ($user.UserStatus -eq 'T')
   {'D'} 
 
 $excel.cells.item($i,1) = $userFunds
 $excel.cells.item($i,2) = $user.PreferredName
 $excel.cells.item($i,3) = $user.Last
 $excel.cells.item($i,4) = $user.EmployeeID
 $excel.cells.item($i,5) = $user.Email
 $excel.cells.item($i,6) = $user.UserStatus
 $excel.cells.item($i,6) = $action
 $i++
 } #end foreach user
 $workbook.saveas($path)
 $Excel.Quit()
 Remove-Variable -Name excel
 [gc]::collect()
 [gc]::WaitForPendingFinalizers()

Answer 3

my solution :

$dateString = Get-Date -Format yyyyMMdd

import-csv "C:\scripts\XXXXXX\ActiveDirectory_$dateString.csv" -Header Firstname,Lastname,Employeenumber,Email,Action | %{

if ($_.Action -eq 'T') {$_.Action='D'}

Add-Member -InputObject $_ -NotePropertyName "Funds" -NotePropertyValue "XXXX" -PassThru

} |  export-csv "C:\Scripts\XXXXXX\Test\DeltaFeed_$dateString.csv" -NoType