BCP format file

Has anyone successfully used a BCP format file to import from a data
file with fewer fields than there are columns in the table you're
importing into? I've been reading the Books Online help and trying to
manipulate this format file for hours and am getting nowhere. BCP tells
me "Starting copy..." but then "0 rows copied" and for the life of me I
can't figure out why. Is there any additional documentation (or war
stories!) anywhere?Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Has anyone successfully used a BCP format file to import from a data
> file with fewer fields than there are columns in the table you're
> importing into? I've been reading the Books Online help and trying to
> manipulate this format file for hours and am getting nowhere. BCP tells
> me "Starting copy..." but then "0 rows copied" and for the life of me I
> can't figure out why. Is there any additional documentation (or war
> stories!) anywhere?
The format file has a couple of columns that describes the fields in the
text file.
Column 1 - Field number in file, normally they run from 1 to the number
you have on line2 in the file.
Column 2 - Data type. Always SQLCHAR or SQLNCHAR for character data.
Column 3 - Prefix length. Only used with binary files.
Column 4 - Field length. Used when you have fixed-length fields.
Column 5 - Field terminator. Note that the row terminator is really
only the field terminator for the last field.
Column 6 - Column number in the table, starting on 1. 0 means "don't
import this field". Thus importing only some columns is not very
difficult.
Column 7 - Normally used for the column name, but BCP does not look at
this data. It's the column number that counts.
Column 8 - Collation, can be set to "", unless you want some conversion.
Important to understand is that BCP always reads the file as if it was
binary. BCP reads bytes for field 1, until it finds the end of that
column, by prefix length, fixed length or terminator. Then it goes on
to field2 etc, until it starts over. For BCP newline has no particular
significance, so if it gets out of sync for some reason, there is
way for it to resync.
If the above does not help you, please post:
o CREATE TABLE statement for your table.
o The format file.
o A sample data file, preferrably in attachment to avoid damage
in news transport.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. I just imported some rows with a format file, but am having
difficulty importing a date field correctly from my data file, which is
four fields delimited by tabs:
FX 2006-05-04 10:10 1103SWLSO1 XYZ
In the table I'm importing into, these columns are all CHAR except the
second which is DATETIME.
In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
Yet in the table that second field gets read in as:
0639-03-09 702:16:41.537
Help would be much appreciated.
In article <Xns97BAF3EA29E15Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> The format file has a couple of columns that describes the fields in the
> text file.
> Column 1 - Field number in file, normally they run from 1 to the number
> you have on line2 in the file.
> Column 2 - Data type. Always SQLCHAR or SQLNCHAR for character data.
> Column 3 - Prefix length. Only used with binary files.
> Column 4 - Field length. Used when you have fixed-length fields.
> Column 5 - Field terminator. Note that the row terminator is really
> only the field terminator for the last field.
> Column 6 - Column number in the table, starting on 1. 0 means "don't
> import this field". Thus importing only some columns is not very
> difficult.
> Column 7 - Normally used for the column name, but BCP does not look at
> this data. It's the column number that counts.
> Column 8 - Collation, can be set to "", unless you want some conversion.
> Important to understand is that BCP always reads the file as if it was
> binary. BCP reads bytes for field 1, until it finds the end of that
> column, by prefix length, fixed length or terminator. Then it goes on
> to field2 etc, until it starts over. For BCP newline has no particular
> significance, so if it gets out of sync for some reason, there is
> way for it to resync.
> If the above does not help you, please post:
> o CREATE TABLE statement for your table.
> o The format file.
> o A sample data file, preferrably in attachment to avoid damage
> in news transport.
>|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks. I just imported some rows with a format file, but am having
> difficulty importing a date field correctly from my data file, which is
> four fields delimited by tabs:
> FX 2006-05-04 10:10 1103SWLSO1 XYZ
> In the table I'm importing into, these columns are all CHAR except the
> second which is DATETIME.
> In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
> Yet in the table that second field gets read in as:
> 0639-03-09 702:16:41.537
I said this in my previous post:
I should have clarified: the data type in the *file*. Anything else
than SQLCHAR or SQLNCHAR means that you have a binary file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Rick,
If you have SQLDATETIME and length 16 in your format file, then
you have told bcp to expect binary data, not text. This is also suggested
by the fact that 702:16:41.537 is the time that could be represented by
misconverted character data. That specific time would be displayed if
a datetime held the 0x2D3531A5 as its last 4 bytes, and this is the
ASCII representation of '-51' More likely values to come from date
strings are similar. If the ascii representation of '2006-05-04' were read
into a datetime, the time portion would correspond to 701:58:33:483.
Or in short, as Erland said, change your format file to say SQLCHAR
(or SQLNCHAR, as appropriate) instead of SQLDATETIME.
Steve Kass
Drew University
Rick Charnes wrote:
>Thanks. I just imported some rows with a format file, but am having
>difficulty importing a date field correctly from my data file, which is
>four fields delimited by tabs:
>FX 2006-05-04 10:10 1103SWLSO1 XYZ
>In the table I'm importing into, these columns are all CHAR except the
>second which is DATETIME.
>In the .FMT file field #2 is defined as SQLDATETIME with a length of 16.
>Yet in the table that second field gets read in as:
>0639-03-09 702:16:41.537
>Help would be much appreciated.
>In article <Xns97BAF3EA29E15Yazorman@.127.0.0.1>, esquel@.sommarskog.se
>says...
>