How can I open csv file in powershell and remove lines with blank data, change column order and write out results

Question

I would like to open a csv file, check the contents of the row for missing data, exclude that row if data is missing, and then write out the columns in a different order than from what I read.

for example, here is the input CSV file:

"givenName","sn","userPrincipalName","telephoneNumber"
,,"[email protected]",
"Citrix","Scheduler Service","[email protected]",
,,,
,,,
"dbagent",,"[email protected]",
"Cory","Montini","[email protected]","925-395-2566"

Here is what I would like the end result to be:

"userPrincipalName","sn","givenName","telephoneNumber"
"[email protected]","Montini","Cory","925-395-2566"

Here is the code that I have:

$Path = 'c:\temp\ps\Test.csv'

Import-Csv -Path $Path | ForEach-Object {
    If ($_.givenName -eq '' ) {
    # how do I remove the line
    }
    If ($_.ns -eq '' ) {
    # how do I remove the line
    }
    If ($_.userPrincipalName -eq '' ) {
    # how do I remove the line
    }
    If ($_.telephoneNumber -eq '' ) {
    # how do I remove the line
    }
    $_
} | Format-Table -AutoSize


(Import-CSV -Path $Path) | Select-Object -Property userPrincipalName, givenName, sn, telephoneNumber | Export-CSV -Path $Path

my current output

Is reordering the columns... but not removing data and I don't know why #TYPE Selected.System.Management.Automation.PSCustomObject is on the first line

#TYPE Selected.System.Management.Automation.PSCustomObject
"userPrincipalName","givenName","sn","telephoneNumber"
"[email protected]","","",""
"[email protected]","Citrix","Scheduler Service",""
"","","",""
"","","",""
"[email protected]","dbagent","",""
"[email protected]","Cory","Montini","925-395-2566"

UPDATE 1

I changed the code to this

$Path = 'c:\temp\ps\Test.csv'

Import-Csv -Path $Path | Where-Object {
        $_.userPrincipalName -and $_.givenName -and $_.sn -and $_.telephoneNumber
    } | ForEach-Object {
    $_
} | Format-Table -AutoSize

(Import-CSV -Path $Path) | Select-Object -Property userPrincipalName, givenName, sn, telephoneNumber | Export-CSV -Path $Path

now I am getting the correct output in Powershell:

givenName sn      userPrincipalName  telephoneNumber
--------- --      -----------------  ---------------
Cory      Montini [email protected] 925-395-2566 

but my output file is still jacked up

UPDATE 2

Here is the latest code:

$Path = 'c:\temp\ps\Test.csv'
$Temp = 'c:\temp\ps\_temp.csv'

Import-Csv -Path $Path | Where-Object {
        $_.userPrincipalName -and $_.givenName -and $_.sn -and $_.telephoneNumber
    } | ForEach-Object {
    $_
} | Export-Csv -Path $Temp -NoTypeInformation

Remove-Item -Path $Path
Rename-Item -Path $Temp -NewName $Path

(Import-CSV -Path $Path) | Select-Object -Property userPrincipalName, givenName, sn, telephoneNumber | Export-CSV -Path $Path

I am getting closer... Here is the output, but where did the first line come from?

#TYPE Selected.System.Management.Automation.PSCustomObject
"userPrincipalName","givenName","sn","telephoneNumber"
"[email protected]","Cory","Montini","925-395-2566"

Answer 1

Here is the answer:

$InputFile = 'c:\temp\ps\Test.csv'
$Cleaned = 'c:\temp\ps\_cleaned.csv'
$Output = 'c:\temp\ps\_columnsAdjusted.csv'

Import-Csv -Path $InputFile | Where-Object {
        $_.userPrincipalName -and $_.givenName -and $_.sn -and $_.telephoneNumber
    } | ForEach-Object {
    $_
} | Export-Csv -Path $Cleaned -NoTypeInformation

(Import-CSV -Path $Cleaned) | Select-Object -Property userPrincipalName, givenName, telephoneNumber, sn | Export-CSV -Path $Output -NoTypeInformation

# this is clean up of temporary files and putting data into original file
Remove-Item -Path $Cleaned
Remove-Item -Path $InputFile
Rename-Item -Path $Output -NewName $InputFile

Answer 2

There is many ways, but quick and simple one can be:

$csv = Import-Csv [...] # add your csv path
$csv | ? {$_.givenName -ne '' -and $_.sn -ne '' `
-and $_.telephoneNumber -ne '' `
-and $_.userPrincipalName -ne ''} | 
Select userPrincipalName,sn,givenName,telephoneNumber
| Export-CSV -Path $Path -NoTypeInformation

The output:

userPrincipalName  sn      givenName telephoneNumber
-----------------  --      --------- ---------------
[email protected] Montini Cory      925-395-2566  

Answer 3

It's not the most obvious solution, but it is concise and works with any number of input columns:

(Import-Csv $Path) |
  Where-Object { $_.psobject.Properties.Value -notcontains '' } |
    Select-Object -Property userPrincipalName, givenName, sn, telephoneNumber |
      Export-Csv -NoTypeInformation $Path

• The property values of custom objects constructed by Import-Csv from CSV input data are always strings ([string] instances).

• $_.psobject.Properties.Value uses member-access enumeration to return an array of all property (column) values.

  • -notcontains '' returns $true only for those input objects for which all properties (columns) are nonempty.

Note: As in your own attempt, the (...) around the Import-Csv command ensures that the input file is read in full, up front, which allows writing back to the same input file with Export-Csv in the same pipeline (note that there is a small risk of data loss, should the pipeline get interrupted).