Developing Matt

My Technical Journal

Export My Data to a CSV with a pipe (|) dream

with one comment

2009_02_20_Flowers 018

In an attempt to generate some test data I wanted an easy way to create a csv file with pipes instead of commas.  Naturally I turn to sql when it comes to making icky processes easier.  So I figured there was some magical t-sql out there that would enable me to write to a file with pipes instead of commas.  I had a really difficult time finding information about moving data from sql to a text file with a pipe.  But…

Turns out there is.  Nigel Rivett has a great post on creating csv files with BCP.  That link is here

Apparently, there is a way to use the openrowset method to save the data (to an existing file) and there is a forum discussion about it here

Jonathan Rabson touched on some great ideas using both methods that he outlined here.  Most of his post was regarding bringing data into sql server though.  So it didn’t quite get me to where I needed. 

But these three guys didn’t top Caroline’s suggestion who works next to me.  She looked for a non-t-sql method and found one. 

tools….options…query results…sql server…results to text. 

Make the output format ‘custom delimiter’ and make it whatever whacked out delimiter you can think of.  So now, when you open a new window this setting will take effect and you can execute results to file (little button on the toolbar or Ctrl+Shift+F.  This is for SSMS 2008)

Thanks Caroline!

Advertisements

Written by matt

March 11, 2010 at 7:44 pm

Posted in Sql Server, T-Sql

Tagged with ,

One Response

Subscribe to comments with RSS.

  1. –Generate BCP DOS command to export pipe delimited data.

    DECLARE @BCPCommandString VARCHAR(8000)
    DECLARE @FilePath VARCHAR(1000)
    DECLARE @FileName VARCHAR(1000)
    DECLARE @SPName VARCHAR(1000)
    DECLARE @ServerName VARCHAR(1000)
    DECLARE @tSQL VARCHAR(1000)

    SET @ServerName = ‘myServer’
    SET @tSQL = ‘Select top 10 * from myTable’
    SET @FilePath = ‘C:\temp\’
    SET @FileName = ‘myFileName.tmp’

    SET @BCPCommandString = ‘bcp “‘ + @tSQL + ‘” queryout ‘;

    SET @BCPCommandString = @BCPCommandString + @FilePath + @FileName;

    SET @BCPCommandString = @BCPCommandString + ‘ -S ‘ + @ServerName;

    SET @BCPCommandString = @BCPCommandString + ‘ -T -c -t^| -U username -P password’;

    SELECT @BCPCommandString;

    hrothenb

    March 8, 2013 at 1:11 pm


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

%d bloggers like this: