Solved

how to specify delimiter with bcp commad

Posted on 2006-07-20
6
788 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
[X]
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
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with Data Warehouse / Data Marts 4 42
Moving away from Access 2003 adp files 4 49
What is needed to become a DBA? 7 56
Need split for SQL data 7 50
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 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