Add FOR CSV to facilitate CSV export
We have FOR XML and FOR JSON. But it is not uncommon that people want to produce CSV files. This can be done with BCP, but it is not straightforward not the least if you want to add headers. And if you only want fields to be quoted when needed, BCP is not really what you are looking for. SSIS is also an alternative, but SSIS does not always fit in.
Thus, it would be handy if there was a an option like FOR CSV, with a syntax like:
FOR CSV [, HEADERS] [,DELIMITER(',')] [, QUOTE('"')]
HEADERS would mean that the column headers would be added on the first line in the CSV output. DELIMITER would specify the delimiter, with comma as the default, and QUOTE would specify which character used to quote the data. (Whehter QUOTE is actually needed, I don't know.)
Introducing FOR CSV will be definitely handy. I tried this approach - http://www.allaboutmssql.com/2019/01/sql-server-export-adhoc-sql-or-table-to.html
John Zabroski commented
@Erland Sommarskog - quote is definitely needed, as per the RFC for CSV
5. Each field may or may not be enclosed in double quotes (however
some programs, such as Microsoft Excel, do not use double quotes
at all). If fields are not enclosed with double quotes, then
double quotes may not appear inside the fields. For example:
For interpretation of "may not", please see this RFC: https://www.ietf.org/rfc/rfc2119.txt
This would be awesome - although i would also to love to see some CSV love shown to the bulk import too, specifically, handling escaped delimiters, double quotes from Excel etc. SSIS / BCP is time consuming bloated nonsense when compared to PostgreSQL's COPY FROM etc
Aaron Nelson commented
I do like this idea.
An easy work-around is to use Invoke-SqlCmd and pipe the results to Export-Csv (PowerShell cmdlets).
Eli Massey commented
I would suggest it be FOR RAWTEXT with options like Delimiter (comman, tab etc) and definitely QUOTE is useful
This is a great suggestion. I would love to see this as well.
Really would like this as well, but it MUST have the option to not to QUOTE fields, unlike in BCP
This would make life a lot easier
Ra Osolage (sqlkiter) commented
I completely agree with this request. We've been using sqlcmd a lot for data exports to CSV, but it always has the 2nd line with a whole bunch of dashes. Perhaps somewhere in the consideration for this request, you can include sqlcmd to honor FOR CSV or something.
Talley Ouro commented
How about FOR(format_type='json',lang='en',max_record='12000',auto_format='true',export_file='c:\export\data.json',open_with_default_program='true') for all of them(XML, JSON, CVS etc..)? lang(get language from user computer) should be defaulted to 'en',max_record=optional,auto_format defaulted to true,export_file should be optional and open_with_default_program should be defaulted to false.
Key here would be to comply with the CSV rules; notably honoring CrLf's when they appear within quoted block.
Never quite understood why SQL Server didn't/hasn't natively transform from and into CSV. Sure BCP does it, sort of, and one can craft an xsl transformation or directly code an extract aka DTS/SSIS but these methods all suffer from externalizing the transformation.