bcp format file annoyance

Hi All
I'm attempting to import a text file into sql server using bcp via an
appropriate format file. I keep getting the 'Incorrect host-column
number found in BCP format-file' error. I've managed to pare my large
text file down and still generate the same error. My input file is
comma-delimited and my text is qualified by double quotes:
"A/R #","Ordered","Date Billed","Diagnosis","Detail Comment"
"1","12/14/2004","03/15/2005","3219","",
"2","12/10/2004","04/19/2005","3312","test...",
"3","12/12/2004","09/29/2005","3300","",
Note: Every record is terminated by a new line character. A comma also
shows up at the end of every record starting with the second row. Most
fields in the "Detail Comment" column are blank ("").
I generated my format file by importing the txt file using DTS, then
running bcp format, etc. The format file follows (I replaced the
collation with "")
8.0
5
1 SQLCHAR 0 8000 "," 1 A/R # ""
2 SQLCHAR 0 8000 "," 2 Ordered ""
3 SQLCHAR 0 8000 "," 3 Date Billed ""
4 SQLCHAR 0 8000 "," 4 Diagnosis ""
5 SQLCHAR 0 8000 "\n," 5 Detail Comment ""
I think my problems might stem from the trailing comma, which I tried
to allow for in the record terminator. I've also tried "\r\n," as the
record terminator.
Any suggestions are greatly appreciated.
chloraphil@.gmail.com wrote:
> Hi All
> I'm attempting to import a text file into sql server using bcp via an
> appropriate format file. I keep getting the 'Incorrect host-column
> number found in BCP format-file' error. I've managed to pare my large
> text file down and still generate the same error. My input file is
> comma-delimited and my text is qualified by double quotes:
> "A/R #","Ordered","Date Billed","Diagnosis","Detail Comment"
> "1","12/14/2004","03/15/2005","3219","",
> "2","12/10/2004","04/19/2005","3312","test...",
> "3","12/12/2004","09/29/2005","3300","",
> Note: Every record is terminated by a new line character. A comma
> also shows up at the end of every record starting with the second
> row. Most fields in the "Detail Comment" column are blank ("").
> I generated my format file by importing the txt file using DTS, then
> running bcp format, etc. The format file follows (I replaced the
> collation with "")
> 8.0
> 5
> 1 SQLCHAR 0 8000 "," 1 A/R # ""
> 2 SQLCHAR 0 8000 "," 2 Ordered ""
> 3 SQLCHAR 0 8000 "," 3 Date Billed ""
> 4 SQLCHAR 0 8000 "," 4 Diagnosis ""
> 5 SQLCHAR 0 8000 "\n," 5 Detail Comment ""
> I think my problems might stem from the trailing comma, which I tried
> to allow for in the record terminator. I've also tried "\r\n," as the
> record terminator.
> Any suggestions are greatly appreciated.
Did you actually try to put the trailing comma *before* the newline in the
format file? Another issue might be column names with white space etc. in
them.
Kind regards
robert