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  |
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 #446840 is a reply to message #446831] |
Wed, 10 March 2010 12:15  |
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 '%!')
^
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 #446863 is a reply to message #446840] |
Wed, 10 March 2010 13:19 |
 |
Littlefoot
Messages: 21823
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 #448040 is a reply to message #446831] |
Fri, 19 March 2010 03:26 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 11 06:19:09 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
