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 1:45 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:50 AM
Points: 550, Visits: 1,040
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
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:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:50 AM
Points: 550, Visits: 1,040
Sara_DBA (8/13/2008)
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


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

With the -t you specify the delimiter.
Post #551674
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:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:50 AM
Points: 550, Visits: 1,040
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
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 09, 2014 10:55 AM
Points: 1, Visits: 8
Thanks!!!
That works for me. Adding "|" in the line.

Regards
Post #1048032
Posted Friday, March 08, 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