sqlcmd csv export
Assuming no commas in the source data, one can do this without using powershell with our old friend SQLCMD.sqlcmd -S localhost -d master -E -W -w 999 -s "," -Q "SELECT D.* FROM sys.databases D" -o "C:\sqlcmd.csv"
powershell export query results to csv
I'm still a neophyte at this powershell stuff, but here's my 30 second take at it. I like this solution better than the sqlcmd simply because the export library looks cleaner.# 2011-03-01 # Bill Fellows # # This PowerShell script is designed to demonstrate how to run a query # against a database and dump to a csv # # Usage: Save this file as C:\sandbox\powershell\databaseQuery.ps1 # Start Powershell (Win-R, powershell) # Execute the script (C:\sandbox\powershell\databaseQuery.ps1) # # If the above fails due to # "cannot be loaded because the execution of scripts is disabled on this system" # run this command within Powershell # Set-ExecutionPolicy RemoteSigned # See also http://technet.microsoft.com/en-us/library/ee176949.aspx # http://www.vistax64.com/powershell/190352-executing-sql-queries-powershell.html $server = "localhost" $database = "master" $query = "SELECT D.* FROM sys.databases D" # powershell raw/verbatim strings are ugly # Update this with the actual path where you want data dumped $extractFile = @" C:\test.csv "@ # If you have to use users and passwords, my condolences $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};" $connectionString = [string]::Format($connectionTemplate, $server, $database) $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $command = New-Object System.Data.SqlClient.SqlCommand $command.CommandText = $query $command.Connection = $connection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $command $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $connection.Close() # dump the data to a csv # http://technet.microsoft.com/en-us/library/ee176825.aspx $DataSet.Tables[0] | Export-Csv $extractFile
Update: Export to CSV from SSMS via PowerShell. This modification to the script allows you to select a query within SSMS and export it to a CSV
6 comments:
First off, it was nice meeting you on Friday (I am Mike from Children International) and second now that I am reading your blog, I figured I would share another way to do this as I love powershell.
With this way you either need to be using the native SQL version of powershell (open command prompt, type SQLPS) or add the sqlprovider and sqlcmdlet snapins. Either way will work. And then you can do it all in one line.
###code
invoke-sqlcmd -query "select D.* from sys.databases D" -database master -serverinstance localhost |export-csv -path c:\test.csv
Awesome, thanks Mike. It was also a pleasure to meet you. You should hang out with the other SQL folk on Twitter.
Aaron Nelson(blog|twitter) also sent this one-liner
invoke-sqlcmd -query "sp_databases" -database master -serverinstance localhost | Export-CSV c:\temp\MyExport.csv
Yeah, I am a big fan of Aaron Nelson, he is without a doubt one of if not the best Powershell guy out there.
His script is identical to what I sent other than the query passed in. :-)
So you're saying I should actually read what I'm posting? Suppose that's the downside of blogging during meetings
I was using this example and it seems I get
#TYPE System.Data.DataRow
in the first row of the output file.
Any way to exclude that?
Apologies if this is lame, but I know nothing except what you post. :)
Hey Nate, what data type is in your query? Is it a binary type like TEXT, IMAGE, binary/varbinary? You can ping me at bill.spam.fellows@gmailspam.com except hold the spam.
Post a Comment