How can I 开发者_JAVA百科export a query result to a .csv file in SQL Server 2008?

1. Open SQL Server Management Studio
2. Go to Tools > Options > Query Results > SQL Server > Results To Text
3. On the far right, there is a drop down box called Output Format
4. Choose Comma Delimited and click OK

Here's a full screen version of that image, below

This will show your query results as comma-delimited text.

To save the results of a query to a file: Ctrl + Shift + F

I know this is a bit old, but here is a much easier way...

1. Run your query with default settings (puts results in grid format, if your's is not in grid format, see below)

2. 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!

You can use PowerShell

$AttachmentPath = "CV File location"
$QueryFmt= "Query"

Invoke-Sqlcmd -ServerInstance Server -Database DBName -Query $QueryFmt | Export-CSV $AttachmentPath

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

1. Copy the query.
2. In Object Explorer right-click on the database in question.
3. Select "Tasks" >> "Export Data..."
4. Configure your datasource, and click "Next".
5. Choose "Flat File" or "Microsoft Excel" as destination.
6. Specify a file path.
7. If working with a flat file, configure as desired. If working with Microsoft Excel, select "Excel 2007" (previous versions have a row limit at 64k)
8. Select "Write a query to specify the data to transfer"
9. Paste query from Step 1.
10. Click next >> review mappings >> click next >> select "run immediately" >> click "finish" twice.

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"

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:

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

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

MS Excel -> Data -> New Query -> From Database ..follow the steps

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.

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.

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

1

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

64 bit http://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine_x64.exe

32 bit http://download.microsoft.com/download/f/d/8/fd8c20d8-e38a-48b6-8691-542403b91da1/AccessDatabaseEngine.exe

2

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

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

• In SSMS Go to Tools > Options > Query Results > SQL Server > Results To Text
• Change output format on far right to Tab Delimited
• Click OK

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.

Image of SSMS Query Options Screen

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.

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

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.