Obtaining Pipe Delimited Results from SQL Server using SSMS

In this short Blog post let’s see how to fetch results in a Custom/pipe delimited format file directly from SSMS without any fancy SSIS Packages involved. In fact you can use SSMS for fetching result set as with any delimited value.

As you all know with all the default settings of your SSMS without any options enabled/disabled, we’ll get results of any SELECT Statement in a grid fashion as shown below.

In the above screenshot I pulled few columns from “AdventureWorks” Database with the default Settings. You can copy the results including column headers and paste into excel and create a delimited file. Or if you are looking for CSV or Tab Delimited file, you can achieve without much pain by just right click in the result set and choosing “Save results as” and selecting the required file format. Let’s assume the requirement is to create a Pipe(|) delimited file with column names as the first row in the file, or some other custom delimiter in the file. You can’t see those options while saving the results by default. But, it’s very simple to achieve this just by turning on few options in your SSMS.

Go to Tools->Options->Query Results->SQL Server->Results to Text as shown below

In the top right corner drop down the list and select Custom delimiter as shown below.

Once you choose Custom Delimiter, you’ll have an option to enter the Delimiter as you can see below.(I entered “|” for Pipe Delimited files in this example)

Click Ok and now the trick is to select “Results to Text” in your SSMS while executing the Query and you get a result set with a clean pipe delimiters as you can see below:)

Or choose “results to file” and execute the same to save the results directly to a file. You can see in the below Screenshot where I’ve selected the required option in my SSMS and saving the file as “Pipe”.

Now, I’ve navigated to that folder and opened the file with notepad where I got the file exactly as I was expecting to be as you can see below 🙂

As you can see we don’t need any fancy tools to get custom delimited results…All it takes is exploring our own SSMS  🙂

Hope this helps!

 

 

 

Advertisements

9 comments

  1. I tried this in SQL 2012. Instructions were excellent. But it always behaves as if I selected the column aligned option. Any ideas? I’ve tried different delimiters. I’ve tried results to text and results to file with the delimited option, and it always behaves as the column aligned option.

    1. I have the same problem as Grant Brimhall. “:I’ve tried different delimiters. I’ve tried results to text and results to file with the delimited option, and it always behaves as the column aligned option.”

      Any ideas?

  2. That’s interesting. I’ve got SSMS 2014 on my laptop, Just tested it again and it’s working as expected with my custom delimiter! Just wanted to double check, you are opening a new query pane to execute your query after the option has been set right?

    1. Turns out it needs to be set by options under the query menu. …under output to text.
      Maybe it’s a SSMS 2008 R2 specific difference.

  3. close out SSMS and reopen if you are having issue with the setting working properly, after that worked like a charm!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s