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 problem

I have a '|' delimited free format file , the records as shown below
1234|Hello world|1234.12|123.12
1234|HI|124.12|13.12
1234|XXXXX|234.12|12.12
I have table X ( col1 number(4),col2Number(10,2)) and another table
Y (col1varchar(200),col2 Number(10,2))
Now my requirement is to load the data into table X and Y using Sql Loader so that the first and third columns of the data file is loaded into table X and the second and fourth column is loaded into table Y .
Any idea how to do that ?
Thanks in advance
According to my knowledge you must load data in each table separetely. In other words , you have to load data in one table first and after you have to load the another table. Perhaps another person can help with the exact sentence. I going to help with the knowledge source about how to do it.
Utilities ( Part II )
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652.pdf
Joel P�rez
you can load the data into multiple tables, if the columns are in sequential order this is very easy, in your case it is slightly complicated because you need columns 1,3 into one table and 2,4 in other table.
i suggest you can try this hint, it may work for you. i haven't tested this. here is the syntax. see if it works for you.
LOAD DATA
INFILE 'test.dat'
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'
REPLACE
INTO TABLE X
(col1 INTEGER EXTERNAL TERMINATED BY |,
dummy_col FILLER INTEGER EXTERNAL TERMINATED BY |,
col2 CHAR TERMINATED BY |)
INTO TABLE Y
(col2 POSITION(2) CHAR TERMINATED BY |,
dummy_col FILLER INTEGER EXTERNAL TERMINATED BY |,
col4 INTEGER EXTERNAL TERMINATED BY |)
Mukundan.
Thanks 
Thanks mukundan it workd fine for me .. 
great!!! glad to hear that.
Mukundan.

SQLLDR Problem.

Hi people,
I am still trying to solve the problem of
loading two related tables. The following
code does not work as I expect - if fills
EXAMPLE_TABLE_TWO.ID with the last value
of EXAMPLE_TABLE_ONE.ID. Does anybody know
how to correct this code to fill these tables
record by record to comply Primary - Foreign
ralationship.
+++
LOAD DATA
INFILE 'example.csv'
BADFILE 'example.bad'
DISCARDFILE 'example.dsc'
DISCARDMAX 50
REPLACE
INTO TABLE EXAMPLE_TABLE_ONE
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED '"' TRAILING NULLCOLS
(
     ONE_FIEDLD,
     ANOTHER_FIELD,
     ID EXPRESSION "EXAMPLES_SEQUENCE.NEXTVAL"
)
INTO TABLE EXAMPLE_TABLE_TWO
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED '"' TRAILING NULLCOLS
(
     ONE_FIELD,
     ID EXPRESSION "EXAMPLES_SEQUENCE.CURRVAL"
)
You actually do not need to refer to an oracle sequence to generate the ascending values when sqlldr'ing.
See this.. it loads the example.csv file - the rows get a sequence value starting with 1, incrementing by 1. Both tables end up with the same ID value, per input line of example.csv.
Contents of example.csv:
tom;best;somewhere
jimmy;dean;elsewhere
per;son;outback city
Contents of example.ctl:
LOAD DATA
INFILE 'example.csv'
REPLACE
INTO TABLE EXAMPLE_TABLE_1
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED '"' TRAILING NULLCOLS
(
a,
b,
ID sequence(1,1)
)
INTO TABLE EXAMPLE_TABLE_2
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED '"' TRAILING NULLCOLS
(
c,
ID sequence(1,1)
)
======
Results:
SQL> select * from example_table_1;
A B ID
--------------- ---------- ----------
tom best 1
jimmy dean 2
per son 3
SQL> select * from example_table_2;
C ID
--------------- ----------
somewhere 1
elsewhere 2
outback city 3
SQL>
Tom Best
Sorry,
I have made a mistake in place of REPLACE goes APPEND.
The tables and siquence already exist and are used for
production. I need to add data every day and want solve
the problem with loader only without any temp. tables,
triggers, procedures etc.
Thanks.
Then you should definitely use external tables. You define a "table" that really doesn't contain data that is in the database... it is a pointer to your flat file. The syntax is much like the sqlldr control file syntax.
You select from the external table in just the same way you do a normal table. So, you can do something like:
insert into mytable select a, b, some_seq.nextval from my_external_table;
See this:
http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/statements_7002.htm#i2153251
Tom Best 
Thank you Tom,
You gave me a good hint. I will use it in
the future for other cases.
In this case I do not have access to Oracle
directories but I have put OPTIONS(ROWS=1)
and the above code works fine. As have few
records that suits me fine.
Cheers.
Yuriy
Hi Yuriy,
It seems to be that your data with master-detail relation is actually 1-1 relation, not 1-n.
Is it true?

How to skip data fields with SQL* Loader?

