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:
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.
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?
@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.
Bill, thanks a ton. This helped me with an export I was building!
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.
@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
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.
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
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
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?
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".
Post a Comment