jim_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
>