Home » RDBMS Server » Server Utilities » How to ignore rows with a specific character (Oracle 10G)
How to ignore rows with a specific character [message #446831] Wed, 10 March 2010 11:37 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Hi guys,

If i have a CSV file that is in the following format

"fd!","sdf","dsfds","dsfd",
"fd!","asdf","dsfds","dsfd",
"fd","sdf","rdsfds","dsfd",
"fdd!","sdf","dsfds","fdsfd",
"fd!","sdf","dsfds","dsfd",
"fd","sdf","tdsfds","dsfd",
"fd!","sdf","dsfds","dsfd",


Is it possible to exclude any row where the first column has an exclamation mark at the end of the string.
i.e. it should only load the following rows

"fd","sdf","rdsfds","dsfd",
"fd","sdf","tdsfds","dsfd",


I am using Oracle 10.2
Thanks
Re: How to ignore rows with a specific character [message #446834 is a reply to message #446831] Wed, 10 March 2010 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref837

Try this:
WHEN (field1 not like '%!')

Regards
Michel
Re: How to ignore rows with a specific character [message #446840 is a reply to message #446831] Wed, 10 March 2010 12:15 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
I tried the following but it failed

WHEN (CODE not like '%!') - fails with the following error


SQL*Loader-350: Syntax error at line 4.
Expecting = or "<>", found "not".
WHEN (CCODE not like '%!')
            ^


WHEN (LENGTH(CODE)<3)


fails with the following erro


SQL*Loader-350: Syntax error at line 4.
Expecting positive integer or column name, found keyword length.
WHEN (LENGTH(CODE)<3)
           ^


From the manual it seems it has to be <columname><operator><value>. I dont understand how that will work if i want to use an expression.
Re: How to ignore rows with a specific character [message #446847 is a reply to message #446840] Wed, 10 March 2010 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not an expert in SQL*Loader and prefer using external table then you have the power of SQL to load.

Regards
Michel

[Updated on: Wed, 10 March 2010 12:29]

Report message to a moderator

Re: How to ignore rows with a specific character [message #446856 is a reply to message #446831] Wed, 10 March 2010 12:48 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Thanks Michel

It looks like functions cant be used in the WHERE clause. Is there any other way of achieving this with SQL loader?
Re: How to ignore rows with a specific character [message #446859 is a reply to message #446856] Wed, 10 March 2010 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to wait Barbara connects to get the answer, she is on US West Coast.

Regards
Michel
Re: How to ignore rows with a specific character [message #446863 is a reply to message #446840] Wed, 10 March 2010 13:19 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There is a way, though. Not very beautiful, but - it works. Sort of.

I guess you know table description - I don't, so I had to create one of my own. It looks like this:
SQL> desc test
 Name              Null?    Type
 ----------------- -------- ------------
 COL1                       VARCHAR2(5)
 COL2                       VARCHAR2(10)
 COL3                       VARCHAR2(10)
 COL4                       VARCHAR2(10)

SQL>

Trick is to check all "col1" column's positions and make sure they are different from the exclamation mark (!). WHEN clause is capable of doing that.

OK then, here's the control file:
load data
infile *
replace
into table test

when (1) <> '!' and (2) <> '!' and (3) <> '!' and (4) <> '!' and (5) <> '!'

fields terminated by ","
optionally enclosed by '"'
trailing nullcols

( col1,
  col2,
  col3,
  col4
)

begindata
"fd!","sdf","dsfds","dsfd",
"fd!","asdf","dsfds","dsfd",
"fd","sdf","rdsfds","dsfd",
"fdd!","sdf","dsfds","fdsfd",
"fd!","sdf","dsfds","dsfd",
"fd","sdf","tdsfds","dsfd",
"fd!","sdf","dsfds","dsfd",

Loading session and the result:
SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri O₧u 10 20:17:23 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 6

SQL> select * from test;

COL1  COL2       COL3       COL4
----- ---------- ---------- ----------
fd    sdf        rdsfds     dsfd
fd    sdf        tdsfds     dsfd

SQL>
Re: How to ignore rows with a specific character [message #447332 is a reply to message #446863] Sat, 13 March 2010 14:22 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Thanks all for your help. I managed to do it using awk in Unix.

Thanks
Re: How to ignore rows with a specific character [message #448040 is a reply to message #446831] Fri, 19 March 2010 03:26 Go to previous messageGo to next message
n_prabhakar
Messages: 3
Registered: March 2010
Location: Singapore
Junior Member
tricky one. am sure you must have thought other ways.

1) you can grep the file and send the output to another file, filtering all the rows with has !, then import the 2nd file

or

2) load the entire data. Have a delete statement to wipe out the incorrect info

or

3) have a database insert trigger on that table, which will validate the 1st column. If it encounters !, then it will fail the insert.

If anyone has better ideas, i would like to learn from them

thanks & regards

Re: How to ignore rows with a specific character [message #448046 is a reply to message #448040] Fri, 19 March 2010 03:44 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If anyone has better ideas, i would like to learn from them

The ones that have been posted before?

Regards
Michel
Previous Topic: Load Data Submit Failed
Next Topic: SQL Loader & control file problem
Goto Forum:
  


Current Time: Sun Sep 25 04:15:58 CDT 2016

Total time taken to generate the page: 0.10844 seconds