Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to specify delimiter with bcp commad

Posted on 2006-07-20
6
Medium Priority
?
798 Views
Last Modified: 2008-01-09
i want to  create a text file from a table without/with delimeter with specified spaces that i can provide as parameter in bcp command

like

ramdev|sharma      // pipe delimeted
and

ramdev  sharma      // 2 spaces

and i dont want tabs

ramdev (tab space) sharma

thanks


0
Comment
Question by:riteinfotech
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17151925
you can use the file terminator certainly to creat a pipe delimited output...

bcp .... -t!

i'd suggest using the format file options to attempt to get a double space delimiter...
(see Books on line ....)

hth
rushed

it may help to state you sql version/edition
0
 
LVL 25

Expert Comment

by:Mr_Peerapol
ID: 17151931
you can use -t to specify the feild delimiter
0
 
LVL 6

Expert Comment

by:Devil666
ID: 17151942
hi there,

try the following, it is using a trusted connection

bcp "[Dbname]..[TableName]" OUT "[FilePath\FileName.DAT]" -t "[Delimeter]" -c -S"[ServerName]" -T



hth
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:riteinfotech
ID: 17152004
if i want to specify different spaces for every field than what should be exact bcp cpmmand

like i have 6 fields in a table and i want text file like
   1                       2                          3                   4                  5                     6  
ramdev[2 space]sharma[10 space]manager[6 space]26[4 space]25000[10 space]1980

and all teh data from table should be copied in same format,in this case what would the exact bcp command
with systax .
please help
0
 
LVL 6

Expert Comment

by:Devil666
ID: 17152050
you could just use a SQL query in your bcp command to do that,

so instead of using a table name, put your query into a view, and then execute the view
0
 
LVL 25

Accepted Solution

by:
Mr_Peerapol earned 375 total points
ID: 17152053
Why don't you just use queryout option instead?
Then, specify:

SELECT field1+"   "+field2+"             "+field3 FROM your_table

So you can put whatever valid string as your field delimiter.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question