April 7, 2005 at 9:15 pm
Hi
I am new to SQL and would like assistance with BCP and format file
I have a table
create table tmp_sales
( c_code char(6) NOT NULL,
c_type char(6) NOT NULL,
c_perio char(3) NOT NULL,
c_prd_yr int NULL,
c_prd_mon smallint NULL,
c_prd_num char(1) NULL,
c_no_sold_1w char(4) NULL,
c_no_sold_1m char(4) NULL,
c_sale_1w numeric(12) NULL,
c_sale_1m numeric(12) NULL
)
insert into tmp_sales values ("ABC","Heavy","ANN",2005,"03","1","n/a","4","
",3000.00)
insert into tmp_sales values
("XYZ","Heavy","ANN",2005,"03","1","2","4",1500.00 ,3000.00)
insert into tmp_sales values ("JOE","Heavy","ANN",2005,"03","1","n/a","10","
",5000.00)
go
my format file is (sales.fmt)
6.0
10
1 SQLCHAR 0 6 " " 1 c_code
2 SQLCHAR 0 6 " " 2 c_type
3 SQLCHAR 0 3 " " 3 c_period
4 SQLCHAR 0 4 " " 4 c_prd_yr
5 SQLCHAR 0 2 " " 5 c_prd_mon
6 SQLCHAR 0 2 " " 6 c_prd_num
7 SQLCHAR 0 4 " " 7 c_no_sold_1w
8 SQLCHAR 0 4 " " 8 c_no_sold_1m
9 SQLCHAR 0 12 " " 9 c_sale_1w
10 SQLCHAR 0 12 "\r " 10 c_sale_1m
>bcp "tmp_sales" out sales.rpt -S DATABASE -U user -P pswd -f sales.fmt
I am tring to output the file in the following format
1 Field size start end
2 code 6 1 6
3 Type 6 8 13
4 Period 3 15 17
5 Period Yr 4 19 22
6 Period Mon 2 24 25
7 Period Num 1 27 27
8 Sold 1Week 4 29 32
9 Sold 1Month 4 34 37
10 Sold 1Week 12 39 50
11 Sold 1Week 12 52 63
But the output does not match the format file, fields are shifting to the left
after field 7 to
1 Field size start end
2 code 6 1 6
3 Type 6 8 13
4 Period 3 15 17
5 Period Yr 4 19 22
6 Period Mon 2 24 25
7 Period Num 1 27 27
8 Sold 1Week 4 29 31
9 Sold 1Month 4 32 34
10 Sold 1Week 12 36 47
11 Sold 1Week 12 48 59
Any suggestions
Ajay
April 8, 2005 at 11:46 am
You can make a view and transform in view each field to CHAR
select
c_code,
c_type,
c_perio,
CAST(LTRIM(STR(c_prd_yr int)) as CHAR(4)) ,
CAST(LTRIM(STR(c_prd_mon)) as CHAR(2)) ,
.....
)
or make 1 field by adding them in DB
Vasc
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply