PowerShell Export-Csv无标题 [英] PowerShell Export-Csv No Headers
问题描述
我正在尝试在PowerShell脚本中使用Export-Csv
创建多个没有标题的CSV文件.我已经阅读了几篇有关使用Select-Object -Skip 1
技巧的文章,但还没有使它成功地用于我的代码.这是我尝试称呼它的方式:
I'm attempting to create multiple CSV files with no headers using Export-Csv
within a PowerShell script. I've read a few posts about using the Select-Object -Skip 1
trick, but I haven't gotten it to successfully work on my code. Here's how I tried to call it:
$DataSet.Tables[0] | Select-Object -Skip 1 |
Export-Csv -NoTypeInformation -Path "C:\$($csvname[$i])_$timer.csv"
这是我完整的功能代码:
Here's my full, functioning code:
function Run-Query {
Param([string[]]$queries,[string[]]$csvname)
Begin {
$SQLServer = 'myserver'
$Database = 'mydatabase'
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
$timer = (Get-Date -f yyyy-MM-dd)
} # End Begin
Process {
# Loop through each query
for ($i = 0; $i -lt $queries.Count; $i++) {
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
# Use the current index ($i) to get the query
$SqlCmd.CommandText = $queries[$i]
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
# Use the current index ($i) to get the sheetname for the CSV
$DataSet.Tables[0] | Export-Csv -NoTypeInformation -Path "C:\$($csvname[$i])_$timer.csv"
}
} # End Process
End {
$SqlConnection.Close()
}
} # End function run-query.
#Entery Query
$queries = @()
$queries += @'
SELECT * FROM table2
'@
$queries += @'
SELECT * FROM table1
'@
#List of CSV file names.
$csvname = @()
$csvname += 'file1'
$csvname += 'file2'
Run-Query -queries $queries -csvname $csvname
推荐答案
您误解了CSV在PowerShell中的工作方式.假设您有一个包含以下内容的CSV文件:
You're misunderstanding how CSVs work in PowerShell. Assume you have a CSV file with the following content:
foo,bar,baz
A,1,2
B,3,4
通过Import-Csv
导入CSV将为您提供2个对象,这些对象的属性foo
,bar
和baz
分别填充了一个数据行中的值.使用JSON表示法,对象列表如下所示:
Importing that CSV via Import-Csv
will give you 2 objects with the properties foo
, bar
, and baz
filled with the values from one data row each. Using JSON notation the list of objects would look like this:
[
{
"foo": "A",
"bar": "1",
"baz": "2"
},
{
"foo": "B",
"bar": "3",
"baz": "4"
}
]
Export-Csv
的作用基本相同,只是相反.我将对象作为输入,并将其属性的值写入CSV的字段.这些字段由接收的第一个对象Export-Csv
的属性确定.如果后续对象中缺少这些属性之一,则将NULL值写入相应的字段.如果后续对象具有其他属性,而第一个对象没有其他属性,则将其忽略.
Export-Csv
does basically the same, just in reverse. I takes objects as input and writes the values of their properties to the fields of the CSV. The fields are determined by the properties of the first object Export-Csv
receives. If one of these properties is missing in a subsequent object a NULL value is written to the respective field. If a subsequent object has additional properties that the first object didn't have they are ignored.
将Select-Object -Skip 1
与Import-Csv
或Export-Csv
一起使用是没有用的,因为通常您不想跳过任何输入或输出对象(否则将丢失数据).
Using Select-Object -Skip 1
with either Import-Csv
or Export-Csv
is useless, because normally you don't want to skip any of the input or output objects (otherwise you'd lose data).
但是,还有两个类似于Import-Csv
和Export-Csv
的cmdlet,它们读取和写入字符串而不是文件:ConvertFrom-Csv
和ConvertTo-Csv
. Import-Csv
基本上是Get-Content
和ConvertFrom-Csv
的组合,而Export-Csv
是ConvertTo-Csv
和Set-Content
的组合.
There are, however, two other cmdlets similar to Import-Csv
and Export-Csv
that read and write strings rather than files: ConvertFrom-Csv
and ConvertTo-Csv
. Import-Csv
is basically a combination of Get-Content
and ConvertFrom-Csv
, and Export-Csv
a combination of ConvertTo-Csv
and Set-Content
.
那么,Select-Object -Skip 1
在这里起什么作用?当您通过ConvertTo-Csv
将对象输入转换为CSV并跳过字符串输出的第一行时,可以有效地从输出文本中删除标题行,而Export-Csv
无法做到这一点.
So, where does Select-Object -Skip 1
come into play here? When you convert your object input to CSV via ConvertTo-Csv
and skip the first row of the string output, you effectively remove the header line from the output text, something you couldn't do with Export-Csv
.
演示:
PS C:\> $data = @()
PS C:\> $data += [PSCustomObject]@{"foo"="A"; "bar"="1"; "baz"="2"}
PS C:\> $data += [PSCustomObject]@{"foo"="B"; "bar"="3"; "baz"="4"}
PS C:\> $data | Format-Table -Auto
foo bar baz
--- --- ---
A 1 2
B 3 4
PS C:\> $data | ConvertTo-Csv -NoType
"foo","bar","baz"
"A","1","2"
"B","3","4"
PS C:\> $data | ConvertTo-Csv -NoType | Select-Object -Skip 1
"A","1","2"
"B","3","4"
将输出到Set-Content
的管道插入,您将拥有所需的无标题CSV文件.
Pipe that output into Set-Content
and you have the headerless CSV files you want.
$DataSet.Tables[0] |
ConvertTo-Csv -NoType |
Select-Object -Skip 1 |
Set-Content "C:\$($csvname[$i])_$timer.csv"
这篇关于PowerShell Export-Csv无标题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!