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"
,,"Administrator@engeo.com",
"Citrix","Scheduler Service","citrixscheduler@engeo.com",
,,,
,,,
"dbagent",,"dbagent@engeo.com",
"Cory","Montini","cmontini@engeo.com","925-395-2566"

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

"userPrincipalName","sn","givenName","telephoneNumber"
"cmontini@engeo.com","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"
"Administrator@engeo.com","","",""
"citrixscheduler@engeo.com","Citrix","Scheduler Service",""
"","","",""
"","","",""
"dbagent@engeo.com","dbagent","",""
"cmontini@engeo.com","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 cmontini@engeo.com 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"
"cmontini@engeo.com","Cory","Montini","925-395-2566"

add the `-NoTypeInformation` parameter to the `Export-CSV` cmdlet to remove the `#TYPE` line, also make sure not to add the `Format-Table` before the `Export-CSV` — Avshalom, Nov 13 '19 at 17:25

score 1 · Answer 1 · answered Nov 13 '19 at 17:21

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
-----------------  --      --------- ---------------
cmontini@engeo.com Montini Cory      925-395-2566

score 0 · Accepted Answer · answered Nov 13 '19 at 17:41

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

mklement0 · Answer 3 · 2022-03-10T23:23:29.697

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).

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

3 Answers3