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

[ntuyen01@yahoo.com]

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 131069002401200 42003040045061 Testing N POLYDOROS TRUST
EEE
2 126212416402800 41004040045633 What are they MARTIN &
XXXXX
S C10000032004004 098500000596110 000005000010000 000019613000000 576497500
S X10000032000002 098500000596130 000000000000000 000019613000000 573497000

Thanks for your help.

Ted Lee

[Ed Murphy]

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

On 9 Aug 2006 08:16:02 -0700, ntuyen01@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 131069002401200 42003040045061 Testing N POLYDOROS TRUST
EEE
>2 126212416402800 41004040045633 What are they MARTIN &
XXXXX
>S C10000032004004 098500000596110 000005000010000 000019613000000 576497500
>S X10000032000002 098500000596130 000000000000000 000019613000000 573497000

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