How to export a SQL table to a CSV file automatically in a query?

Question

I want to export a SQL table to a CSV file automatically within a query. I've already accomplished to export a SQL table to a .CSV but the output was a TAB-separated file and without the column headers. How to make the output comma separated and with column headers?

See below the query that works but without the comma delimiter and column headers.


DECLARE @cmd NVARCHAR(4000)
DECLARE @pathAndFileName NVARCHAR(MAX)
DECLARE @result INT
SET @pathAndFileName = 'C:\test.csv'
SET @cmd = 'bcp " SELECT * from  testdb.dbo.test_table " queryout "'
  + @pathAndFileName + '" -w -T -t -S Servername\SQLExpress; '       
EXEC @result = xp_cmdshell @cmd 
SELECT @result

-t, for the comma-separator, -t just picks the default, which is tab delimited — Richard Hansell
– Richard Hansell, Commented Apr 29, 2019 at 9:35
Possible duplicate of How to automatically export data from SQL Server 2012 to CSV file? — Noor A Shuvo
– Noor A Shuvo, Commented Apr 29, 2019 at 9:38
It's probably also worth noting that BCP hasn't got an option to export column headers, but there are plenty of hacks out there to do this, usually involving adding a UNION to your query, and either hardcoding the values, or pulling them from sys.columns, etc. — Richard Hansell
– Richard Hansell, Commented Apr 29, 2019 at 9:43

Thom A · Accepted Answer · 2019-04-29 11:13:28Z

2

You need to specify the field terminator when you use the -t (which by default is a tab (\t). As you haven't specified it, the default is used:

SET @cmd = 'bcp " SELECT * from  testdb.dbo.test_table " queryout "'
         + @pathAndFileName + '" -w -T -t "," -S Servername\SQLExpress; ';

Note, however, I strongly suggest against concatenating the raw value of @pathAndFileName, especially when it's an nvarchar(MAX). What you have there could easily be injected.

If @pathAndFileName will always have a value less than or equal to 128 characters, then i would declare @pathAndFileName as sysname, and then use ...+ QUOTENAME(@pathAndFileName,'"') + .... If it could be over 128 characters, then you'll need to quote it yourself:

... + REPLACE(@pathAndFileName,'"','""') + ...

edited Apr 29, 2019 at 11:13

answered Apr 29, 2019 at 9:35

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

1 Comment

SQL Shane Over a year ago

Thanks! This helped a lot. Only the column headers though.

Collectives™ on Stack Overflow

How to export a SQL table to a CSV file automatically in a query?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related