Pipe Symbol as Column delimiter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Pipe Symbol as Column delimiter

Pipe Symbol as Column delimiter

Rate Topic

Display Mode

Topic Options

Author

Message

Sara_DBA-629440

Posted Wednesday, August 13, 2008 12:42 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, September 28, 2008 11:25 PM
Points: 19, Visits: 93

I need pipe sysmbol as column delimiter., eg.,

FirstName | LastName | Address | Phone

Can anyone explain me how to do this (except +'|'+ option) please? I used +Char(166)+ and export into .dat file. When I open into notepad the sysmbol is different but it is opening in DOS prompt / editor w00t

I need this .dat file should open in notepad also with '|' symbol.

Thanks in advance...Your help is much appreciated...

Sara

A ship in the harbour is safe . . . but that's not what ships were made for. :D

Post #551625

Sara_DBA-629440

Posted Wednesday, August 13, 2008 1:26 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, September 28, 2008 11:25 PM
Points: 19, Visits: 93

Lately I found CHAR(124) will be the Pipe Symbol (|)

Thanks for your timings...:D

Sara

A ship in the harbour is safe . . . but that's not what ships were made for. :D

Post #551645

Wandrag

Posted Wednesday, August 13, 2008 1:45 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 505, Visits: 970

Hi,
What are you using to export your data?
If you use DTS packages you can specify the delimiter when you use the text file as destination. (Vertical bar)
Or with BCP you can also specify the "|"
ex. BCP "Query" queryout FileName -t| -r\r

Post #551653

Sara_DBA-629440

Posted Wednesday, August 13, 2008 2:18 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, September 28, 2008 11:25 PM
Points: 19, Visits: 93

I am exporting records by using Stored procedure. The master table contains many coulmns, I needed to export all master table columns (10 columns) into .dat file,

eg of the master table.,

Column names: FirstName, LastName, Address, Phone, Pin

so all these result set sould be pipe delimited in the .dat file.,

SELECT ISNULL(FirstName,'') + CHAR(124) + ISNULL(LAstName,'')+ CHAR(124) +ISNULL(Address,'')+ CHAR(124) +ISNULL(Phone,'') from table.

Thanks

Sara

A ship in the harbour is safe . . . but that's not what ships were made for. :D

Post #551668

Wandrag

Posted Wednesday, August 13, 2008 2:32 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 505, Visits: 970

Sara_DBA (8/13/2008)

Try:
master..xp_cmdshell 'BCP table OUT Path-t| -r\r'

With the -t you specify the delimiter.

Post #551674

Sara_DBA-629440

Posted Wednesday, August 13, 2008 2:43 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, September 28, 2008 11:25 PM
Points: 19, Visits: 93

I tried initially and now but the result is same (-t| -r\r)

"The system cannot find the path specified"

Sara

A ship in the harbour is safe . . . but that's not what ships were made for. :D

Post #551678

Wandrag

Posted Wednesday, August 13, 2008 4:05 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 505, Visits: 970

This should work - i'm using it on some of my stuff...

declare @sql varchar(8000)
select @sql = 'bcp DatabaseName..TableName out R:\test\Test.txt -c -T -S' + @@servername + ' -t"|"'
print @sql
exec master..xp_cmdshell @sql

Post #551720

Sara_DBA-629440

Posted Wednesday, August 13, 2008 4:09 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, September 28, 2008 11:25 PM
Points: 19, Visits: 93

I checked and found command should be '-c -T -t "|" '

eg.,

'BCP ' + @TABLE + ' QUERYOUT ' + @DIR + @FILE + '-c -T -t "|" '

Thanks for your help.

Sara

A ship in the harbour is safe . . . but that's not what ships were made for. :D

Post #551722

er_ramirez20-1032551

Posted Friday, January 14, 2011 9:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 9:53 AM
Points: 1, Visits: 7

Thanks!!!
That works for me. Adding "|" in the line.

Regards

Post #1048032

hrothenb

Posted Friday, March 08, 2013 11:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 7:46 AM
Points: 3, Visits: 53

--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;

Post #1428722

« Prev Topic | Next Topic »

Permissions