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