Sql*Loader - Control File-Urgent - SQL & PL/SQL

I have a data file and I want to select only specific rows from
the data file.
eg.data file:
No,Name,City
1,Ford,Kansas
2,King,St.Louis
3,Scott,Atlanta
4,Brian,New York
5,Toms,Kansas
6,Lewis,Kansas
7,Martin,Atlanta
8,Kathy,New York
I want to create a control file that will load data into my
table and select only those rows for which the city is Kansas
and Atlanta. ie mutiple rows for the same column.
How do I create the control file with WHEN clause.
Thanks
Thiru

LOAD DATA
INFILE 'datafile1.csv'
TRUNCATE
INTO TABLE table1
WHEN city <> 'St.Lousis' AND city <> 'NewYork'
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( Number1 ,
Name1 ,
city )
-- Saravanan.

First you must load this data TWICE using the control below
modified 2 times.
First Time
----------
LOAD DATA
INFILE 'filename.dat'
WHEN (city='Kansas')
...
...
Second Time
----------
LOAD DATA
INFILE 'filename.dat'
WHEN (city='Atlanta')
Why? Because I cannot write a WHEN clause and OR two or more
conditions. That is the fact of life using SQL* Loader. You can
only AND conditions, not OR them.
Gio

Related

SQL loader group by value if duplicate recordes found

Hi
I want to load the data from a text value, the field values are delimited by pipe line. But in my text value i have duplicate records, and i want to add the value of amount if some duplicate record is there. for example:
prod_id|security_id|amount|date
12|AB|130|20091231
12|Ab|40|20091231
where prod_id and security_id combined is the primary key in my target table. I want insert a single record in the target table like flollowing:
prod_id security_id amount idate
12 AB 170 20091231
How would i write in control file for that. Following are my loader parameters I am using
OPTIONS (SKIP=1)
LOAD DATA
INFILE *
INTO TABLE my_table
REPLACE
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
prod_id position(1:2) "to_number(:prod_id)",
security_id position(4:5) "trim(:security_id )",
amount position(6:10) "to_number(:amount )",
idate position(12:20) "trim(:idate)"
)
Can anyone help me how would i group by amount values
Thanks
shaveta 
You wouldn't write a control file as this is not SQLloader functionality. SQLloader cannot aggregate data, as documented (as all documentation is comprehensive, what is not documented, is not there).
You need a staging table, allowing alll junk in the file to be processed, and SQL code to aggregate your data.
It is also bad practice to directly load into a target production table.
------------
Sybrand Bakker,
Senior Oracle DBA 
user10996835 wrote:
Hi
I want to load the data from a text value, the field values are delimited by pipe line. But in my text value i have duplicate records, and i want to add the value of amount if some duplicate record is there. for example:
prod_id|security_id|amount|date
12|AB|130|20091231
12|Ab|40|20091231
where prod_id and security_id combined is the primary key in my target table. I want insert a single record in the target table like flollowing:If your table has a PK defined on what you don't want duplicate, then you're already safe, the 2nd loaded row will be rejected by the PK.
Nicolas.

load text file to table

I have text file contain table data with the format
12345,678
12345,678
12345,678
12345,678
how can I insert it to table in oracle database
Let me make a few assumptions before I proceed to the answer.
First of all assuming you are using Oracle 9i,
Assuming that your table name is TABLE1, and assuming your text file is called TEXT1.TXT, columns names are column1 and column2 both char ), user to be scott and tiger
Create a control file with control1.ctl with the following contents :
LOAD DATA
INFILE 'TEXT1.TXT'
APPEND
INTO TABLE TABLE1
FIELDS TERMINATED BY ','
(column1 position(01:05) char,
column2 position(06:08) char
)
Then, use sql loader to load the data
From command line,
----------------------------
sqlldr scott/tiger control='control1.ctl' log='control1.log' errors=100
----------------------------
This should load the contents of the text file into your table.
I love external tables, for which this is a great application. And, conveniently enough, here is Tom Kyte's comparison of them to sqlldr:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6611962171229
Tom Best

how to load file data into single column using sql loader

Dear Members,
I am facing problems with sql loader while loading data.
Suppose my file contains 35 columns and it is a tab delimited file.
I want to load all the 35 columns in a single column in the table. How can i do that using sql loader.
when i tried to use the below code in ctl file it loaded one character which is tab(chr(9)) in col2:
LOAD DATA INFILE 'VCF40_049.txt'
APPEND
INTO TABLE apps.test
(col1 "ap_credit_card_trxns_s1.nextval",
col2)
Here col2 is the column in table test where i want to load all the file data(35 columns).
How can we do the above using sqlldr.
Thanks
Sandeep 
Hi,
why can't you concatenate the data in the excel and load it, that make easy your work instead of concatenating at the load time.
- Pavan Kumar N 
Hello,
Based on the information you provided i created a test table
CREATE TABLE MY_NEW_TABLE
(
  COL1  NUMBER,
  COL2  VARCHAR2(4000 BYTE)
)mydata.txt
my data should be loaded like this
#mycontrol.ctl -- I am using FILLER for col1 but you can replace with your default value
LOAD DATA
INTO TABLE my_new_table
(
col1 FILLER,
col2 char(4000)
)To load from command line
sqlldr username/password control=/path_to_control_file/mycontrol.ctl data=/path_to_data_file/mydata.txt log=mydata.logRegards 
Orionnet,
Thanks for your reply. I used your example and tried it and it works great but there is a problem.
It is not loading the first column in the file.
For example:
I have 35 columns seperated by TAB in my file. It is loading 34 columns into a single column in a table, its skipping the first column.
Do you have any idea why this is happening. I am using the following code in my ctl file ( i am replacing tab with pipe so that i can easily understand the data):
LOAD DATA INFILE 'VCF406.txt'
APPEND
INTO TABLE apps.test
(col1 "ap_credit_card_trxns_s1.nextval",
col2 char(3000) "replace(:col2,chr(9),'|')")
Thanks
Sandeep 
Hello,
Can you post 2 records for sample data? Also it will be better if you can seperate both fields (columns) by '|'
E.g.
test value1 | test value2 and i will load anything in this column upto 3000 character.
Regards

