How can I export a query result to a .csv file in SQL Server 2008?
This question is related to
sql-server-2008
I hope 10 years isn't too late, I used this in Windows Scheduler;
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
-S BLRREPSRVR\SQLEXPRESS -d Reporting_DB -o "C:\Reports\CHP_Gen.csv" -Q "EXEC dbo.CHP_Generation_Report" -W -w 999 -s ","
It opens sql command .exe and runs a script designed for sql command. The sql command script is very easy to use with a few google searches and trial and error. You can see that it picks a database, defines output location and executes a procedure. The procedure is just a query selecting which rows and columns to display from a table in the database.
Use T-SQL:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName
But, there are a couple of caveats:
You need to have the Microsoft.ACE.OLEDB.12.0 provider available. The Jet 4.0 provider will work, too, but it's ancient, so I used this one instead.
The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.
If you don't want to use Powershell, this answer is a variation on 8kb's great answer. The only difference is that instead of selecting CSV as the output format, select Tab Delimited. This way if there are commas in your data, it won't skip cells in Excel. Also, if you have Excel's default delimiter set to tabs, you can simply do a copy-all of the SSMS query results (CTRL-A, CTRL-C) and paste into Excel (no need to save as a file and import to Excel):
Now you can execute your query, then do a CTRL-A to select all the results, then CTRL-C to copy to clipboard, then switch to Excel 2013 (may work in 2007 too, not sure) and paste -- assuming Excel's default delimiter is set to tab.
MS Excel -> Data -> New Query -> From Database ..follow the steps
Using the native SQL Server Management Studio technique to export to CSV (as @8kb suggested) doesn't work if your values contain commas, because SSMS doesn't wrap values in double quotes. A more robust way that worked for me is to simply copy the results (click inside the grid and then CTRL-A, CTRL-C) and paste it into Excel. Then save as CSV file from Excel.
One more method worth to mention here:
SQLCMD -S SEVERNAME -E -Q "SELECT COLUMN FROM TABLE" -s "," -o "c:\test.csv"
NOTE: I don't see any network admin let you run powershell scripts
If the database in question is local, the following is probably the most robust way to export a query result to a CSV file (that is, giving you the most control).
After going through this process exhaustively, I found the following to be the best option
PowerShell Script
$dbname = "**YOUR_DB_NAME_WITHOUT_STARS**"
$AttachmentPath = "c:\\export.csv"
$QueryFmt= @"
**YOUR_QUERY_WITHOUT_STARS**
"@
Invoke-Sqlcmd -ServerInstance **SERVER_NAME_WITHOUT_STARS** -Database $dbname -Query $QueryFmt | Export-CSV $AttachmentPath -NoTypeInformation
Run PowerShell as Admin
& "c:\path_to_your_ps1_file.ps1"
Building on N.S's answer, I have a PowerShell script that exports to a CSV file with the quote marks around the field and comma separated and it skips the header information in the file.
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
$qry = @"
Select
*
From
tablename
"@
Invoke-Sqlcmd -ServerInstance Server -Database DBName -Query $qry | convertto-CSV -notype | select -skip 1 > "full path and filename.csv"
The first two lines enable the ability to use the Invoke-SqlCmd command-let.
You can use PowerShell
$AttachmentPath = "CV File location"
$QueryFmt= "Query"
Invoke-Sqlcmd -ServerInstance Server -Database DBName -Query $QueryFmt | Export-CSV $AttachmentPath
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
SELECT Field1, Field2, Field3 FROM DatabaseName
as @Slogmeister Extraordinaire Quoted is correct.
One need to have 1> File already present with columns 2> One need to have Office installed
Errors faced
Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"."
Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 0
RECONFIGURE
GO
You could use QueryToDoc (http://www.querytodoc.com). It lets you write a query against a SQL database and export the results - after you pick the delimiter - to Excel, Word, HTML, or CSV
Yes, all these are possible when you have the direct access to the servers. But what if you have only access to the server from a web / application server? Well, the situation was this with us a long back and the solution was SQL Server Export to CSV.
I know this is a bit old, but here is a much easier way...
Run your query with default settings (puts results in grid format, if your's is not in grid format, see below)
Right click on grid results and click "Save Results As" and save it.
If your results are not in grid format, right click where you write the query, hover "Results To" and click "Results To Grid"
Be aware you do NOT capture the column headers!
Good Luck!
Source: Stackoverflow.com