将 powershell SQL Server 输出格式化为列和行 [英] Formatting powershell SQL Server output into columns and rows

问题描述

我是 Powershell 的初学者，尝试查询 SQL Server 数据库并在组织的行和列的文件中输出 csv.我可以使用以下命令部分实现这一点:

I'm a beginner with Powershell trying to query a SQL Server database and output the csv in a file organised rows and columns. I can partially achieve this with the following command :

sqlps -Command Invoke-Sqlcmd -ServerInstance "xxx.xx.xxx.xxx" 
      -Database "xxxx" -InputFile "xxx" | out-file -filepath "xxxx"

我的问题是查询的输出不是以行和列的形式出现，而是将每一行组合为一个字符串列表，每个单元格一个字符串.例如:

My problem is that the output from the query doesn't come in rows and columns, instead it groups each row together as a list of strings, one string per cell. For example:

column a: value 1  
column b: value 1  
column c: value 1  

column a: value 2  
column b: value 2  
column c: value 2  

我也尝试过使用 export-csv，但这只是返回上面命令返回的每个字符串的长度，就像这个问题:导出csv只吐出长度

I have also tried using export-csv, however this just returns the length of each string returned by the command above, exactly like this issue: Export csv spits out length only

我已经尝试了 group-object，但我正在努力让它发挥作用.如果有人能从概念上解释我想要用一些明确的指导方针做什么，我将不胜感激.

I have tried a group-object, but I'm struggling to make it work. If somebody could explain conceptually what I'm trying to do with some clear guidelines it would be very much appreciated.

推荐答案

Export-Csv 期望输入是对象.字符串输入被认为是字符串对象，它只有一个属性(Length)，所以只导出这个属性.如果你的输入是一个字符串数组，你需要把它变成一个对象，例如像这样:

Export-Csv expects the input to be objects. String input is considered as string objects, which have just one property (Length), so only this property is exported. If your input is an array of strings, you need to make it into an object, e.g. like this:

$array = "foo", "bar", "baz"

New-Object -Type PSCustomObject -Property @{
  "a" = $array[0]
  "b" = $array[1]
  "c" = $array[2]
} | Export-Csv output.csv -NoTypeInformation

以上将创建一个文件output.csv，内容如下:

The above would create a file output.csv with the following content:

"c","a","b"
"baz","foo","bar"

属性名称 (a, b, c) 成为 CSV 标题，属性值 (foocode>, bar, baz) 成为 CSV 值.

The property names (a, b, c) are become the CSV headers, the property values (foo, bar, baz) become the CSV values.

如果您的 SQL 查询生成一个数组列表，您可能必须执行以下操作:

If your SQL query generates a list of arrays, you'll probably have to do something like this:

Invoke-Sqlcmd ... | % {
  New-Object -Type PSCustomObject -Property @{
    "col1" = $_[0]
    "col2" = $_[1]
    "col3" = $_[2]
  }
} | Export-Csv output.csv -NoTypeInformation

不过，我手头没有 SQL 服务器，所以请谨慎对待.

I don't have an SQL server at hand, though, so take with a grain of salt.

这篇关于将 powershell SQL Server 输出格式化为列和行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！