Formatting powershell SQL Server output into columns and rows

Question

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

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

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.

Ansgar Wiechers · Accepted Answer · 2013-05-28 19:45:02Z

0

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

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

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

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

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

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

edited May 28, 2013 at 19:45

answered May 27, 2013 at 19:07

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ALB Over a year ago

Thanks, I'm getting some error messages, is this correct: -Property @{"mypropertyname" = $columnheaderA[example value]}?

Ansgar Wiechers Over a year ago

Probably not, from the looks of it. I added some more explanation to my answer.

RobinNilsson · Accepted Answer · 2013-05-27 18:49:35Z

0

I think what you are looking for is the -NoTypeInformation switch.

$results | export.csv $FilePathOfCSV -NoTypeInformation

-NoTypeInformation is a switch that omits type information from the outputted CSV file. The first line of the CSV contains the type followed by the full name of the .NET framework object it is using.

That should give you a structured CSV output.

Hope it works!

answered May 27, 2013 at 18:49

RobinNilsson

4881 gold badge16 silver badges26 bronze badges

1 Comment

ALB Over a year ago

Hi Robin, I tried this to no avail, I think the issue is with the output of the query from my input file

Collectives™ on Stack Overflow

Formatting powershell SQL Server output into columns and rows

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related