（四）PowerShell过滤csv数据，生成insert SQL，使用PSCustomObject对象

• 使用Get-Member查看Import-Csv的结构

PS /Users/sixdog/Documents/PowerShell> Import-Csv -Path ./test.csv -Header "id","name","sex","age","birthday","comment"  | Get-Member

   TypeName: System.Management.Automation.PSCustomObject

Name        MemberType   Definition
----        ----------   ----------
Equals      Method       bool Equals(System.Object obj)
GetHashCode Method       int GetHashCode()
GetType     Method       type GetType()
ToString    Method       string ToString()
age         NoteProperty string age=20
birthday    NoteProperty string birthday=19871201
comment     NoteProperty string comment=无话可说1
id          NoteProperty string id=1
name        NoteProperty string name=zhangsan1
sex         NoteProperty string sex=男

PS /Users/sixdog/Documents/PowerShell> 

• 由Get-Member结果可知，得到的是PSCustomObject对象
• 使用对象过滤数据，做成SQL

#1.读取test.csv内以下数据
# 1,zhangsan1,男,20,19871201,无话可说1,
# 2,zhangsan2,女,20,19871201,无话可说2,
# 3,zhangsan3,男,20,19871201,无话可说3,
# 4,zhangsan4,女,20,19871201,无话可说4,
# 5,zhangsan5,男,20,19871201,无话可说5,
# 6,zhangsan6,女,20,19871201,无话可说6,
# 7,zhangsan7,男,20,19871201,无话可说7,
# 8,zhangsan8,女,20,19871201,无话可说8,
# 9,zhangsan9,男,20,19871201,无话可说9
#2.自定义表头："id","name","sex","age","birthday","comment"
#3.过滤性别是女的数据，赋值给$filterDto
$filterDto = Import-Csv -Path ./test.csv -Header "id","name","sex","age","birthday","comment" | Where-Object{
    $_.'sex' -eq '女'
}
Write-Host $filterDto
#输出$filterDto结构
#@{id=2; name=zhangsan2; sex=女; age=20; birthday=19871201; comment=无话可说2} 
#@{id=4; name=zhangsan4; sex=女; age=20; birthday=19871201; comment=无话可说4} 
#@{id=6; name=zhangsan6; sex=女; age=20; birthday=19871201; comment=无话可说6} 
#@{id=8; name=zhangsan8; sex=女; age=20; birthday=19871201; comment=无话可说8}
$SQL = $null
$filterDto | ForEach-Object{
    #循环每个对象
    $id = $_.'id'
    $name = $_.'name'
    $comment = $_.'comment'
    #这里要特别注意，powershell的换行符不是用我们习惯的\r\n,\n,\r;而是`n，我这里开始不知道输出的文件一值不换行
    $SQL +="insert into table (id,name,comment) value ($id,'$name','$comment'); `n"
}
# 文件输出
$SQL | Out-File -FilePath ./输出SQL.txt

• 输出SQL.txt文件的内容

insert into table (id,name,comment) value (2,'zhangsan2','无话可说2'); 
insert into table (id,name,comment) value (4,'zhangsan4','无话可说4'); 
insert into table (id,name,comment) value (6,'zhangsan6','无话可说6'); 
insert into table (id,name,comment) value (8,'zhangsan8','无话可说8'); 

• 这次主要是学会使用Get-Member查看结构，从而知道怎么解析数据，这么做比上一章更方便，简介。