Is there a way to export query results to a CSV through query itself?

Question

I want to export the results of a SQL Query to a CSV file in SQL Server Mgmt Studio, but not through the GUI. I want to do it automatically through my actual query. Is there an "Export to CSV" statement I can use in SQL Server? I have searched online and am only finding sites talking about how to export results to CSV through the GUI.

ANSWER

I utilized Powershell which is found in the this answer down in the below post:

Export query result to .csv file in SQL Server 2008

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

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

The third answer down might. But what goes in the `$QueryFmt` variable. Do you know? — Crimp, Apr 20 '23 at 19:21
that is a powershell variable where you add your query between both "" — nbk, Apr 20 '23 at 19:24
Glad to hear it, @Crimp, but note that answer positions are subject to change, both over time and based on one's "Sorted by" setting. I assume you meant [this answer](https://stackoverflow.com/a/12950723/45375), right? — mklement0, Apr 20 '23 at 22:43

score 1 · Answer 1 · answered Apr 20 '23 at 18:51

1

Can you try if this will work on your side.

Use the SQLCMD utility to export the result of a query to a CSV file.

sqlcmd -S <SERVERNAME> -d <DATABASENAME> -E -s"," -W -Q "SELECT * FROM <TABLENAME>" -o "C:\PATH\TO\FILENAME.csv"

answered Apr 20 '23 at 18:51

Michael C

308
1
6

score 1 · Answer 2 · answered Apr 20 '23 at 19:03

You have different ways

1.sqlcmd

https://learn.microsoft.com/en-us/sql/tools/sqlcmd/sqlcmd-utility?view=sql-server-ver16

example:

sqlcmd -S . -Q "select * from Employees;" -o "C:\employees.csv" -W -w 1024 -s "," -h-1

sqlcmd is the command name. -S . specifies the server name.

-Q "select * from Employees;" is the query that fetches data to be exported.

-o "C:\employees.csv" defines the file that stores the exported data.

-W removes the trailing white spaces you see in the command line tool output after each value. -w 1024 defines the length of the line in the CSV file.

-s "," defines the separator for the column values; here, a comma.

-h-1 removes the header.

2.SSIS

you can export data to a csv file using an SSIS package

https://www.mssqltips.com/sqlservertip/7209/export-data-to-excel-from-sql-server-integration-services-package/#:~:text=Double%2Dclick%20on%20the%20Flat,Delimited

3.C#

Is there a way to export query results to a CSV through query itself?

2 Answers2