Thursday, April 7, 2011

[Powershell] Basic SQL query exported to CSV

Invoke-Sqlcmd -query "select getdate(), getdate()+1" -serverinstance "yourservername"|Export-Csv c:\temp\testps2.txt –notypeinformation

or, broken down by line so you can see all of it...

-query "select getdate(), getdate()+1"
-serverinstance "yourservername"

-query: the query.
-serverinstance: server name
-notypeinformation: removes the “#TYPE System.Data.DataRow” line at the top.

And if you don't want a header row... you have to use a different export process, and then tell a different process to iterate through the array and write to disk. Really, guys? Too hard to add a -noheader option?

(and all this is on one line; you can use a ` to split it across lines.

Invoke-Sqlcmd -query "select getdate(), getdate(); select getdate()+1, getdate()+1" -serverinstance "yourservername"|ConvertTo-Csv -notypeinformation -outvariable outdata; $outdata[1..($outdata.count-1)] |ForEach-Object {Add-Content -value $_ -path "c:\temp\test.txt"}

Invoke-Sqlcmd -query "select getdate(), getdate(); select getdate()+1, getdate()+1" `
-serverinstance "ftw-sv-db-03"|ConvertTo-Csv -notypeinformation -outvariable outdata;`
$outdata[1..($outdata.count-1)] |ForEach-Object {Add-Content -value $_ -path "c:\temp\test.txt"}
(then hit enter again to tell it you're done for realsies)

Oh, and it for some reason outputs the full file to console, but saves what you want to a file.

1 comment:

Unknown said...

Please can you help
I try to use a script to have result in a csv file but the file is empty!
Where is my error please?

foreach ($svr in get-content "D:\test\ListeInfoServeurs.txt")
$con = "server=$svr;database=master;Integrated Security=sspi"
$cmd = "SELECT name, physical_name, CAST(SUM(size * 8) AS FLOAT) / 1024 AS [Taille en Mo] FROM sys.master_files GROUP BY name, physical_name"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
$dt | Format-Table -autosize
} $output | export-csv export.csv -force -NoTypeInformation
$output | FT