Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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:

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

311 votes
Sign in
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Erland Sommarskog shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →


Sign in
Sign in with: Microsoft
Signed in as (Sign out)
  • John Zabroski commented  ·   ·  Flag as inappropriate

    @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:

    "aaa","bbb","ccc" CRLF

    For interpretation of "may not", please see this RFC:

  • HeavenCore commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    I would suggest it be FOR RAWTEXT with options like Delimiter (comman, tab etc) and definitely QUOTE is useful

  • John commented  ·   ·  Flag as inappropriate

    Really would like this as well, but it MUST have the option to not to QUOTE fields, unlike in BCP

  • Ra Osolage (sqlkiter) commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

  • Mighty-O commented  ·   ·  Flag as inappropriate

    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.

Feedback and Knowledge Base