BCP question

Hello All
I am trying to use the BCP utility. Here is what I did. From the dos prompt
I ran
bcp HPDELGt out C:\eldorado\elbcp.txt -SMyserver -Uusername -Ppassword
which generated a bcp.fmt file
then I ran
bcp HPDELGt in C:\elbcp.txt -fC:\bcp.fmt -SMyserver -Uusername -Ppassword
But it gives me a message that 0 rows copied - below is .fmt file - the
input file is a comma separated file. I would like to do it with a fixed
length file which would have no commas. how would I do that
8.0
68
1 SQLCHAR 0 3 "," 1 PUNBR
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 3 "," 2 GRNBR
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 9 "," 3 ESSN
SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 2 "," 4 SEQ
SQL_Latin1_General_CP1_CI_AS
5 SQLDATETIME 0 8 "," 5
EFFDATE ""
6 SQLCHAR 0 1 "," 6 STATUS
SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 1 "," 7
SELECT1 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 1 "," 8
SELECT2 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 1 "," 9
SELECT3 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 1 "," 10
SELECT4 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 1 "," 11
SELECT5 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 1 "," 12
SELECT6 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 1 "," 13
SELECT7 SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 1 "," 14
SELECT8 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 1 "," 15
SELECT9 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 1 "," 16
SELECT10 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 1 "," 17
SELECT11 SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 1 "," 18
SELECT12 SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 1 "," 19
SELECT13 SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 1 "," 20
SELECT14 SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 1 "," 21
SELECT15 SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 10 "," 22 BEN1
SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 10 "," 23 BEN2
SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 10 "," 24 BEN3
SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 10 "," 25 BEN4
SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 10 "," 26 BEN5
SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 10 "," 27 BEN6
SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 10 "," 28 BEN7
SQL_Latin1_General_CP1_CI_AS
29 SQLDATETIME 1 8 "," 29
TRMDATE ""
30 SQLCHAR 0 1 "," 30
PRODUCT1 SQL_Latin1_General_CP1_CI_AS
31 SQLCHAR 0 1 "," 31
PRODUCT2 SQL_Latin1_General_CP1_CI_AS
32 SQLCHAR 0 1 "," 32
PRODUCT3 SQL_Latin1_General_CP1_CI_AS
33 SQLCHAR 0 1 "," 33
PRODUCT4 SQL_Latin1_General_CP1_CI_AS
34 SQLCHAR 0 1 "," 34
PRODUCT5 SQL_Latin1_General_CP1_CI_AS
35 SQLCHAR 0 1 "," 35
PRODUCT6 SQL_Latin1_General_CP1_CI_AS
36 SQLCHAR 0 1 "," 36
PRODUCT7 SQL_Latin1_General_CP1_CI_AS
37 SQLCHAR 0 1 "," 37
PRODUCT8 SQL_Latin1_General_CP1_CI_AS
38 SQLCHAR 0 1 "," 38
PRODUCT9 SQL_Latin1_General_CP1_CI_AS
39 SQLCHAR 0 1 "," 39
PRODUCT10 SQL_Latin1_General_CP1_CI_AS
40 SQLCHAR 0 1 "," 40
PRODUCT11 SQL_Latin1_General_CP1_CI_AS
41 SQLCHAR 0 1 "," 41
PRODUCT12 SQL_Latin1_General_CP1_CI_AS
42 SQLCHAR 0 1 "," 42
PRODUCT13 SQL_Latin1_General_CP1_CI_AS
43 SQLCHAR 0 1 "," 43
PRODUCT14 SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 0 1 "," 44
PRODUCT15 SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 0 1 "," 45
COBFLAG1 SQL_Latin1_General_CP1_CI_AS
46 SQLCHAR 0 1 "," 46
COBFLAG2 SQL_Latin1_General_CP1_CI_AS
47 SQLCHAR 0 1 "," 47
COBFLAG3 SQL_Latin1_General_CP1_CI_AS
48 SQLCHAR 0 1 "," 48
COBFLAG4 SQL_Latin1_General_CP1_CI_AS
49 SQLCHAR 0 1 "," 49
COBFLAG5 SQL_Latin1_General_CP1_CI_AS
50 SQLCHAR 0 1 "," 50 PEND
SQL_Latin1_General_CP1_CI_AS
51 SQLCHAR 0 3 "," 51 PNDRS
SQL_Latin1_General_CP1_CI_AS
52 SQLCHAR 0 1 "," 52
COBTYPE1 SQL_Latin1_General_CP1_CI_AS
53 SQLCHAR 0 1 "," 53
COBTYPE2 SQL_Latin1_General_CP1_CI_AS
54 SQLCHAR 0 1 "," 54
COBTYPE3 SQL_Latin1_General_CP1_CI_AS
55 SQLCHAR 0 1 "," 55
COBTYPE4 SQL_Latin1_General_CP1_CI_AS
56 SQLCHAR 0 1 "," 56
COBTYPE5 SQL_Latin1_General_CP1_CI_AS
57 SQLCHAR 0 8 "," 57 PCP1
SQL_Latin1_General_CP1_CI_AS
58 SQLCHAR 0 8 "," 58 PCP2
SQL_Latin1_General_CP1_CI_AS
59 SQLCHAR 0 3 "," 59 COBCR1
SQL_Latin1_General_CP1_CI_AS
60 SQLCHAR 0 3 "," 60 COBCR2
SQL_Latin1_General_CP1_CI_AS
61 SQLCHAR 0 3 "," 61 COBCR3
SQL_Latin1_General_CP1_CI_AS
62 SQLCHAR 0 3 "," 62 COBCR4
SQL_Latin1_General_CP1_CI_AS
63 SQLCHAR 0 3 "," 63 COBCR5
SQL_Latin1_General_CP1_CI_AS
64 SQLCHAR 0 3 "," 64 COBLT1
SQL_Latin1_General_CP1_CI_AS
65 SQLCHAR 0 3 "," 65 COBLT2
SQL_Latin1_General_CP1_CI_AS
66 SQLCHAR 0 3 "," 66 COBLT3
SQL_Latin1_General_CP1_CI_AS
67 SQLCHAR 0 3 "," 67 COBLT4
SQL_Latin1_General_CP1_CI_AS
68 SQLCHAR 0 3 "\n" 68 COBLT5
SQL_Latin1_General_CP1_CI_ASRahul Chatterjee (rahul@.benesysinc.com) writes:
> I am trying to use the BCP utility. Here is what I did. From the dos
> prompt I ran
> bcp HPDELGt out C:\eldorado\elbcp.txt -SMyserver -Uusername -Ppassword
> which generated a bcp.fmt file
> then I ran
> bcp HPDELGt in C:\elbcp.txt -fC:\bcp.fmt -SMyserver -Uusername -Ppassword
> But it gives me a message that 0 rows copied
Maybe I'm pointing out something irrelevant, but the BCP IN does not have
the same directory for the file as the BCP out command.
> - below is .fmt file - the
> input file is a comma separated file. I would like to do it with a fixed
> length file which would have no commas. how would I do that
You replace "," with "". There are already length indicators in your
format file.
Look BCP in Books Online. At the end of that page there is a link to
a page about format files.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp