469,080 Members | 1,730 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,080 developers. It's quick & easy.

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 Loans
Jul 20 '05 #1
3 8796
ji**********@countrywide.com (Jim Geissman) wrote in message news:<b8**************************@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
Jul 20 '05 #2
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

Jul 20 '05 #3
Jim Geissman (ji**********@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by perspolis | last post: by
6 posts views Thread by Robert Schuldenfrei | last post: by
19 posts views Thread by Owen T. Soroke | last post: by
3 posts views Thread by TPhelps | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.