0

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.

3
Contributors
4
Replies
7
Views
5 Years
Discussion Span
Last Post by apanimesh061
0

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!

0

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

0

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.

Attachments
2,Gorgonzola Telino,1,1,24 - 12 oz bottles,19,17,40,25,FALSE                    
4,Geitost,2,2,48 - 6 oz jars,22,53,0,0,FALSE                                    
6,Steeleye Stout,3,2,12 - 8 oz jars,25,120,0,25,FALSE                           
7,Inlagd Sill,3,7, ,30,-15,0,10,FALSE                                           
9,Cte de Blaye,4,6,18 - 500 g pkgs.,0,29,0,0,TRUE                              
10,Chartreuse verte,4,8,12 - 200 ml jars,31,31,0,0,FALSE                        
11,Boston Crab Meat,5,4,1 kg pkg.,21,22,30,30,FALSE                             
13,Singaporean Hokkien Fried Mee,6,8,2 kg box,6,24,0,5,FALSE                    
15,Gula Malacca,6,2,24 - 250 ml bottles,15.5,39,0,5,FALSE                       
18,Carnarvon Tigers,7,8,16 kg pkg.,62.5,42,0,0,FALSE                            
21,Sir Rodney's Scones,8,3, ,10,3,40,5,FALSE                                    
23,Tunnbrd,9,5,12 - 250 g pkgs.,9,61,0,25,FALSE                                
24,Guaran Fantstica,10,1,12 - 355 ml cans,4.5,20,0,0,TRUE-1                   
25,NuNuCa Nu-Nougat-Creme,11,3,20 - 450 g glasses,14,76,0,30,FALSE             
26,Gumbr Gummibrchen,11,3,100 - 250 g bags,31.23,15,0,0,FALSE                 
27,Schoggi Schokolade,11,3,100 - 100 g pieces,43.9,49,0,30,FALSE                
28,Rssle Sauerkraut,12,7,25 - 825 g cans,-43,26,0,0,1                          
30,Chai,13,8,10 - 200 g glasses,25.89,10,0,15,FALSE                             
31,Chang,14,4,12 - 100 g pkgs,12.5,0,70,20,FALSE                                
32,Aniseed Syrup,14,4,24 - 200 g pkgs.,32,9,40,25,FALSE                         
33,Chef Anton's Cajun Seasoning,15,4,500 g,2.5,112,0,20,FALSE                   
35,Grandma's Boysenberry Spread,16,1,24 - 12 oz bottles,18,20,0,15,FALSE        
36,Uncle Bob's Organic Dried Pears,17,8,24 - 250 g  jars,19,112,0,20,FALSE      
37,Northwoods Cranberry Sauce,17,8,12 - 500 g pkgs.,26,11,50,25,FALSE           
39,Ikura,18,1,750 cc per bottle,18,69,0,5,FALSE                                 
40,Queso Cabrales,19,8,24 - 4 oz tins,18.4,123,0,30,FALSE                       
41,Queso Manchego La Pastora,19,8,12 - 12 oz cans,9.65,85,0,10,FALSE            
42,Konbu,20,5,32 - 1 kg pkgs.,14,26,0,0,TRUE                                    
43,Tofu,20,1,16 - 500 g tins,46,17,10,25,FALSE                                  
44,Genen Shouyu,20,2,20 - 2 kg bags,19.45,27,0,15,FALSE                         
45,semolina,21,8,1k pkg.,9.5,5,70,15,FALSE                                      
46,Spegesild,21,8,4 - 450 g glasses,12,95,0,0,FALSE                             
47,Zaanse koeken,22,3,10 - 4 oz boxes,9.5,36,0,0,FALSE                          
48,Chocolade,22,3,10 pkgs.,12.75,15,70,25,FALSE                                 
49,Maxilaku,23,3,24 - 50 g pkgs.,20,10,60,15,FALSE                              
50,Valkoinen suklaa,23,3,12 - 100 g bars,16.25,65,0,30,FALSE                    
51,Manjimup Dried Apples,24,7,50 - 300 g pkgs.,53,20,0,10,FALSE                 
53,Perth Pasties,24,6,48 pieces,32.8,0,0,0,TRUE                                 
54,Burger,25,6,16 pies,7.45,21,0,10,0                                           
55,Fries,25,6,24 boxes x 2 pies,24,115,0,20,FALSE                               
56,Gnocchi di nonna Alice,26,5,24 - 250 g pkgs.,38,21,10,30,FALSE               
57,Ravioli Angelo,26,5,24 - 250 g pkgs.,19.5,36,0,20,FALSE                      
58,Escargots de Bourgogne,27,8,24 pieces,13.25,62,0,20,FALSE                    
59,Raclette Courdavault,28,4,5 kg pkg.,55,79,0,0,FALSE                          
60,Camembert Pierrot,28,4,15 - 300 g rounds,34,19,0,0,FALSE                     
61,Patties,29,2,24 - 500 ml bottles,28.5,113,0,25,FALSE                         
62,Tarte au sucre,29,3,48 pies,49.3,17,0,0,FALSE                                
63,Vegie-spread,7,2,15 - 625 g jars,43.9,24,0,5,FALSE                           
64,Wimmers gute Semmelkndel,12,5,20 bags x 4 pieces,33.25,22,80,30,FALSE       
65,Louisiana Fiery Hot Pepper Sauce,2,2,32 - 8 oz bottles,21.05,76,0,0,FALSE    
66,Louisiana Hot Spiced Okra,2,2,24 - 8 oz jars,17,4,100,20,FALSE               
67,Laughing Lumberjack Lager,16,1,24 - 12 oz bottles,14,52,0,10,FALSE           
68,Scottish Longbreads,8,3,10 boxes x 8 pieces,12.5,6,10,15,FALSE               
69,Gudbrandsdalsost,15,4,10 kg pkg.,36,26,0,15,FALSE                            
70,Outback Lager,7,1,24 - 355 ml bottles,15,15,10,30,FALSE                      
72,Mozzarella di Giovanni,14,4, ,34.8,14,0,0,FALSE                              
73,chicken,17,8,24 - 150 g jars,15,101,0,5,FALSE                                
75,salsa,12,1,24 - 0.5 l bottles,7.75,125,0,25,FALSE                            
76,Puree,23,1,500 ml,18,57,0,20,FALSE
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.