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

217 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    8 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • 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