Column-conscious bulk insert

I am trying to bulk insert a text file. The file has fixed-length fields
with no field terminators. BOL says that field terminators are only
needed when the data does *not* contain fixed-length fields, which
implies they are optional -- so I made a format file without any (two
consecutive tabs with nothing between them). The following message
resulted:

Server: Msg 4827, Level 16, State 1, Line 1
Could not bulk insert. Invalid column terminator for column number
1 in format file

That sounds like I am required to have some sort of terminator in the
format file, even though there aren't any in the data file. Unfortunately,
the documentation on bcp/bulk copy and format files does not directly
address this point, and I would appreciate some help.

BTW, putting '""' (empty string) for the terminator also leads to errors,
with the first field overflowing -- bulk insert can't figure out where
it ends.

Thanks,
Jim Geissman
Countrywide Home Loansjim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0401281622.34aa0e42@.posting.google.com>...
> I am trying to bulk insert a text file. The file has fixed-length fields
> with no field terminators. BOL says that field terminators are only
> needed when the data does *not* contain fixed-length fields, which
> implies they are optional -- so I made a format file without any (two
> consecutive tabs with nothing between them). The following message
> resulted:
> Server: Msg 4827, Level 16, State 1, Line 1
> Could not bulk insert. Invalid column terminator for column number
> 1 in format file
> That sounds like I am required to have some sort of terminator in the
> format file, even though there aren't any in the data file. Unfortunately,
> the documentation on bcp/bulk copy and format files does not directly
> address this point, and I would appreciate some help.
> BTW, putting '""' (empty string) for the terminator also leads to errors,
> with the first field overflowing -- bulk insert can't figure out where
> it ends.
> Thanks,
> Jim Geissman
> Countrywide Home Loans

Jim,

Just a thought, but have you tried using the "-c" flag with the BCP IN
command instead of using a format file? Create a target table where
the column widths exactly match the fields in your file, and give it a
try. ("-c" takes no parameters). Assuming you've got record
terminators in the correct place, I think this should work.
Personally, I hate using format files and avoid them like the plague
if I can.

bcp <db>..<target_tbl> in <datafile> -Uuser -Ppass -Sserver -c

Phil|||Thanks, Phil.

I wish that were true. However it seems that -c assumes \t (tab)
separators. At least it doesn't work. Putting in -t (specify separator
but don't provide one) causes bcp to just sit there and do nothing.
I'm going to use DTS and specify column by column where they all end.
It's such a waste of effort, though, because the data is from the Census
and the input exactly matches the table, character by character.

Thanks again
Jim

> Jim,
> Just a thought, but have you tried using the "-c" flag with the BCP IN
> command instead of using a format file? Create a target table where
> the column widths exactly match the fields in your file, and give it a
> try. ("-c" takes no parameters). Assuming you've got record
> terminators in the correct place, I think this should work.
> Personally, I hate using format files and avoid them like the plague
> if I can.
> bcp <db>..<target_tbl> in <datafile> -Uuser -Ppass -Sserver -c
> Phil|||Jim Geissman (jim_geissman@.countrywide.com) writes:
> I am trying to bulk insert a text file. The file has fixed-length fields
> with no field terminators. BOL says that field terminators are only
> needed when the data does *not* contain fixed-length fields, which
> implies they are optional -- so I made a format file without any (two
> consecutive tabs with nothing between them). The following message
> resulted:
> Server: Msg 4827, Level 16, State 1, Line 1
> Could not bulk insert. Invalid column terminator for column number
> 1 in format file
> That sounds like I am required to have some sort of terminator in the
> format file, even though there aren't any in the data file.
> Unfortunately, the documentation on bcp/bulk copy and format files does
> not directly address this point, and I would appreciate some help.

You must specify the separator in quotes, but it can be the empty
string, "". The tabs does not mean anything to BCP, as far as I know.
At least it never complain about lack of tabs in my format files.

>BTW, putting '""' (empty string) for the terminator also leads to errors,
>with the first field overflowing -- bulk insert can't figure out where
>it ends.

What about posting:

o CREATE TABLE statement for your table.
o The format file. (The one with "" in it.)
o A sample file to bulk-load.

That makes it a little easier to have a guess of what is going on.

If the data file is more than 75 characters wide, you are probably
better of putting it an attachment.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp