BCP's /F switch - forposting.txt [1/1]

begin 755 forposting.txt
M2"!^,C`P-#`U,C(Q-CHR.3HU.7Y"3$%(0DQ!2'Y865H-"D\@.?C`P,7XR,#`T
M,#0P.'Y:42`P,#$P,#`Q?DI/12!30TA-3T4@.0T]-4$%.62`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.?B!^1$)!?B!^,#`P,#!^-#,@.0D%,($A)
M3$P@.4D0N("`@.("`@.("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("!^0TA%4E)9($A)3$P@.("`@.("`@.("`@.("`@.("`@.("`@.?DY*?B`@.("`@.("`@.
M("`@.("`@.("`@.("`@.?C$T,#$P("`@.("!^3GXR,#`V,#,P-'XR,#`W,#,P,7X@.
M("`@.("`@.("`@.?C$Y,#`P,3`Q?B`@.("`@.("`@.("XP,'Y44GY#3TU004Y9($Y!
M344@.("`@.("`@.("`@.("`@.("`@.("`@.('Y03R!"3U@.@..3DY("`@.("`@.("`@.("`@.
M("`@.("`@.('XQ-R!-04E.(%-4("`@.("!^4TE,5D52(%-04DE.1R`@.("`@.("`@.
M("`@.("`@.("`@.("!^341^,C$Q,3$@.("`@.('Y,:6-E;G-E($AE<F4@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.('X@.("`@.("`V.#`P,'Y/0T-^("`@.("`@.("`@.
M(#!^("`@.?B`@.("`@.("`@.("`P?B`@.('X@.("`@.("`@.,'XD?D]#0WX@.("`@.("`@.
M("`@.,'X@.("`@.("`@.,'XD?B`@.('X@.("`@.("`@.("`@.,'X@.("`@.("`@.,'XD?D]#
M0WX@.("`@.("`Q.#DN,#!^('XP,#`P,'XQ.2!"3%5%($A)3$P@.4D0N("`@.("`@.
M("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^0TA%4E)9($A)
M3$P@.("`@.("`@.("`@.("`@.("`@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.?DY*
M?C`X-3(S("`@.("!^("`@.("`@.-C@.P,#!^("`S-3DN-S@.X-'Y9?DY!?C$@.?C`P
M,#`P?CDY($),544@.059%+B`@.("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^34%$25-/3B`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('Y.2GXP.#4T,"`@.("`@.?EI:60T*3R!^,#`Q?C(P,#(P-#`X?DU.
M(#`P-#`P,#)^3D57($Q)1D4@.1$E35%))0E543U)3("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("!^('Y$0D%^('XP,#`P,'XY,3`@.4TE-4$Q%($%612!!
M4%0@.,D(@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.('Y02514
M4T)54D=(("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^("`@.("`@.("`@.("`@.("`@.
M("`@.("!^,#`Y.3$@.("`@.('Y.?C(P,#8P,S`Q?C(P,#<P,S`Q?B`@.("`@.("`@.
M("!^,3DP,#`Q,#%^("`@.("`@.("`@.+C`P?E12?D-/35!!3ED@.3D%-12`@.("`@.
M("`@.("`@.("`@.("`@.("`@.?E!/($)/6"`Y.3D@.("`@.("`@.("`@.("`@.("`@.("`@.
M?C$W($U!24X@.4U0@.("`@.('Y324Q615(@.4U!224Y'("`@.("`@.("`@.("`@.("`@.
M("`@.('Y-1'XR,3$Q,2`@.("`@.?DQI8V5N<V4@.2&5R92`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.?B`@.("`@.(#<X.3`P?D]#0WX@.("`@.("`@.("`@.,'X@.("!^
M("`@.("`@.("`@.(#!^("`@.?B`@.("`@.("`P?B1^3T-#?B`@.("`@.("`@.("`P?B`@.
M("`@.("`P?B1^("`@.?B`@.("`@.("`@.("`P?B`@.("`@.("`P?B1^3T-#?B`@.("`@.
M(#(P,2XP,'X@.?C`P,#`P?CDY.2!32$]25"!!5D4@.05!4(#)"("`@.("`@.?B`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.('Y-041)4T].("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^,#@.Y.3D@.
M("`@.('X@.("`@.("`W.#DP,'X@.(#,Y,BXU,S<S?EE^3D%^,2!^,#`P,#!^-34P
M(%-(3U)4($A)3$P@.059%($%05"`R0B`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.('Y$3U=30E521T@.@.("`@.("`@.("`@.("`@.("`@.("`@.
M?DY*?C`X.#8U("`@.("!^6E!1#0I/('XP,#%^,C`P,3`T,#A^3U$@.,#`V,#`P
M,WY304T@.34%#3D%-05)!("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('X@.?D1"07X@.?C`P,#`P?E!/($)/6"`Q,C0@.("`@.("`@.("`@.("`@.
M("!^("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.?DU!1$E33TX@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.('Y.2GX@.("`@.("`@.("`@.("`@.("`@.("`@.('XP
M-S$P-"`@.("`@.?DY^,C`P-C`S,#%^,C`P-S`S,#%^("`@.("`@.("`@.('XQ.3`P
M,#$P,7X@.("`@.("`@.("`N,#!^5%)^0T]-4$%.62!.04U%("`@.("`@.("`@.("`@.
M("`@.("`@.("!^4$\@.0D]8(#DY.2`@.("`@.("`@.("`@.("`@.("`@.("!^,3<@.34%)
M3B!35"`@.("`@.?E-)3%9%4B!34%))3D<@.("`@.("`@.("`@.("`@.("`@.("`@.?DU$
M?C(Q,3$Q("`@.("!^3&EC96YS92!(97)E("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("!^("`@.("`@.(#<T,#!^3T-#?B`@.("`@.("`@.("`P?B`@.('X@.("`@.("`@.
M("`@.,'X@.("!^("`@.("`@.(#!^)'Y/0T-^("`@.("`@.("`@.(#!^("`@.("`@.(#!^
M)'X@.("!^("`@.("`@.("`@.(#!^("`@.("`@.(#!^)'Y/0T-^("`@.("`@.(#,Q+C`P
M?B!^,#`P,#!^4$\@.0D]8(#DQ.2`@.("`@.("`@.("`@.("`@.("!^("`@.("`@.("`@.
M("`@.("`@.("`@.("`@.("`@.("`@.("`@.?DI%5TE45"`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.('X@.("`@.("`@.("`@.("`@.("`@.("`@.('Y.2GXP.3DQ,"`@.("`@.?B`@.
M("`@.("`W-#`P?B`@.,C,X+C<P.3=^67Y.07XQ('XP,#`P,'XY,"!(04U05$].
M(%)$+B`@.("`@.("`@.("`@.("`@.?B`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.("`@.
M("`@.("`@.("`@.?DI!4U!%4B`@.("`@.("`@.("`@.("`@.("`@.("`@.("!^3DI^,#<T
-,C$@.("`@.('Y24%$-"@.
`
endRick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> begin 755 forposting.txt
> H ~2004052216:29:59~BLAHBLAH~XYZ
> O ~001~20040408~ZQ 0010001~JOE SCHMOE COMPANY
>...
I'm awfully sorry, but the hour is late, so I need to be quick.
But it appears to me that you should be able to bulk-load this file
without splitting.
First you define a format file for the header, four columns, and
use /L1 to load on the first record according to that defintion.
For the other table, you need to defined a first field in the
table that has "O ~" as terminator. For this field you specify 0
in column number - that is you don't import this field. This field
will always yield the empty string - except in the very first record.
You should not use /F2 when you import the second table.
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'd be very happy to do this without splitting. I'm not sure I
understand when you say I should define a first field in the table that
has "O ~" as terminator. That first field in the table is called
transaction_type and is VARCHAR(2), and it does need to be populated
with all the "O" values.
Am I creating a format file for all the "O" rows, and I specify 0 as the
column number in the format file? But I do need to import that field.
Sorry -- I'm not clear on what you're saying.
In article <Xns97CD7786BAYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> I'm awfully sorry, but the hour is late, so I need to be quick.
> But it appears to me that you should be able to bulk-load this file
> without splitting.
> First you define a format file for the header, four columns, and
> use /L1 to load on the first record according to that defintion.
> For the other table, you need to defined a first field in the
> table that has "O ~" as terminator. For this field you specify 0
> in column number - that is you don't import this field. This field
> will always yield the empty string - except in the very first record.
> You should not use /F2 when you import the second table.
>
>|||Rick C. (blue--nospam--.heron3@.verizon.net) writes:
> Thanks, I'd be very happy to do this without splitting. I'm not sure I
> understand when you say I should define a first field in the table that
> has "O ~" as terminator. That first field in the table is called
> transaction_type and is VARCHAR(2), and it does need to be populated
> with all the "O" values.
> Am I creating a format file for all the "O" rows, and I specify 0 as the
> column number in the format file? But I do need to import that field.
> Sorry -- I'm not clear on what you're saying.
I think you got the message. Yes, the idea is that you would have to
sacrifice that field. It the value is always "O ", then you can always
arrange that with a default value.
If there are records further down in the file that start with a different
letter, then my suggestion cannot work.
I got some weird idea last night before I went to bed, but I will have to
test that first, because it may be a completely dead end. That will not
happen until tomorrow, so for the time being the answer appears to be
"cannot be done without splitting".
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|||In article <Xns97CD611DF49FAYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...

> If there are records further down in the file that start with a different
> letter, then my suggestion cannot work.
The first record is a Header that starts with "H", then there are 2000
Original records that start with "O", and the file ends with one Footer
record that starts with "F". There are three tables, each to receive
one of these three types of records.

> I got some weird idea last night before I went to bed, but I will have to
> test that first, because it may be a completely dead end. That will not
> happen until tomorrow, so for the time being the answer appears to be
> "cannot be done without splitting".
Thanks very much for this help, and I look forward to your new thought.
If that doesn't work out, I'd be very happy to devise a way to simply
split off the Header and Footer records to two new files of one record
each ***while keeping all 2000 of the Original records *in the original-
named file****. (But that's not really a SQL Server issue!) In any
case, everyone's help here has been much appreciated.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> The first record is a Header that starts with "H", then there are 2000
> Original records that start with "O", and the file ends with one Footer
> record that starts with "F". There are three tables, each to receive
> one of these three types of records.
Only 2000 records? And a footer too? Hey, what's wrong with writing
a program in C#, VBScript, Perl or whatever to just run INSERT statements?
2000 records is no big deal. For 200000 records I would not recommend this,
though!
Nevertheless, I tried my crazy idea, and I am completely perplexed,
it works!
Here are sample tables:
CREATE TABLE header (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL)
CREATE TABLE footer (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL)
CREATE TABLE middler (a char(1) NOT NULL,
b varchar(12) NOT NULL,
c varchar(10) NULL,
d varchar(10) NULL,
e varchar(12) NOT NULL,
f varchar(10) NULL,
g varchar(10) NULL)
Here is a sample file:
H~Goddag~~yxskaft!
M~Nu~tndas~tusen~juleljus~~
M~Vi~g~ver~daggstnkta~berg~
M~Vi~ro~musikanter~utifrn~~Skaraborg
M~Elva~elaka~elefanter~ervrade~Enkping
~
F~Hello~~goodbye!
The first table is trivial to load:
bcp torsten..header in slask.bcp -T -c -t ~ -F1 -L1
The essential is -L1, this constraints the load to the first line.
The footer table is almost equally simple:
bcp torsten..footer in slask.bcp -T -c -t ~ -F6 -L6
The catch is that you need to know the actual number, 6 in this case.
So you need to use a program that counts the line in file and compose
the SQL command with help of this.
The BCP command for the middler table:
bcp torsten..middler in slask.bcp -T -f slask.fmt -L4
And this is the format file:
8.0
8
1 SQLCHAR 0 0 "\n" 0 "" ""
2 SQLCHAR 0 0 "~" 1 a Finnish_Swedish_CS_AS
3 SQLCHAR 0 0 "~" 2 b Finnish_Swedish_CS_AS
4 SQLCHAR 0 0 "~" 3 c Finnish_Swedish_CS_AS
5 SQLCHAR 0 0 "~" 4 d Finnish_Swedish_CS_AS
6 SQLCHAR 0 0 "~" 5 f Finnish_Swedish_CS_AS
7 SQLCHAR 0 0 "~" 6 g Finnish_Swedish_CS_AS
8 SQLCHAR 0 0 "\r" 7 h Finnish_Swedish_CS_AS
The crazy idea I got that the line terminator Windows is two characters,
CR-LF. (Pray tell that your file does not come from Unix and only has
\n as separtor!) Normally you specify them together as the terminator for
the last field, but here I've split them. The net result of this is that
the first field for the first record in the file is the entire first
line. As you see, the DB-column number for this field is 0, which means
that we do not import it. For remaining records that first field is
just the empty string.
Again, I use the -L option to constrain how many records that are
imported. Note that the number is 4 - not 5. The -L option is not
required here, but without it you would get the message "Unexpected EOF
encountered in BCP data file". The row still gets imported though.
(With BCP. With BULK INSERT you need to set BATCHSIZE=1.)
What baffles me is that if I delete the footer row, I don't get this
message even if I leave out the -L option. After all, in this case,
there is an empty first field for an incomplete record in the file.
But that is good news! Maybe not for you, but for everyone else who
has a header, but not a footer. They don't have to count lines in the
file just to escape the message. (You have to anyway, because of the
footer.)
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 VERY much for this crazy (and perplexing) idea, Erland -- I'll
try it out... Crazy ideas are always the most enjoyable.
In article <Xns97CEB31622B10Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Nevertheless, I tried my crazy idea, and I am completely perplexed,
> it works!|||ERLAND, YOU ARE A GENIUS!!!!!!!!!!!!!!! This works perfectly!!! THANK
YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!
The amazing is that it worked the first time I tried it. And yes, I'm
sure it's that specially-modified format file that does it with the
separation of \n and \r as delimiters. Thank so SO MUCH. Much
appreciated. This is great.
In article <Xns97CEB31622B10Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Only 2000 records? And a footer too? Hey, what's wrong with writing
> a program in C#, VBScript, Perl or whatever to just run INSERT statements?
> 2000 records is no big deal. For 200000 records I would not recommend this
,
> though!
> Nevertheless, I tried my crazy idea, and I am completely perplexed,
> it works!
> Here are sample tables:|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> ERLAND, YOU ARE A GENIUS!!!!!!!!!!!!!!! This works perfectly!!! THANK
> YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU!!!!!
> The amazing is that it worked the first time I tried it. And yes, I'm
> sure it's that specially-modified format file that does it with the
> separation of \n and \r as delimiters. Thank so SO MUCH. Much
> appreciated. This is great.
I'm glad to hear that it worked out!
And I can tell you that I too was amazed when it worked in my first try.
BCP is really a tool that fosters your creative thinking. After all these
years, I'm still finding new angles with it.
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

BCP's /F switch - forposting.txt [0/1]

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".
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