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
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 #312665 is a reply to message #312655] Wed, 09 April 2008 10:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
For each WHEN clause after the first one, you need to reset the position, even with a delimited file.


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 )
Re: Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file [message #312705 is a reply to message #312665] Wed, 09 April 2008 11:28 Go to previous messageGo to next message
scolvenkar
Messages: 5
Registered: April 2008
Junior Member

Thanks for the extremely quick response,
I should have mentioned that i have tried that as well,

What i found was that the Trailer dataset doesnt populate correctly.

Though s1 gets populated correctly, s2 didnt get populated correctly and was left blank.

Please advise

Re: Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file [message #312726 is a reply to message #312705] Wed, 09 April 2008 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now you have to post table descriptions, data file, control file you used, log files you got in order for us to see what happened and try on our side.

Regards
Michel
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 messageGo to next message
Barbara Boehmer
Messages: 9077
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:
D,1
D,2
D,3
T,3


-- 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

Re: Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file [message #312762 is a reply to message #312705] Wed, 09 April 2008 13:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I don't see your Oracle version anywhere in your post. In earlier versions, since you do not have an ending comma, you may need to add TRAILING NULLCOLS to your control file.
Re: Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file [message #312934 is a reply to message #312655] Thu, 10 April 2008 02:58 Go to previous messageGo to next message
scolvenkar
Messages: 5
Registered: April 2008
Junior Member

Thank you so much Barbara, I really appreciate the effort that u went through to get me an answer.

I modified my CTL file to be in line with your proposed solution and i am extremely pleased that it gave me the expected results.

Thank you once again.

Sidd
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 messageGo to next message
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 #312964 is a reply to message #312946] Thu, 10 April 2008 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't start a new topuic for the same question, it is easier to follow it in the original one.

Regards
Michel
Re: Different Load Type options for Mutliple INTO table statements [message #313035 is a reply to message #312946] Thu, 10 April 2008 08:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Put the truncate and append keywords after the into table clauses:

LOAD DATA
INFILE 'SIDD.CSV'
into table A
TRUNCATE
when s1 = 'D'
fields terminated by ","
( s1,S2 )
into table B
APPEND
when s1 = 'T'
fields terminated by ","
( s1 POSITION (1), S2 )
Re: Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file [message #313060 is a reply to message #312655] Thu, 10 April 2008 09:26 Go to previous messageGo to next message
scolvenkar
Messages: 5
Registered: April 2008
Junior Member
Thanks a lot once again
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 messageGo to next message
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 #342189 is a reply to message #342181] Thu, 21 August 2008 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use FILLER

Regards
Michel
Re: Different Load Type options for Mutliple INTO table statements [message #342240 is a reply to message #342189] Thu, 21 August 2008 14:50 Go to previous messageGo to next message
deepankumr
Messages: 5
Registered: August 2008
Junior Member
Thank you Michel

i have another question:

in the below control file, how could i load as per the requirement:

LOAD DATA
INFILE *
INTO TABLE TABLE1
FIELDS TERMINATED BY ","
(COL1,
DUMMY FILLER,
DUMMY2 FILLER,
COL2
)
INTO TABLE TABLE1
FIELDS TERMINATED BY ","
(??? POSITION(1),
COL1 (i want to load "bbb" here)
)

[Updated on: Thu, 21 August 2008 14:52]

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 messageGo to next message
Barbara Boehmer
Messages: 9077
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 messageGo to next message
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 messageGo to next message
Barbara Boehmer
Messages: 9077
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 #342423 is a reply to message #342257] Fri, 22 August 2008 05:23 Go to previous messageGo to next message
deepankumr
Messages: 5
Registered: August 2008
Junior Member
Thank a lot for your valuable time!
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 messageGo to next message
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 messageGo to next message
Barbara Boehmer
Messages: 9077
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 messageGo to next message
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.
Re: Load Comma Delimited file having multiple datasets into multiple tables using a single CTL file [message #459659 is a reply to message #459609] Mon, 07 June 2010 15:46 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, but I didn't quite get it ... why did you resurrect that old topic? Is your message an answer, or a question? If it is a question, it can be split from this topic and, eventually, moved into a more appropriate forum. (Which one would it be, then?)
Previous Topic: ORA-03120: two-task conversion routine: integer overflow
Next Topic: Recovery Problem
Goto Forum:
  


Current Time: Thu Mar 28 09:02:10 CDT 2024