therefore looks at line 2 and sees that there are 75 fields, and
therefore internally defines a line as 75 fields. It then goes back to
line 1 in order to start looking for line 2, determining that line 2
will come after the first 75 fields it finds. Instead of that putting
it at the *beginning* of my line 2 as I would want, it starts counting
with the four fields of line 1 and then another 71 fields into line 2,
and considers that point the "line end".
Erland, thanks much for the offer, and I'm attaching a sample file with
the first header row and then 3 rows of (approximately) 75 fields each.
I'd be very grateful for anything you can do.
In article <Xns97CC2D50F29Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> 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.
>
>On Tue, 23 May 2006 13:30:19 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
>therefore looks at line 2 and sees that there are 75 fields, and
>therefore internally defines a line as 75 fields. It then goes back to
>line 1 in order to start looking for line 2, determining that line 2
>will come after the first 75 fields it finds. Instead of that putting
>it at the *beginning* of my line 2 as I would want, it starts counting
>with the four fields of line 1 and then another 71 fields into line 2,
>and considers that point the "line end".
BCP checks to see what the field terminator - usually a tab or comma -
is for the first field. It then reads the file, byte by byte, until
it comes to that terminator. Then it continues for field 2, field 3,
and so forth. When it looks at field 4 in the file the terminator it
is looking for is still the comma or whatever, so it continues right
past the carriage return and line feed and finds the column terminator
of the first field of the second line. It then continues along the
second line, counting its way up to its 75th field. THAT field it
knows does not end in a comma, but in a carriage return / line feed
pair. SO, it keeps reading to the end of line 2 to find the next
CR/LF, and THAT is the end of what BCP sees as line 1. After that is
line 2, so that is where it starts based on the /F2 parameter.
In reaching the end of line 2, and considering it to be the end of
line 1, the first three fields are the first three on the first line.
What BCP sees as the fourth field is the last field of the first line,
the CR/LF of the first line, and the first field of the second line.
BCP then works through the second line, treating field 2 as 5, 3 as 6,
and so forth, until it looks for field 75. What BCP sees as field 75
will be field 72, 73, 74 and 75 all strung together.
Roy Harvey
Beacon Falls, CT|||On Tue, 23 May 2006 13:30:19 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:
>Erland, thanks much for the offer, and I'm attaching a sample file with
>the first header row and then 3 rows of (approximately) 75 fields each.
>I'd be very grateful for anything you can do.
Using your sample data file, the following two commands split it into
two files that BCP should be able to handle. Of course it only works
if the rows are all prefixed with H and O as in the sample data.
findstr /B "H" forposting.txt > H.txt
findstr /B "O" forposting.txt > O.txt
I'd never used findstr before, thanks for prompting me to add a new
trick to the loolbox!
Roy Harvey
Beacon Falls, CT|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks, guys. Is my understanding correct then: BCP sees the /F2 and
> therefore looks at line 2 and sees that there are 75 fields,
Nonono! BCP does not think in lines! As Roy said, it thinks in fields.
Keep in mind that BCP can be used to import binary files as well, and
it works in a strictly binary way. The lines are only there to mislead
you. :-)
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
No comments:
Post a Comment