SQL SERVER – SSMS Trick – Generating CSV file using Management Studio

SQL SERVER - SSMS Trick - Generating CSV file using Management Studio helpink In my previous blog I talked about using SQLCMD to generate a comma separate files for the table data.

Some blog readers reported that why we should use SQLCMD or BCP to generate the file when the same can be achieved using SQL Server Management Studio itself. Well, that was a strong statement and I liked the simplicity of the solution. Here are the steps. A lot of times it is our inability to learn some of the simple techniques present in the tools that we use make us even more productive.

Here are the steps to achieve the same. Go to SQL Server Management Studio > Choose Tools from Menu Bar > Click on Options. Go to “Query Results” > “SQL Server” > Click on “Results to Text”

SQL SERVER - SSMS Trick - Generating CSV file using Management Studio ssms-csv-01

Once we choose “Comma delimited” and hit OK – we will get below kind of output when we eecure a query (Note that we need to open a new Query Window so that the settings are saved)

SQL SERVER - SSMS Trick - Generating CSV file using Management Studio ssms-csv-02

Notice that first column is the column name. If we want to avoid that and wants ONLY the data, then we can customized that in the same place where we have modified the separator setting. If we uncheck “Include column headers in the result set” then we will get only the rows as output.

We can also choose custom delimiter. I have set delimiter as pipe (|) and unchecked the box which I mentioned earlier and below is the output for the same query.

SQL SERVER - SSMS Trick - Generating CSV file using Management Studio ssms-csv-03

As we can see that output is pipe separated and we just have data not column heading. This can be saved and imported again to some other server using bcp command, Bulk Insert command or Import/Export Wizard.

Recommended reading:

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet
Next Post
SQL SERVER – FIX – The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run

Related Posts

16 Comments. Leave new

  • Hi Pinal Dave, thanks for this, I was looking for a way to create CSV file from my SQL results. No how to I export the data from SQL? What format do you recommend?

    Reply
  • Is there any benefit in using this over the right click and export to csv method?

    Nick

    Reply
  • I have got issue with BCP out that it doesn´t support UTF8/Unicode from SQL2008. Do you got a workaround for it?

    Reply
  • Steven Garside
    January 30, 2015 9:20 pm

    Using the “Save Results As..” context menu option in the grid result pane appears to be the best option to save to CSV, as there is the option “Quote strings containing list separators when saving .csv results” in Tools > Options > Query Results > SQL Server > Results to Grid. I’ve tried this in SSMS 2014 – only thing is that it wont quote delimit fields with newlines (effectively the row/record separator).

    Reply
  • i need a pipe line delimited file with column headings. This is an example of the sql that I’m running
    SELECT DISTINCT
    convert (varchar, Ht.HSP_ACCOUNT_ID ) +’|’+
    HT.CPT_CODE
    FROM
    HSP_TRANSACTIONS HT

    what I get is:

    (No column name)
    50017370|3601003
    50017370|62270
    50087558|3601001
    50087558|3601003
    50087558|36558
    50087558|36578
    50087558|47490
    50096423|3601001
    50096423|3601002
    50096423|37204

    what I need is:
    acct_id|cpt_cd
    50017370|3601003
    50017370|62270
    50087558|3601001
    50087558|3601003
    50087558|36558
    50087558|36578
    50087558|47490
    50096423|3601001
    50096423|3601002
    50096423|37204

    Reply
    • In the above blog, the options has something called as custom delimited. You can use a Pipe (|) symbol there and get the output too. This reduces the need to concatenate in your SELECT statement.

      Reply
  • This one line saved me after 30 minutes of frustration. Thanks.

    (Note that we need to open a new Query Window so that the settings are saved)

    Reply
  • Maluth, Thomas
    March 13, 2015 2:08 am

    I have kind of similar issue that I need pipe delimited on my query as I would like to create an excel file from it.

    create table #formitem2 (
    formitemid| bigint,
    itemidentifier| varchar(50),
    itemtext| nvarchar(max),
    parentformitemid| bigint)

    insert into #formitem2
    Select –fv.identifier,fv.title,
    fi.id as formitemid,
    fi.identifier as itemidentifier,
    itemtext,
    parentformitemid
    from formversion fv
    inner join formversionformitem fvfi on fv.id = fvfi.formversionid
    inner join formitem fi on fvfi.formitemid = fi.id
    inner join formseries fs on fv.formseriesid = fs.id and fs.ownerid = 31
    UNION
    select fi.id as formitemid,fi.identifier as itemidentifier,itemtext,parentformitemid
    from formitem fi
    where fi.parentformitemid in (select fi.id from formseries fs
    inner join formversion fv on fs.id = fv.formseriesid
    inner join formversionformitem fvfi on fv.id = fvfi.formversionid
    inner join formitem fi on fvfi.formitemid = fi.id
    Where fs.ownerid = 31 )

    select t.itemidentifier,cast(t.itemtext as char(4000)) as itemText,isnull(fi.Identifier,”) as parentIdentifier
    into work..formitem_IREC_150310a
    from #formitem2 t
    left outer join formitem fi on t.parentformitemid = fi.id

    Reply
  • can we automate this i mean can we do all this process by query..!!!

    Reply
  • My results pane still show as if I have selected column delimited, no matter if I choose text delimited, custom delimited or comma delimited.
    (I am not outputting to file yet until I am comfortable with the results). I am outputting to text in result pane.

    SSMS 2008 R2

    Reply
  • Any idea in SSMS 18.4 how to output the CSV so that each field (or just text fields) automatically have double-quotes – that is without adding a quote command in the query? I have an issue where my JobName column can be a file name or a URL. They potentially may have a delimiter character (comma, semicolon, etc) in the name, which breaks importing the file into Excel. Right now I’m using a custom delimiter (*) but would prefer using the comma.

    Reply

Leave a Reply

Menu