A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Loading...

Tuesday, March 1, 2011

Powershell export query to csv

This morning I saw a tweet from Matt Velic (blog|twitter) and he was trying to run a query, dump the results to a csv and then ftp the file.

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

12 comments:

Michael said...

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

Bill said...

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

Michael said...

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. :-)

Bill said...

So you're saying I should actually read what I'm posting? Suppose that's the downside of blogging during meetings

Nate said...

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. :)

Bill said...

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.

octopusgrabbus said...

Bill: How do you launch this? I've got an MSSQL Server 2008. I'm assuming the script could just be scheduled to run, since it's PowerShell, right?

Bill Fellows said...

@octopusgrabbus yeah, if you wanted to create a job step of PowerShell, the script will run just fine. I put a quick example of a PowerShell export from SQL Agent on a new post.

Matt said...

Bill, thanks a ton. This helped me with an export I was building!

Davy said...

Bill, thanks for the info. I'm new to Powershell and trying to figure out how to export data to CSV from a MAS 200 ODBC database. I'm able to retrieve the records, however I'm struggling with the export to CSV part. I am trying to adapt your script, but receiving a "Cannot index into a null array message". I can return the records to the screen, just not to the specified file. Thanks for any help you can offer.

Bill Fellows said...

@Davy so if you comment out the last line, everything works good? That would make me think the $DataSet.Tables has nothing in it. Try adding "Write-Host $DataSet.Tables.Count" just prior to the last line there. Feel free to hit me up on email and I'll be happy to see if we can't diagnose the issue bill.spam.fellows@spamgmail.com just be sure and hold the spam


Davy said...

I found my problem. I didn't modify my line of code retrieving the DataTable instead of DataSet. Simple as that. Working perfectly now. Thanks again for your help.