Formatfile shortcut?

[php newbie]

I was searching the group for posts on the subject of format files and
I came across a message by Erland Sommarskog dated 2003-07-07 with
subject line "Re: BCP Format File". In this example, he used a
FORMATFILE where the "prefix length" and "host file data length"
fields were both zero for all columns.

This convention seems to simplify the task of creating format files.
Is this shortcut generally applicable or was it a special case? Does
this mean that SQL Server can always establish the actual lengths in
the data file by looking up the destination table's schema
information?

[Erland Sommarskog]

php newbie (ne**********@yahoo.com) writes:

I was searching the group for posts on the subject of format files and
I came across a message by Erland Sommarskog dated 2003-07-07 with
subject line "Re: BCP Format File". In this example, he used a
FORMATFILE where the "prefix length" and "host file data length"
fields were both zero for all columns.

This convention seems to simplify the task of creating format files.
Is this shortcut generally applicable or was it a special case? Does
this mean that SQL Server can always establish the actual lengths in
the data file by looking up the destination table's schema
information?

No. The format file descibes the data file. It does not describe the
database columns. All there is a mapping from the data-file fields to
the database columns. (Which is the column numbers. The column names
in the BCP file are informational only.)

There are three ways to describe a field in a format file:
o The file itself has the length of the field, and the format file
describes the length of this length information: 1, 2 or 4 bytes.
o Fixed length.
o A certain character sequence acts as terminator, and this terminator
can thus not be part of the data itself.

In the format file I posted, I used a termintor. (I have to assume,
as I don't remember that particular post.)

You can mix these methods in the same format file, and use one method
for one column, and another method for another column. I have to admit
that I don't know the significance of using two methods for the same
column, but I know that I have not always been successful with using
0 for the column length, although I have had a terminator.

The BCP command offers a couple of options that relieves you from having
to write a format file, and when you use these options, BCP does look
at the schema for the target table. But you can see these options as
shortcuts for particular format-file definitions.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[php newbie]

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

There are three ways to describe a field in a format file:
o The file itself has the length of the field, and the format file
describes the length of this length information: 1, 2 or 4 bytes.
o Fixed length.
o A certain character sequence acts as terminator, and this terminator
can thus not be part of the data itself.

In the format file I posted, I used a termintor. (I have to assume,
as I don't remember that particular post.)

Hello Erland,

Thanks for the info. For reference, the post was this:
http://groups.google.com/groups?q=sq...7.0.0.1&rnum=2

I am primarily interested in data files that are delimited with a
special character, like a tab or a comma. So, essentially, if I use
delimited data files, I do not need to calculate field lengths and put
them in the format file. Is this an accurate rephrase of your
suggestion? I would like to use the same format file with different
database tables (which have similar structure but different column
lengths for names and addresses.)

Thanks again!

[Erland Sommarskog]

php newbie (ne**********@yahoo.com) writes:

I am primarily interested in data files that are delimited with a
special character, like a tab or a comma. So, essentially, if I use
delimited data files, I do not need to calculate field lengths and put
them in the format file. Is this an accurate rephrase of your
suggestion? I would like to use the same format file with different
database tables (which have similar structure but different column
lengths for names and addresses.)

Yes, I would expect that to work.

But as I said, I have not always been successful, and it has happened
that I have had to throw in a length specification. Note however that
that specification relates to the host-data file, not the database
table.

Note also, that is the host data is too long with regards to the database
columns, you are likely to get the error "String or binary data would be
truncated".
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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