Loading a CSV file to Oracle!

[apanimesh061]

How should I use SQL loader to load a .csv file as an Oracle Database ?

CREATE TABLE products(
ProductID INTEGER NOT NULL,
ProductName CHAR(30) NOT NULL,
SupplierID INTEGER,
CategoryID INTEGER,
QuantityPerUnit CHAR(30) NOT NULL,
UnitPrice INTEGER NOT NULL,
UnitsInStock INTEGER NOT NULL,
UnitsOnOrder INTEGER NOT NULL,
ReorderLevel INTEGER NOT NULL,
Discontinued CHAR(6) NOT NULL
);

This was the table!

load data
infile 'c:\Users\Animesh Pandey\product.csv'
into table products
fields terminated by "," optionally enclosed by '"' (
ProductID,
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel,
Discontinued 
);

I saved this as load.ctl.
How should I run it?

I searched on the internet but that could not help.
Could any one help me with this problem?

Thanx!
:confused:

PS: The .csv file is attached.

[hfx642]

Try looking here http://docs.oracle.com/cd/B13789_01/server.101/b10825/part_ldr.htm#i436326
I have found this documentation to be invaluable for SQL*Loader.

[apanimesh061]

Try looking here http://docs.oracle.com/cd/B13789_01/server.101/b10825/part_ldr.htm#i436326
I have found this documentation to be invaluable for SQL*Loader.

The code for product.ctl

LOAD DATA 
	INFILE 'F:\product.csv'
	DISCARDFILE 'F:\product.dis'
INTO TABLE product TRUNCATE
WHEN ( 
	PRODUCTID is null or
        PRODUCTNAME is null or
	SUPPLIERID is null or
	CATEGORYID is null or
	DISCONTINUED is null or
	SIGN(UNITPRICE) = -1 OR
	SIGN(UNITSINSTOCK) = -1 OR
	SIGN(UNITSONORDER) = -1 OR
	SIGN(REORDERLEVEL) = -1)
FIELDS TERMINATED BY ","(
	PRODUCTID,
    PRODUCTNAME,
	SUPPLIERID,
	CATEGORYID,
	QUANTITYPERUNIT,
	UNITPRICE,
	UNITSINSTOCK,
	UNITSONORDER,
	REORDERLEVEL,
	DISCONTINUED "TRIM (:Discontinued)"
)

This is the batch file I use to load:

sqlldr control=F:\product.ctl log=F:\product.log
pause

This is the error I get!

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jan 22 01:33:48 2012

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

SQL*Loader-350: Syntax error at line 6.
Expecting = or "<>", found "is".
        PRODUCTID is null or
           ^

I need to exclude all rows where the value is null or missing, mainly with the ProductID,ProductName,QuantityPerUnit,SupplierID,CategoryID and Discontinued.
Rest I want to exclude all rows with negative and non-numeric numbers before loading (UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel)

How should I do that???
please help!

[debasisdas]

What about loading all data from .CSV into database using loader and later filtering out all unwanted records.

[apanimesh061]

What about loading all data from .CSV into database using loader and later filtering out all unwanted records.

well sir, you are right!
The csv file file that I loaded had 77 records, of which only 59 records were added to the Oracle table!

In some columns there are blank spaces which are NULL by default and in some columns there are entries like 'fifty' or 'zero' instead of 50 or 0. Those replacements have to be done!

This is the discard file!

1,Nord-Ost Matjeshering,1,1,10 boxes x 20 bags,18,39,0,10, 
3,Mascarpone Fabioli, ,2,12 - 550 ml bottles,10,13,70,25,FALSE
5,Sasquatch Ale,2,2,36 boxes,21.35, NA,0,0,TRUE
8,Gravad lax,3,2,12 - 12 oz jars,40,6, ,0,FALSE
12,Jack's New England Clam Chowder,5,4,10 - 500 g pkgs., ,86,0,0,FALSE
14,Ipoh Coffee, ,7,40 - 100 g pkgs.,23.25,35,0,0,FALSE
16,Rogede sild,7,3,32 - 500 g boxes,17.45, fifty,0,10,FALSE
17,Alice Mutton,7,6,20 - 1 kg tins,39,0,0,0, 
19,Teatime Chocolate Biscuits,8,3,10 boxes x 12 pieces,9.2,25, ,5,FALSE
20,Sir Rodney's Marmalade, ,3,30 gift boxes,81,40,0,0,FALSE
22,Grape sause,9,5,24 - 500 g pkgs.,21,104, ,25, 
29,Thüringer Rostbratwurst,12,6,50 bags x 30 sausgs.,123.79,0,0,0,TRUE
34,Chef Anton's Gumbo Mix,16,1,24 - 12 oz bottles,14,111, zero,15,FALSE
38,Mishi Kobe Niku,18,1,12 - 75 cl bottles,263.5,17,0,15,FALSE
52,Filo Mix,24,5,16 - 2 kg boxes, seven,38,0,25,FALSE
71,Flotemysost,15,4,10 - 500 g pkgs.,21.5,26, zero,0,FALSE
74,Longlife Tofu,4,7, twenty,10,4,20,5, 
77,Original Frankfurter  ,12,2,12,13, ,0,15,FALSE

I want to include entries of 43, 71 and 74 but how can I convert words into numbers?
Should I use Java to automate Data cleaning ?
I cannot find any help for that!

Attached: Oracle table with valid values.