creating text files using bcp

I am using BCP and it works just fine. I get a txt file with tab seperated fields. Can i specify the character positions or lengths of each field so that all my fields line up at the same positions.

Help is appreciated.

I created a stored procedure with the below
declare @.filename varchar(50),
@.bcpcommand varchar(2000)

set @.filename = 'c:\report\media.txt'
print @.filename
set @.bcpcommand = 'bcp "select * from table" queryout "'+ @.filename -U -P'
exec master..xp_cmdshell @.bcpCommandYou could format your select as needed.|||Can u elaborate more ...how do i format my sql to seperate the fields.

like i want one field on position 1-10 and the seond from position 11-40 and so on...

Thanks|||Originally posted by hp1000
Can u elaborate more ...how do i format my sql to seperate the fields.

like i want one field on position 1-10 and the seond from position 11-40 and so on...

Thanks
Just create one long field or as many as you need like this:

select field1+replicate(' ',25-datalength(field1)+...
from table|||I am using

set @.bcpcommand = 'bcp "select medium_name + replicate(' ',10-datalength(medium_name)) from database..report_tbl" queryout "'+ @.filename + '" -U sa -P -c'

I get Line 6: Incorrect syntax near '

How can i escape the single quotes

Please help

Thsi method should work for what i want to do .

Thanks|||Originally posted by snail
Just create one long field or as many as you need like this:

select field1+replicate(' ',25-datalength(field1)+...
from table

I am using the above but it does not work with NULL values . How do i make it to work with null values. Help is very much appreciated.

Thanks|||If you attempt to format your fields within BCP...QUERYOUT you may very quickly reach the limitation on the length of the command line (I don't remember what it is, but you can check.)

What you can do instead, is create a stored procedure where you'd format whatever and whichever way you want, and in your BCP specify a call to that procedure along with QUERYOUT.|||Here's how I do it - much easier to read and manage changes.

Much like a structure or type, I create a temporary table in a stored procedure (TABLE vars won't work, as BCP won;t be able to see them), using CHAR data types, one field for each output field. I have a user-defined function for zero-padding, and one for right-justifying (space padding). I populate that table with all my data, then do a BCP statement consisting of SELECT field1 + field2 + field3 etc.

This gives my the spacing I want, and that one create table at the beginning of the procedure allows me to easily control the number spaces each field gets.

I can post a proc if it would be easier to understand.

-b|||My table def looks like this (for an SAP feed):

CREATE TABLE ##sap_table(sales_organization CHAR(4) NOT NULL,
distribution_channel CHAR(2) NOT NULL,
division CHAR(2) NOT NULL,
filler_1 CHAR(2) NOT NULL,
sold_to CHAR(8) NOT NULL,
delivering_plant CHAR(4) NOT NULL,
profit_center CHAR(10) NOT NULL,
material_code CHAR(10) NOT NULL,
pricing_date CHAR(8) NOT NULL,
charge_type CHAR(4) NOT NULL,
order_reason CHAR(3) NOT NULL,
project_number CHAR(24) NOT NULL,
contract_number CHAR(10) NOT NULL,
purchase_order_number CHAR(12) NOT NULL,
internal_sales_reference CHAR(12) NOT NULL,
usage_type CHAR(3) NOT NULL,
usage_detail CHAR(60) NOT NULL,
requestor_name CHAR(40) NOT NULL,
user_number CHAR(10) NOT NULL,
[user_name] CHAR(40) NOT NULL,
comments CHAR(100) NULL,
quantity CHAR(15) NOT NULL,
unit_of_measure CHAR(3) NOT NULL,
currency CHAR(5) NOT NULL,
extended_amount CHAR(15) NOT NULL,
customer_cost_object CHAR(40) NOT NULL,
cco_flag CHAR(3) NOT NULL,
xy_flag CHAR(1) NOT NULL,
filler_2 CHAR(6) NOT NULL )|||If you want fixed width data, why not use a format file?|||Thanks for all ur feedback . I am using coalesce and datalength functions and it did the trick.

I create a view with the fields i want in the format i want and then do the select on the view with my bcp and it writes to the text file just fine.

Thanks a lot,