Dear Experts,
My requirement is to insert only 4 columns that are present in a CSV file which has many columns in each record. For example, one of the records in the data file is:
1,20005606,,PROCTER & GAMBLE UK,TW20 9NW,EGHAM,gs_update1,11/09/2008,10:35:56,400000,GBP,16/09/2008,11:51:32,agreement,"400,000",GBP,,,,,,,N,137376101,29/09/2008,UNITED KINGDOM,152
I want only to insert via the SQL* Loader control file the 8th, 9th, 10th and 20th colum (delimited by commas). How do I write the commands in the SQL* Loader control file so that I can select just these columns (that are not fixed length)?
Many thanks,
Mark 
You can make use of position parameter in controlfile of the sql loader.
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
OR
Make use of this which is more convinient
FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
Regards 
Thanks for that NKU,
With the POSITION command, I understand you first state what column you are starting with, then often (as in your example) the length of the field. However, I do not know the length, but I do know it is delimited by a commas.
Therefore, should it be something like this:
LOAD DATA
REPLACE
INTO TABLE XX_TEST
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(
field_1 POSITION(9),
field_2 POSITION(10)
field_3 POSITION(15)
...etc
)
Otherwise, I could use FILLER as you have described.
Thanks again for your help,
Mark 
It appears it is not possible to use POSITION, only FILLER, as seen here:
"One cannot use POSITION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want."
Thanks,
Mark

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

Dear All,
I Am Using Oracle 10g r2 on windows.
I have tablel like below
create table sql_loader(empnumber int,empname varchar2(10));
SQL> desc sql_loader
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNUMBER                                          NUMBER(38)
EMPNAME                                            VARCHAR2(10)I am Using .csv File to upload the data in table.
Now,I have EMPNUMBER column first and EMPNAME column second,My sequence of column in the .csv file should be like empnumber and empname.
If i have EMPNAME column First and EMPNUMBER column second in the .csv file, Data is not inserting into the table.
Is there is any way to insert the data like above.
(In short i just wanted to insert data from .csv file to oracle table throught SQL LOADER. But thing is that the column sequence may not be the same as in the oracle table.)
Is there is any way to insert the data like above.
Please help.
Regards,
Chanchal. 
Yes!
In the loader control file you have to use the column order of the csv-file you want to read.
I guess you only have to change the order of column specifications in your loader control file.
(I can only guess, because you did not show your loader control file)
The order of columns in the table does not matter for sqlldr.
For a more detailed answer you should post your question in the sqlldr-forum:
Export/Import/SQL Loader & External Tables
Edited by: hm on 22.12.2011 01:39 
yes we can change sequence of the column in .csv file as per oracle table.
But is there is any way that we will put any condition once while creating control file only.
My control file:-
options (skip=1)
LOAD DATA
INFILE 'C:\Documents and Settings\amos000448\Desktop\PMS\loader.csv'
truncate
INTO TABLE sql_loader
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
     empnumber,
     empname
     
)
{code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
You can change the control file for different order in CSV-File:
options (skip=1)
LOAD DATA
INFILE 'C:\Documents and Settings\amos000448\Desktop\PMS\loader.csv'
truncate
INTO TABLE sql_loader
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
     empname,
     empnumber     
)
{code}
-----------------------------------------------------------------------------------------------------------
I have an idea (untested) that is not very nice, but it should work when the name never consists completely of numbers.
The decision in wich column the date will be stored will be made by looking at the content of the data.
(But it is a bad idea to load files an not to know how the data are organized)
Here the untested loader control file:
{code}
options (skip=1)
LOAD DATA
INFILE 'C:\Documents and Settings\amos000448\Desktop\PMS\loader.csv'
truncate
INTO TABLE sql_loader
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
     empname "decode(regexp_substr(:empnumber,'^[0-9]+$'),null,:empnumber,:empname)",
     empnumber  "decode(regexp_substr(:empnumber,'^[0-9]+$'),null,:empname,:empnumber)"
)Edited by: hm on 22.12.2011 01:58
Edited by: hm on 22.12.2011 01:59 
Hi,
try to use the below control file,
options (skip=1)
LOAD DATA
INFILE 'C:\Documents and Settings\amos000448\Desktop\PMS\loader.csv'
truncate
INTO TABLE sql_loader
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
     empname trunc(:empname),
     empnumber trunc(:empnumber)
)
Thanks,
Atul 
Thanks Atul.
But will it take that name of column that you have trunc and bind it from .csv to oracle table. 
Atul its not working. 
I tried by above (untested) control file (the second in my previous post) now with the following csv-file content:
col1,col2
1,otto
peter,2It works in that way, that the number column will be loaded in the empnumber column and the name (otto or peter) will be loaded in the empname column.
Edited by: hm on 22.12.2011 02:18 
No, My requirement is not like that.
Empnumber column will be in place of empname and empname column will be in place of empnumber. 
so please take the first control file in my first post!

Categories

Resources