BCP's /F switch

We have a text file in which the first row has four fields and it needs
to get BCP'ed into a table with four columns. All remaining rows are
much longer with 75 fields and get BCP'ed into a table with 75 columns.
I am doing this second BCP execution with the /F2 option to indicate
that the first row should be row 2. Yet when I run it, it skips row 2
(the first of the longer rows) and starts grabbing at row 3. I have a
feeling BCP gets over that first short row and starts grabbing
at the second longer row (really row 3).
Any thoughts about what I could do? Thanks much.BCP is certainly going to have problems with that. We tend to think
of BCP first breaking the data into lines, then the lines into fields.
That isn't what it does, however. BCP goes field by field, and what
we call the line terminator is really just the field terminator of the
last field on the line. When you tell it to skip the first line, it
actually skips the first 75 fields. This takes it well into the
second line, and the 75th field will include everything to the end of
the second line.
I would start by looking for some string in the first line that does
not appear in any of the other lines, or which appears in all the
other lines that does not appear in the first. If something like that
can be found it may be possible to pre-process the data into two files
using the DOS (well not really DOS, the command line that looks like
DOS to us old fogeys) command FIND, and redirection. Something like:
find "some string" input.txt > withstring.txt
find /V "some string" input.txt > withOUTstring.txt
Otherwise, I would write a very simple program to read the file and
write it out to two files as you need. Or perhaps see if DTS parses
lines on a line-then-field basis.
Roy Harvey
Beacon Falls, CT
On Mon, 22 May 2006 17:17:53 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:

>We have a text file in which the first row has four fields and it needs
>to get BCP'ed into a table with four columns. All remaining rows are
>much longer with 75 fields and get BCP'ed into a table with 75 columns.
>I am doing this second BCP execution with the /F2 option to indicate
>that the first row should be row 2. Yet when I run it, it skips row 2
>(the first of the longer rows) and starts grabbing at row 3. I have a
>feeling BCP gets over that first short row and starts grabbing
>at the second longer row (really row 3).
>Any thoughts about what I could do? Thanks much.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> We have a text file in which the first row has four fields and it needs
> to get BCP'ed into a table with four columns. All remaining rows are
> much longer with 75 fields and get BCP'ed into a table with 75 columns.
> I am doing this second BCP execution with the /F2 option to indicate
> that the first row should be row 2. Yet when I run it, it skips row 2
> (the first of the longer rows) and starts grabbing at row 3. I have a
> feeling BCP gets over that first short row and starts grabbing
> at the second longer row (really row 3).
BCP ? BCP never gets , but users of BCP often gets
over it!
I think the best description of BCP is that it reads a *binary* file.
The file may look like a text file to you and me, for BCP it's binary.
When BCP reads the file, it looks the format definition of the first
field as specified by the format file or command-line switches. When
it has found the end of that column, it goes for the next etc. The
row terminator is just the terminator for the last field.
When you say -F2, that is not the second line in the file. It's the second
record in the file according to the format specification. Given your
description, I can guess that will be line 3 in the file. That first
skipped record has an embedded newline in field 4, and some embedded
field terminators in field 75.
As for what you want to do, you will have to split the file. BCP
is not smart enough to handle two tables or two different formats.
Or, hm, maybe you can get away with a single file - but requires
quite some luck. Getting that single line into a table should be
simple. Define a format file for those four fields, and then specify
-L 1, to get only the first row.
Skipping that first line may be more problematic. If you can post a
sample, I might be able to come with something. But no promises.
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