473,237 Members | 1,174 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

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 2488
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Alan Mailer | last post by:
Imagine I had a table called MyTable with the following values: Field1 Field2 ____ _____ A X AA R B X BB T Now, say I want to return only rows which match *BOTH* of the
4
by: hharry | last post by:
Hello All, Does the BCP utility enable you to selectively import rows from a flat file to a table ? For example: The first column in my flat file contains a record type - 1, 2..7 I only...
59
by: AK | last post by:
I tried to google "skip scan DB2" but came up with nothing. Does DB2 have the feature under a different name?
3
by: Jim Heavey | last post by:
Trying to figure out the technique which should be used to add rows to a datagrid. I am thinking that I would want an "Add" button on the footer, but I am not quite sure how to do that. Is that...
16
by: Adam Witney | last post by:
Hi, Is it possible for the COPY command to read data from a file, but skip specific columns? Thanks Adam
3
by: Niyazi | last post by:
Hi all, I have a dataTable that contains nearly 38400 rows. In the dataTable consist of 3 column. column 1 Name: MUHNO column 2 Name: HESNO Column 3 Name: BALANCE Let me give you some...
5
by: bruxerjk | last post by:
I need to do the following. I want to copy cells from a number of Excel files, say "1.xls", "2.xls", "3.xls", etc, into successive rows of "All.xls". That is, I'd open "1.xls", copy cells and...
5
by: Jim in Arizona | last post by:
I built a webpage using vb.net (.net 2.0) that creates a form letter. This letter pulls data from a database. Although I populate the address with the person's full name, which comes from the name...
3
by: nigelesquire | last post by:
Please help! I'm trying to clone and delete multiple rows with JavaScript. I need two delete buttons that work...! I only have one for now, but it's not working properly, the output count is...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.