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

Find ramblings

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

16 comments:

Unknown 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 Fellows 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

Unknown 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 Fellows 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 Fellows 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.

Anonymous 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.

OG said...

hi bill, thanks for the script. i am trying to export the data to txt without any column header.
Also i also seem to get this:#TYPE System.Data.DataRow in my output each time.

i know this post is old but i would really love a prompt answer.

Thanks

Gareth said...

Nate and OG, you can get rid of the #TYPE System.Data.DataRow in the first row by changing the export line to:

$DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation

Unknown said...

Hi Bill this worked wonderfully until I hit a script that brought back nulls and I get the message:

Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.
At C:\xxx.ps1:29 char:22
+ $DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand

How can I handle the nulls correctly?

coremanster said...

sqlcmd in Powershell works exactly as it does from CMD (DOS shell). It doesn't really format output in a normal data file sense of the meaning.

You could use either Invoke-sqlcmd or bcp.

----if you really have to have it be comma-separated:
bcp "your query here" queryout nul -S server -E -o "y:\our\file\here.txt" -c -t","

bcp "your query here" queryout nul -S server -E -o "y:\our\file\here.txt" -c
...the critical part using queryout with BCP is that nul right after it
...and, the "-c" option, as that coerces every output value to string representation.

if you really want more control over things, use BCP to first create a format file, then twiddle that, and use the format file (-f "y:\our\bcpformatfile.fmt") instead of "-c".