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”
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)
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.
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:
- SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
- SQL SERVER – FIX – Msg 4864, Level 16, State 1 – Bulk load data conversion error
Reference: Pinal Dave (https://blog.sqlauthority.com)
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?
Is there any benefit in using this over the right click and export to csv method?
Nick
I have got issue with BCP out that it doesn´t support UTF8/Unicode from SQL2008. Do you got a workaround for it?
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).
Save result as is saving the output shown in the results.
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
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.
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)
Glad it helped you Jon.
The need to open a new Query Window was the thing I needed. First time I had seen that mentioned by anyone. Thanks for mentioning it!
Chris – I am glad that you were able to find this blog
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
Choose custom delimited and use pipe.
can we automate this i mean can we do all this process by query..!!!
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
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.