Export query result to .csv file in SQL Server 2008

Question

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

possible duplicate of [How to export data as CSV format from SQL Server using sqlcmd?](http://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd) — Martin Smith, Jul 02 '10 at 21:38
Do you need your delimiter escaped? Most of the answers so far assume you do not, which isn't really all that CSV. — Nick, Oct 06 '14 at 17:02
@Nick - generally delimiters are contained with a string only, and they typically have quotes around them. See my answer to see the solution to this. http://stackoverflow.com/questions/6115054/how-to-get-export-output-in-real-csv-format-in-sql-server-managment-studio/32660037#32660037 — JustBeingHelpful, Sep 18 '15 at 20:50
Related post - [Export table from database to csv file](https://stackoverflow.com/q/14212641/465053), [SQL Server 2008 - use cmd to output with headers to .csv](https://stackoverflow.com/q/3769302/465053) & [Saving results with headers in Sql Server Management Studio](https://stackoverflow.com/q/10677133/465053). — RBT, Mar 18 '19 at 09:51

score 198 · Accepted Answer · edited Jul 22 '14 at 05:22

198

Open SQL Server Management Studio
Go to Tools > Options > Query Results > SQL Server > Results To Text
On the far right, there is a drop down box called Output Format
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

edited Jul 22 '14 at 05:22

congusbongus

13,359
7
71
99

answered Jul 04 '10 at 04:46

8kb

10,956
7
38
50

I could not find where its saves result, can you give me a hint where to look? – Andrey Jul 09 '14 at 14:02
11

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. – qJake Jul 17 '14 at 17:01
3

Should be Go to Tools > Options > Query Results > **SQL Server** > Results To Text – congusbongus Jul 22 '14 at 05:21
57

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. – Breandán Sep 05 '14 at 14:29
3

Confirmed @Breandán comment. The new settings won't apply to currently opened query window, must close them and re-run the query. – Shinigamae Jun 22 '15 at 08:14
After going through this process exhaustively, I found that using Powershell in combination with SQLCMD to be a more configurable and efficient option. – pim Feb 03 '16 at 14:18
2

@Breandán Thank you for the comment, luckily I read it before throwing my laptop over the balcony. – EaziLuizi Apr 21 '16 at 09:49
5

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. – Thomas Bennett Jul 29 '16 at 17:36
@Breandán Right click -> Results To -> Results to File (Ctrl+Shift+F) – riezebosch Feb 21 '18 at 14:17
This does not handle embedded commas. – SteveC Nov 27 '18 at 00:36
Using SSMS 2017, in my system, after I make the change in the options, I have to restart SSMS in order for it to take effect. – Clark Vera Jun 04 '19 at 15:16
I restarted SSMS so changes took effect. – Abdollah Aug 23 '20 at 12:27
I didn't need to close the window, but I did realize that I could `right-click`, select `Query Options` and tweak the settings right from there. THEN my CSV data came out ok. (Actually though, where the heck are the quotes around the strings?!) – Scott Fraley Jan 21 '21 at 20:14

score 171 · Answer 2 · edited Jan 29 '20 at 00:10

171

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!

edited Jan 29 '20 at 00:10

aztroy

3
4

answered May 16 '12 at 20:00

Ricketts

5,025
4
35
48

1

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. – Noah Sep 06 '13 at 07:36
1

@Noah A way around nulls that I am using is for any columns that you know will be null you can use `ISNULL(NullableColumn,'') AS NullableColumn` This seems to work best for me. – Prodigal Maestro Apr 01 '14 at 19:35
18

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. – Eric J. Dec 07 '14 at 22:08
5

Also doesn't give you column headers. – Don Jan 24 '15 at 17:02
1

I use this method all the time for small amount of data. I currently have a result of over 800,000 rows. I get a system out of memory error! – Cathy Sullivan May 11 '15 at 20:58
11

@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" – Rob Wise Jun 06 '15 at 06:59
9

This should be the answer. – pspahn Jul 24 '15 at 22:52
No, this doesn't work if you have anything beyond a simple case where all of the fields in the output have non-NULL values and none of them are in a format or have values that would require quoting or escaping. – wades Apr 20 '18 at 15:44
you can set text to be quoted so you prevent the usag of charecters that are the same as the delimiter to be used as such: go to Tools -> Options -> Query results -> Results to grid, check the option "Quote strings containing list seperators when saving .csv results. Note that also for this setting to take effect, you need to start a new query window after adjusting the setting. (tested in MS SMS 2014) – Daniël Tulp Feb 11 '19 at 13:20

score 54 · Answer 3 · edited Mar 21 '14 at 15:02

54

You can use PowerShell

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

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

edited Mar 21 '14 at 15:02

Dan Bechard

5,104
3
34
51

answered Oct 18 '12 at 08:54

N.S

541
4
2

Thank you! This was the only suggestion that worked for me. Handles escaping properly – emertechie Apr 30 '13 at 21:22
3

+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`. – Eric J. Dec 07 '14 at 22:28
1

if you face timeout add the **querytimeout** so e.g. `Invoke-Sqlcmd -ServerInstance MySQLserver123 -Query $QueryFmt -querytimeout 600 | Export-CSV $AttachmentPath` – Tilo Apr 06 '17 at 16:44
1

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 Dec 19 '19 at 12:00
1

@sandyscott I'm a Powershell newbie as well, so thank you. Anyway I think that in this case a `RemoteSigned` execution policy will suffice. – BigBother Jun 06 '20 at 21:21
2

SSMS has a character limitation of (ONLY!) 8192 characters when saving results to text. Using PowerShell circumvents this! Thanks for this answer. – klabarge Jul 20 '21 at 16:26

pim · Answer 4 · 2017-06-01T14:38:34.937

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

Copy the query.
In Object Explorer right-click on the database in question.
Select "Tasks" >> "Export Data..."
Configure your datasource, and click "Next".
Choose "Flat File" or "Microsoft Excel" as destination.
Specify a file path.
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)
Select "Write a query to specify the data to transfer"
Paste query from Step 1.
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"

if you face timeout add the **querytimeout** so e.g. `Invoke-Sqlcmd -ServerInstance MySQLserver123 -Query $QueryFmt -querytimeout 600 | Export-CSV $AttachmentPath` — Tilo, Apr 06 '17 at 16:43
Your first solution worked like a charm. It also handles the carriage return issue quite well. — ABH, Dec 12 '21 at 12:25

score 8 · Answer 5 · edited Jun 12 '16 at 13:34

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.

score 6 · Answer 6 · edited Jun 12 '16 at 13:32

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.

score 6 · Answer 7 · answered May 11 '16 at 11:21

6

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

answered May 11 '16 at 11:21

tichra

557
5
18

score 3 · Answer 8 · answered Nov 27 '13 at 02:01

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.

score 3 · Answer 9 · edited Jun 20 '20 at 09:12

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

score 3 · Answer 10 · answered Mar 16 '17 at 23:00

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

score 2 · Answer 11 · answered Apr 22 '12 at 19:10

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

answered Apr 22 '12 at 19:10

Robert

21
1

score 2 · Answer 12 · answered Sep 11 '20 at 09:02

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.

score 1 · Answer 13 · edited Sep 10 '20 at 11:12

1

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

edited Sep 10 '20 at 11:12

fcdt

2,371
5
14
26

answered Sep 09 '20 at 16:24

Ayaz Hoda

51
1

1

FYI, this answer is already discussed in more details in the "possible duplicate" link to https://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd – mtone Sep 10 '20 at 06:20

score 0 · Answer 14 · answered Jan 05 '23 at 14:27

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.

score -2 · Answer 15 · edited Jun 12 '16 at 13:35

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

edited Jun 12 '16 at 13:35

Peter Mortensen

30,738
21
105
131

answered Mar 06 '14 at 16:57

Jiju Thomas Mathew

359
3
7

Link ... Access denied? – hoggar May 16 '20 at 11:54

Export query result to .csv file in SQL Server 2008

15 Answers15

1

2

Linked

Related