Hi
I am running the following BCP command to extract data from a table test_stu

C:\test>bcp temp..test_stu out c:\test\out\test.rpt /S SERVER /U USER /P
password /f c:\test\test.fmt

create table test_stu
(s_name char (6)
s_year int (4)
s_mean varchar (10)
s_median varchar (10)
s_standard_dev varchar (10)
s_rec_no smallint (2)
s_number_up varchar (10)
s_number_down varchar (10)
)


format file
test.fmt

8.0
8
1 SQLCHAR 0 6 "/t" 1 s_name ""
2 SQLCHAR 0 8 "/t" 2 s_year ""
3 SQLCHAR 0 5 "/t" 3 s_mean ""
4 SQLCHAR 0 5 "/t" 4 s_median ""
5 SQLCHAR 0 5 "/t" 5 s_standard_dev ""
6 SQLCHAR 0 4 "/t" 6 s_record_no ""
7 SQLCHAR 0 4 "/t" 7 s_number_up ""
8 SQLCHAR 0 4 "\r\n" 8 s_number_down ""


I get a tab formatted output file. Is it possible to create a fixed field
output file using the format file eg.

Label length position
s_name 6 1 - 7

s_year 4 8 - 11
s_mean 5 13 - 17

s_median 5 19 - 23

s_standard_dev. 5 25 - 30

s_record_no 4 31 - 34

s_number_up 4 36 - 39

s_number_down 4 41 - 44



Ajay

ajmister (ajmister@optonline.net) writes:
> format file
> test.fmt
>
> 8.0
> 8
> 1 SQLCHAR 0 6 "/t" 1 s_name ""
> 2 SQLCHAR 0 8 "/t" 2 s_year ""
> 3 SQLCHAR 0 5 "/t" 3 s_mean ""
> 4 SQLCHAR 0 5 "/t" 4 s_median ""
> 5 SQLCHAR 0 5 "/t" 5 s_standard_dev ""
> 6 SQLCHAR 0 4 "/t" 6 s_record_no ""
> 7 SQLCHAR 0 4 "/t" 7 s_number_up ""
> 8 SQLCHAR 0 4 "\r\n" 8 s_number_down ""
>
>
> I get a tab formatted output file. Is it possible to create a fixed field
> output file using the format file eg.

Yes. Specify "" as the column terminator, and specify the column lengths
appropriately. In the example of how your fixed-length file would look
like, you have s_year as 4 characters long, but the in the format file
above, you have 8.


--
Erland Sommarskog, SQL Server MVP, [email]sommar@algonet.se[/email]

Books Online for SQL Server SP3 at
[url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]