Solved

how to specify delimiter with bcp commad

Posted on 2006-07-20
6
785 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now