184

How can I export a query result to a .csv file in SQL Server 2008?

Improve this question
5

15 Answers 15

Reset to default
207
  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

enter image description here

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

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

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

The answer is a bit misleading... When you press Ctrl+Shift+F, it only changes the output mode, it doesn't affect the results if you already ran the query. In order for this to be reflected, you have to re-run the query. When you run it in "Results to File" mode, it should prompt you for where you would like to save the results.
Should be Go to Tools > Options > Query Results > SQL Server > Results To Text
Re-running the query wasn't enough for me. I had to close the query window, open a new one and then run the query again.
Confirmed @Breandán comment. The new settings won't apply to currently opened query window, must close them and re-run the query.
One thing I had to do was export it as the default RPT extension. After doing that you can simply rename it to a CSV and it works.
|
180

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!

Improve this answer

9 Comments

Only problem is that this method seems to not let you set any options. For example all the NULLs show in output files as "NULL", etc. Perhaps there is a way to set this that I don't know about, however.
Amazingly even in SSMS 2012, this does not properly quote text for CSV. A comma or new line within a CHAR/VARCHAR should be quoted, but is not. That causes data to shift into new columns or into a new line.
Also doesn't give you column headers.
@Don this does give column headers if you go into "Query"->"Query Options...", grid tab, and check "include column headers when copying or saving results"
This should be the answer.
|
55

You can use PowerShell

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

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

6 Comments

+10 if I could. Only answer that handles escaping. To get this to run, I had to add two lines at the top of the script: Add-PSSnapin SqlServerCmdletSnapin100 and Add-PSSnapin SqlServerProviderSnapin100.
if you face timeout add the querytimeout so e.g. Invoke-Sqlcmd -ServerInstance MySQLserver123 -Query $QueryFmt -querytimeout 600 | Export-CSV $AttachmentPath
As Powershell newbie I needed to install the SqlServer Module: Install-Module -Name SqlServer (but didn't need to snap in those Cmdlets). Also, I'd saved the commands in a script, which wouldn't run until I'd changed the execution policy: Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser.
@sandyscott I'm a Powershell newbie as well, so thank you. Anyway I think that in this case a RemoteSigned execution policy will suffice.
SSMS has a character limitation of (ONLY!) 8192 characters when saving results to text. Using PowerShell circumvents this! Thanks for this answer.
|
17

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"
Improve this answer

2 Comments

if you face timeout add the querytimeout so e.g. Invoke-Sqlcmd -ServerInstance MySQLserver123 -Query $QueryFmt -querytimeout 600 | Export-CSV $AttachmentPath
Your first solution worked like a charm. It also handles the carriage return issue quite well.
10

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.

Improve this answer

Comments

6

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.

Improve this answer

Comments

5

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

Improve this answer

Comments

3

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.

Improve this answer

Comments

3 
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
Improve this answer

Comments

3

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

Improve this answer

Comments

3

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

Improve this answer

1 Comment

FYI, this answer is already discussed in more details in the "possible duplicate" link to stackoverflow.com/questions/425379/…
2

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

Improve this answer

Comments

2

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.

Improve this answer

Comments

0

You can use both the Export Wizard of SQL Server Management Studio (SSMS) or the Save to CSV feature of a result set in Azure Data Studio.

If you need to export large amounts of data (>=2 GB) to CSV, the current tools provided by Microsoft are not adequate. You can use the PowerShell SqlBulkExport module for this purpose. Find more details about the module in this article.

Improve this answer

Comments

-2

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.

Improve this answer

1 Comment

Link ... Access denied?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.