Home » RDBMS Server » Server Utilities » Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file (mer
Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file (mer [message #312655] |
Wed, 09 April 2008 09:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scolvenkar
Messages: 5 Registered: April 2008
|
Junior Member |
|
|
Scenario:
Extract Definition: Comma delimited file with two datasets
1st dataset : Detail records representing the main information
2nd dataset: Trailer records with record counts
Example of extract:
D,1
D,2
D,3
T,3
Load Requirement definition:
Detail records dataset need to be loaded into Staging table A
Trailer records need to be loaded into Control Table B
Issue description:
Unable to load both datasets as part of one CTL file
What has been tried so far:
LOAD DATA
INFILE 'SIDD.CSV'
into table A
when s1 = 'D'
fields terminated by ","
( s1,S2 )
into table B
when s1 = 'T'
fields terminated by ","
( s1,S2 )
With the above, only the first When condition is evaluated properly, while the second one fails positive
only Table A is loaded with detail records
Current Understanding:
In a case of comma delimited file, the second When condition fails positiove because the s1 data column for second table is by default taken as a third data column in the CSV file ( which does not exist )
This makes me infer that the for a comma delimited file, the defualt functionality of SQL loader would be to strip by columns rather than by rows as expected in the load requirement
Web research does provide sufficient example of sql loads into multiple table, but each of them uses non-delimited files ( as in character positions would be used to determine the data columns)
This allows the second When condition column to be correctly placed to the first data column by specifying the character positions.
Workaround:
Have Two CTL files to load the respective datasets
Issue Resolution Expectation:
Load requirement performed in one CTL file.
Thanks a lot,
Sidd
|
|
|
|
|
|
Re: Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file [message #312760 is a reply to message #312705] |
Wed, 09 April 2008 13:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Apparently you have done something other than what you posted, because it works for me, as demonstrated below.
-- sidd.csv:
-- test.ctl:
LOAD DATA
INFILE 'SIDD.CSV'
into table A
when s1 = 'D'
fields terminated by ","
( s1,S2 )
into table B
when s1 = 'T'
fields terminated by ","
( s1 POSITION (1), S2 )
-- tables, load, and select results:
SCOTT@orcl_11g> CREATE TABLE a (s1 varchar2(1), s2 number)
2 /
Table created.
SCOTT@orcl_11g> CREATE TABLE b (s1 varchar2(1), s2 number)
2 /
Table created.
SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11g> select * from a
2 /
S S2
- ----------
D 1
D 2
D 3
SCOTT@orcl_11g> select * from b
2 /
S S2
- ----------
T 3
SCOTT@orcl_11g>
-- Here is the all-in-one script that I use for testing:
store set saved_settings replace
set echo off feedback off heading off pagesize 0 verify off
spool sidd.csv
prompt D,1
prompt D,2
prompt D,3
prompt T,3
spool off
spool test.ctl
prompt LOAD DATA
prompt INFILE 'SIDD.CSV'
prompt into table A
prompt when s1 = 'D'
prompt fields terminated by ","
prompt ( s1,S2 )
prompt into table B
prompt when s1 = 'T'
prompt fields terminated by ","
prompt ( s1 POSITION (1), S2 )
spool off
start saved_settings
spool test.txt
CREATE TABLE a (s1 varchar2(1), s2 number)
/
CREATE TABLE b (s1 varchar2(1), s2 number)
/
host sqlldr scott/tiger control=test.ctl log=test.log
select * from a
/
select * from b
/
spool off
drop table b
/
drop table a
/
ed test.txt
ed test.log
|
|
|
|
|
Different Load Type options for Mutliple INTO table statements [message #312946 is a reply to message #312655] |
Thu, 10 April 2008 03:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
scolvenkar
Messages: 5 Registered: April 2008
|
Junior Member |
|
|
Is it possible to specify multiple Load type options with the multiple INTO table options.
LOAD Requirement:
Detail Type records to be Loaded into TABLE A . TABLE A should be truncated before load
Trailer Type records to be Loaded into TABLE B. The new records should be appended.
I tried the following:
LOAD DATA
INFILE 'SIDD.CSV'
TRUNCATE
into table A
when s1 = 'D'
fields terminated by ","
( s1,S2 )
APPEND
into table B
when s1 = 'T'
fields terminated by ","
( s1 POSITION (1), S2 )
SQL*Loader-350: Syntax error at line 9.
Expecting keyword INFILE, keyword INTO or end of file, found keyword truncate.
TRUNCATE
Versions :
Sql loader version - Release 8.0.6.3.0
Oracle Version - Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
|
|
|
|
|
|
Re: Different Load Type options for Mutliple INTO table statements [message #342181 is a reply to message #313035] |
Thu, 21 August 2008 11:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
deepankumr
Messages: 5 Registered: August 2008
|
Junior Member |
|
|
i have a question with respect to the scenario that you were discussing...
requirement:
table1 has col1 and col2
table2 has col1
data file
---------------
aaa,bbb,ccc,ddd
i need to load the tables as:
table1
------
col1 col2
---- -----
aaa ddd
table2
------
col1
----
bbb
As you had suggested, we need to reset the position in the control file while loading table2 but, if we have to reset we need to specify position(1) for the first column of table2...
In my case, i need to reset the position but, do not want to store the value "a" from position(1) of the data file to any of the column in table2 rather store "bbb" to col1 of table2....
could you please help?
Thank you,
Deepan
[Updated on: Thu, 21 August 2008 11:27] Report message to a moderator
|
|
|
|
|
Re: Different Load Type options for Mutliple INTO table statements [message #342248 is a reply to message #342240] |
Thu, 21 August 2008 15:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
LOAD DATA
INFILE *
INTO TABLE table1
FIELDS TERMINATED BY ","
(col1,
dummy1 FILLER,
dummy2 FILLER,
COL2)
INTO TABLE TABLE2
FIELDS TERMINATED BY ","
(dummy3 FILLER POSITION(1),
col1)
BEGINDATA
aaa,bbb,ccc,ddd
|
|
|
Re: Different Load Type options for Mutliple INTO table statements [message #342250 is a reply to message #342248] |
Thu, 21 August 2008 15:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
deepankumr
Messages: 5 Registered: August 2008
|
Junior Member |
|
|
Thanks a lot!
i have 2 more questions:
Q1:If we have 100 values seperated by comma and we need to load the 1st and 100th value into a table, do we have to use 98 FILLERS in the control file or do we have other options?
Q2:If i need to load a column for which i will not take the data from the datafile rather say it is a sequence on which i perform NEXTVAL then, how will my control file look like?
For example: if i use the following control file
LOAD DATA
INFILE *
INTO TABLE table1
FIELDS TERMINATED BY ","
(seq1 DUMMY_SEQUENCE.NEXTVAL,
col1,
COL2,
COL3)
BEGINDATA
a,b,c
In the above case - value "a" is skipped because we have "seq1" column being used in the control file and "b" is stored in col1...
how can we handle this?
|
|
|
Re: Different Load Type options for Mutliple INTO table statements [message #342257 is a reply to message #342250] |
Thu, 21 August 2008 16:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
deepankumr wrote on Thu, 21 August 2008 13:45 |
Q1:If we have 100 values seperated by comma and we need to load the 1st and 100th value into a table, do we have to use 98 FILLERS in the control file or do we have other options?
|
Yes, you will need 98 fillers unless there is some other delimiter or fixed format that you can use to identify where the 1st field ends and the 100th field begins.
deepankumr wrote on Thu, 21 August 2008 13:45 |
Q2:If i need to load a column for which i will not take the data from the datafile rather say it is a sequence on which i perform NEXTVAL then, how will my control file look like?
For example: if i use the following control file
LOAD DATA
INFILE *
INTO TABLE table1
FIELDS TERMINATED BY ","
(seq1 DUMMY_SEQUENCE.NEXTVAL,
col1,
COL2,
COL3)
BEGINDATA
a,b,c
In the above case - value "a" is skipped because we have "seq1" column being used in the control file and "b" is stored in col1...
how can we handle this?
|
Your sequence needs to be enclosed within double quotes and all such calculated fields must be at the end of the field list. Also, if you do not have a comma after your last field, then you need to use trailing nullcols. Please see the corrected control file below.
LOAD DATA
INFILE *
INTO TABLE table1
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(col1,
col2,
col3,
seq1 "dummy_sequence.NEXTVAL")
BEGINDATA
a,b,c
|
|
|
|
Re: Different Load Type options for Mutliple INTO table statements [message #342543 is a reply to message #342423] |
Fri, 22 August 2008 13:15 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
deepankumr
Messages: 5 Registered: August 2008
|
Junior Member |
|
|
In case of mutiple table loads, while loading the second table, where should i place the calculated fields of second table:
For example:
LOAD DATA
INFILE *
INTO TABLE table1
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(col1,
dummy FILLER,
col2,
seq1 "dummy_sequence.NEXTVAL")
INTO TABLE table2
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(dummy1 FILLER position(1),
seql "dummy_sequence.CURRENTVAL"[where should i place this line?]
col1,
dummy3 FILLER,
col2)
BEGINDATA
a,b,c,d
|
|
|
Re: Different Load Type options for Mutliple INTO table statements [message #342563 is a reply to message #342543] |
Fri, 22 August 2008 15:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The calculated field must go at the bottom of the field list. However, if you are using a database sequence for two tables, in order for the sequence numbers to match, you must use the rows=1 option at the top of the control file and it will be slow if you are loading a large amount of data. A faster option is to use a SQL*Loader sequence. I have provided control files for both methods below.
OPTIONS (ROWS=1)
LOAD DATA
INFILE *
INTO TABLE table1
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(col1,
dummy FILLER,
COL2,
seq1 "dummy_sequence.NEXTVAL")
INTO TABLE TABLE2
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(dummy1 FILLER POSITION(1),
col1,
dummy3 FILLER,
col2,
seq1 "dummy_sequence.CURRVAL")
BEGINDATA
a,b,c,d
LOAD DATA
INFILE *
INTO TABLE table1
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(col1,
dummy FILLER,
COL2,
seq1 SEQUENCE)
INTO TABLE TABLE2
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(dummy1 FILLER POSITION(1),
col1,
dummy3 FILLER,
col2,
seq1 SEQUENCE)
BEGINDATA
a,b,c,d
|
|
|
Re: Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file [message #459609 is a reply to message #312655] |
Mon, 07 June 2010 07:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
elizas
Messages: 2 Registered: March 2010 Location: india
|
Junior Member |
|
|
Write the different queries for getting the data from the DB for different controls in a single string variable by separating them with semicolon.
Ex -
//Create the SQL query.
string selectQueryForControlPopulation =
"SELECT COL1,COL2,... FROM TABLE_NAME1 WHERE CONDITION; SELECT COL1,COL2,..FROM TABLE_NAME2 WHERE CONDITION; ......";
Step 2:
- Create the DataAdapter, DataSet object. Execute the query and fill the DataSet object.
Ex -
//Create the Connection object.
OleDbConnection oConnection = new OleDbConnection(ConfigurationSettings.AppSettings["SQLConnectionString"]);
//Create the Command object.
OleDbCommand oCommand = new OleDbCommand(selectQueryForControlPopulation , oConnection );
//Create the DataAdapter object and set its property.
OleDbDataAdapter oAdapter = new OleDbDataAdapter();
oAdapter .SelectCommand = oCommandPopulateOrganizations;
// Create a DataSet object.
DataSet oDataSet = new DataSet();
//Filling the DataSet object.
oAdapter .Fill(oDataSet);
(**) The DataSet object contain the result of different queries as different tables. We can access those table to fill our controls.
|
|
|
|
Goto Forum:
Current Time: Mon Sep 09 22:22:21 CDT 2024
|