Formatting powershell SQL Server output into colum

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.

Answer 1

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!

Answer 2

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.