how to specify delimiter with bcp commad

Posted on 2006-07-20
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


ramdev|sharma      // pipe delimeted

ramdev  sharma      // 2 spaces

and i dont want tabs

ramdev (tab space) sharma


Question by:riteinfotech
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 50

Expert Comment

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 ....)


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

Expert Comment

ID: 17151931
you can use -t to specify the feild delimiter

Expert Comment

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


Author Comment

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

Expert Comment

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
LVL 25

Accepted Solution

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.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 52
query analyser in sql server 2016 express edition 2 25
SQL syntax question 6 39
Specify timing interval fro change data 2 54
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

756 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