时间戳

首页 > 解决方案 > 如何在 powershell 中打开 csv 文件并删除包含空白数据的行、更改列顺序并写出结果

powershell - 如何在 powershell 中打开 csv 文件并删除包含空白数据的行、更改列顺序并写出结果

问题描述

我想打开一个 csv 文件，检查行的内容是否有缺失数据，如果数据缺失则排除该行，然后以与我阅读的顺序不同的顺序写出列。

例如，这是输入 CSV 文件：

"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"

这是我希望的最终结果：

"userPrincipalName","sn","givenName","telephoneNumber"
"cmontini@engeo.com","Montini","Cory","925-395-2566"

这是我拥有的代码：

$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

我当前的输出

正在重新排序列...但不删除数据，我不知道为什么 #TYPE Selected.System.Management.Automation.PSCustomObject 在第一行

#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"

更新 1

我把代码改成了这个

$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

现在我在 Powershell 中得到了正确的输出：

givenName sn      userPrincipalName  telephoneNumber
--------- --      -----------------  ---------------
Cory      Montini cmontini@engeo.com 925-395-2566

但我的输出文件仍然被顶起

更新 2

这是最新的代码：

$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

我越来越近了......这是输出，但第一行是从哪里来的？

#TYPE Selected.System.Management.Automation.PSCustomObject
"userPrincipalName","givenName","sn","telephoneNumber"
"cmontini@engeo.com","Cory","Montini","925-395-2566"

标签： powershellcsv

解决方案

有很多方法，但快速简单的一种方法是：

$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

输出：

userPrincipalName  sn      givenName telephoneNumber
-----------------  --      --------- ---------------
cmontini@engeo.com Montini Cory      925-395-2566

推荐阅读