Pipe Symbol as Column delimiter

Question

Post reply

Pipe Symbol as Column delimiter

Sara_DBA-629440

Right there with Babe

Points: 721
More actions
August 13, 2008 at 12:42 am

#70981

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...
[font="Comic Sans MS"]Sara[/font]
A ship in the harbour is safe . . . but that's not what ships were made for. 😀

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sara_DBA-629440 Right there with Babe Points: 721 More actions · Answer 1

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

Thanks for your timings...:D

[font="Comic Sans MS"]Sara[/font]

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

Sara_DBA-629440 Right there with Babe Points: 721 More actions · Answer 2

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

[font="Comic Sans MS"]Sara[/font]

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

Sara_DBA-629440 Right there with Babe Points: 721 More actions · Answer 3

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

"The system cannot find the path specified"

[font="Comic Sans MS"]Sara[/font]

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

Sara_DBA-629440 Right there with Babe Points: 721 More actions · Answer 4

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

eg.,

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

Thanks for your help.

[font="Comic Sans MS"]Sara[/font]

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

er_ramirez20-1032551 SSC Journeyman Points: 87 More actions · Answer 5

Thanks!!!

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

Regards

hrothenb SSC-Addicted Points: 427 More actions · Answer 6

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