sql loader query

hello Experts, i have tried to use sql loader, below is my control file but it halt in between, neither given error nor command is executing completely. cmd prompt is just waiting for cmd prompt. please let me know what is the problem
load data
infile *
APPEND
into table dept1
fields terminated by ","
(deptno,dname)
INTO TABLE EMP1
FIELDS TERMINATED BY ","
(EMPNO,ENAME,DEPTNO)
begindata
10,accounting,new york,101,SMITH,10
20,research,dallas,102,JOHN,10
30,sales,chicago,103,RAM,20
40,operations,boston,104,VISNA,20
40,operations,boston,105,VISNA2,20
thanks n regards
yash 
Pl post details of OS and database versions, along with the description of the table, sample data file and the contents of the log file.
There is a forum dedicated to SQL*Loader - Export/Import/SQL Loader & External Tables
HTH
Srini 
I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds. 
can we have extra values in control file than the actual values which we are inserting in table.
eg. in my control file i have user 6 values but inserting only 5 values? 
can we have extra values in control file than the actual values which we are inserting in table.yes 
i use:
host sqlldr userid=scott/tiger#scott control=c:\dept.txt log=dept.log
commit point reach: logical records count 4
commit point reach: logical records count 5
data does not load in tables emp1 and dept1
when i add third column loc in dept1 in control file, data loaded successfully in tables. please tell me where is problem 
when i add third column loc in dept1 in control file, data loaded successfully in tables. please tell me where is problemIf/when data loads successfully, then no problem exists.
FILLER is valid in control file. 
THAKS ALL 
is there any relation between order of column name of table and data in datafile?
load data
infile *
APPEND
into table dept1
fields terminated by ","
(deptno,loc,dname) -------order of name of columns of table
INTO TABLE EMP1
FIELDS TERMINATED BY ","
(EMPNO,ENAME,DEPTNO)-------name of column of table
begindata
10,accounting,new york,101,SMITH,10 ------- order of data values
20,research,dallas,102,JOHN,10
30,sales,chicago,103,RAM,20
40,operations,boston,104,VISNA,20
40,operations,boston,105,VISNA2,20

SQL Loader checking datafile fields matches ctl file fields

Hi,
I'm using SQL Loader for loading flat files to Tables.
Here is my datafile
*****************************
empid|name|dept|salary|grade
1|shekhar|projects|1000|C
2|ravi|projects|800|D
3|rob|projects|1200|B
*****************************
my ctl file
*****************************
LOAD DATA
INFILE emp.txt "str '|\r\n'"
APPEND INTO TABLE EMP
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
EMP_ID                    
, NAME
, DEPT
, SALARY
, GRADE
)
******************************
If there is extra field added in datafile mgr field.
empid|name|dept|salary|mgr|grade
1|shekhar|projects|1000|rajesh|C
and using the same old ctl file, how can I identify mismatch in no. of columns in ctl file & datafile.
Thanks,
Shekhar 
Shekhar,
Generally load will succeed if both mgr and grade field have the same type of data field and data length. But if they are defined like this in table
mgr varchar2(30)
grade varchar2(1)
Then the load will fail.
for variable length data file, your control file should match your control file (sequence of columns). But in case of fixed you can pick and choose based on position of the fields in data file.
In this case if you don't want to load "mgr" filed then you need to modify your control file like this
LOAD DATA
INFILE emp.txt "str '|\r\n'"
APPEND INTO TABLE EMP
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
EMP_ID
, NAME
, DEPT
, SALARY
,FILLER
, GRADE
)Regards 
Shekhar
Beyond the previous suggestion, there are 2 possible approaches:
1) validate the data file outside SQL*Loader (eg use grep/sed/awk to count the number of pipe characters in a row, and complain if that is greater than (number of fields - 1).
2) define an extra filler field in the control file, and complain (refuse to load) if it is NOT null.
LOAD DATA
INFILE emp.txt "str '|\r\n'"
APPEND INTO TABLE EMP
WHEN DUMMY = ''                  -- this is the check which will discard records where there is an extra value
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
EMP_ID
, NAME
, DEPT
, SALARY
, GRADE
, DUMMY FILLER
)If there is extra data, the records won't be loaded.
NOTE:
1) any records where the last column is not supplied WILL be loaded
2) there is no error message (just a note in the log) "Record 1: Discarded - failed all WHEN clauses."
HTH
Regards Nigel

Categories

Resources