-1

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
SQL Shane
  • 115
  • 4
  • `-t,` for the comma-separator, `-t` just picks the default, which is tab delimited – Richard Hansell Apr 29 '19 at 09:35
  • Possible duplicate of [How to automatically export data from SQL Server 2012 to CSV file?](https://stackoverflow.com/questions/32186750/how-to-automatically-export-data-from-sql-server-2012-to-csv-file) – Noor A Shuvo Apr 29 '19 at 09: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 Apr 29 '19 at 09:43

1 Answers1

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,'"','""') + ...
Thom A
  • 88,727
  • 11
  • 45
  • 75