BCP Help - Incorrect host-column number found in BCP format-file

I've used BCP before, so I understand how to use it for the most part.
But, I'm trying to do something different.
I created a fmt file from my table. But, what I want is to only import
say column 50. So, this is how I changed it.
8.0
50
50 SQLCHAR 0 510 "\t" 41 COUNTY
That was just a guess.
Originally it was
8.0
108
then all the colums.
The text file I am processing includes all 108 columns.
So, my question is...Is what I am trying to do possible using the same
text file, but only wanting to import 1 column? (Please understand, I
will really be importing about 50 but this is an example)
*** Sent via Developersdex http://www.examnotes.net ***[posted and mailed, please reply in news]
Joey Martin (joey@.infosmiths.net) writes:
> I've used BCP before, so I understand how to use it for the most part.
> But, I'm trying to do something different.
> I created a fmt file from my table. But, what I want is to only import
> say column 50. So, this is how I changed it.
> 8.0
> 50
> 50 SQLCHAR 0 510 "\t" 41 COUNTY
> That was just a guess.
> Originally it was
> 8.0
> 108
> then all the colums.
> The text file I am processing includes all 108 columns.
> So, my question is...Is what I am trying to do possible using the same
> text file, but only wanting to import 1 column? (Please understand, I
> will really be importing about 50 but this is an example)
No, this is perfectly possible. If you already have a format file with
all the 108 fields, just put a 0 after the field delimiter after the
fields you don't care about. This means that this field in the file does
not map to a database column.
A non-zero value, for instance 41 as above, means that you want to
map that field to the 41th column. The name that follows the number
is informational only, and carries no meaning.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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