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

[ntuyen01]

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

[Ed Murphy]

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/;
}