469,160 Members | 1,919 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,160 developers. It's quick & easy.

Skip rows where the first column with letter 'S' - BCP

Hi All,
I have this data file with fix length(see below). I am able to insert
it into the database using bcp, but now I want to skip (do not insert)
the row which start with letter 'S' into the database. Is there away to
do it? By the way I am using -F2 option to skip the first record.

Here is my data:
Record 1 04
XXX
2 13106900240120042003040045061 Testing N POLYDOROS TRUST
EEE
2 12621241640280041004040045633 What are they MARTIN &
XXXXX
S C1000003200400409850000059611000000500001000000001 9613000000576497500
S X1000003200000209850000059613000000000000000000001 9613000000573497000

Thanks for your help.

Ted Lee

Aug 9 '06 #1
1 2309
On 9 Aug 2006 08:16:02 -0700, nt******@yahoo.com wrote:
>I have this data file with fix length(see below). I am able to insert
it into the database using bcp, but now I want to skip (do not insert)
the row which start with letter 'S' into the database. Is there away to
do it? By the way I am using -F2 option to skip the first record.

Here is my data:
Record 1 04
XXX
2 13106900240120042003040045061 Testing N POLYDOROS TRUST
EEE
2 12621241640280041004040045633 What are they MARTIN &
XXXXX
S C1000003200400409850000059611000000500001000000001 9613000000576497500
S X1000003200000209850000059613000000000000000000001 9613000000573497000
1) Create a new table with the same structure as your existing one,
plus an extra column to store the first character (if needed).

2) Give the new table trigger logic on insert/update to copy its data
to the original table iff the first character is not 'S'.

3) Use bcp to insert into the new table.

Alternatively, strip out the S lines ahead of time, e.g. install
ActivePerl and then do

while (<>) {
print "$_" unless $_ =~ /^S/;
}
Aug 9 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by hharry | last post: by
59 posts views Thread by AK | last post: by
3 posts views Thread by Jim Heavey | last post: by
16 posts views Thread by Adam Witney | last post: by
5 posts views Thread by Jim in Arizona | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.