Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pipe Symbol as Column delimiter Expand / Collapse
Author
Message
Posted Wednesday, August 13, 2008 12:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

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
Posted Wednesday, August 13, 2008 1:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, August 13, 2008 2:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, August 13, 2008 2:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, August 13, 2008 4:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, January 14, 2011 9:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 9, 2014 10:55 AM
Points: 1, Visits: 8
Thanks!!!
That works for me. Adding "|" in the line.

Regards
Post #1048032
Posted Friday, March 8, 2013 11:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 13, 2014 11:14 AM
Points: 4, Visits: 56

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

Add to briefcase

Permissions Expand / Collapse