SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468005] |
Wed, 28 July 2010 09:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Victoria2
Messages: 3 Registered: July 2010
|
Junior Member |
|
|
A question concerning the SQL-Loader.
I need to load data from a CSV file where one of the CSV values determines how many records should be inserted.
Example of the input data:
KEYWORD;2;REC1_COL1_X,REC1_COL2_X;REC2_COL1_X;REC2_COL2_X
KEYWORD;3;REC1_COL1_Y;REC1_COL2_Y,REC2_COL1_Y;REC2_COL2_Y;REC3_COL1_Y;REC3_COL2_Y
KEYWORD;4;REC1_COL1_Z;REC1_COL2_Z,REC2_COL1_Z;REC2_COL2_Z;REC3_COL1_Z;REC3_COL2_Z,REC4_COL1_Z;REC4_COL2_Z
If the KEYWORD is found, then the next value determines how many value pairs will follow, and therefore how many rows should be created in the affected DB table.
As a result I hope to achieve this:
SELECT Column1, Column2 FROM testTable
REC1_COL1_X,REC1_COL2_X
REC2_COL1_X;REC2_COL2_X
REC1_COL1_Y;REC1_COL2_Y
REC2_COL1_Y;REC2_COL2_Y
REC3_COL1_Y;REC3_COL2_Y
REC1_COL1_Z;REC1_COL2_Z
REC2_COL1_Z;REC2_COL2_Z
REC3_COL1_Z;REC3_COL2_Z
REC4_COL1_Z;REC4_COL2_Z
I learned how to import data using Oracle SQL loader for cases where one input line more or less matches a (new) row in a DB table. But how to handle this?
|
|
|
|
|
|
|
|
Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468053 is a reply to message #468033] |
Wed, 28 July 2010 13:35 ![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: 9096 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You could load the data into a staging table of varrays of pairs of column1 and column2, then insert from the staging table to the target table, as demonstrated below.
-- test.dat (using ; as delimiter):
KEYWORD;2;REC1_COL1_X;REC1_COL2_X;REC2_COL1_X;REC2_COL2_X
KEYWORD;3;REC1_COL1_Y;REC1_COL2_Y;REC2_COL1_Y;REC2_COL2_Y;REC3_COL1_Y;REC3_COL2_Y
KEYWORD;4;REC1_COL1_Z;REC1_COL2_Z;REC2_COL1_Z;REC2_COL2_Z;REC3_COL1_Z;REC3_COL2_Z;REC4_COL1_Z;REC4_COL2_Z
-- test.ctl:
load data
infile test.dat
into table staging_table
replace
trailing nullcols
(keyword filler terminated by ';',
pairs filler terminated by ';',
columns varray terminated by whitespace
(columns column object
(column1 terminated by ';',
column2 terminated by ';')))
-- types, tables, load, and results:
SCOTT@orcl_11gR2> create or replace type test_typ as object
2 (column1 varchar2 (15),
3 column2 varchar2 (15))
4 /
Type created.
SCOTT@orcl_11gR2> create or replace type test_varray as varray(10) of test_typ;
2 /
Type created.
SCOTT@orcl_11gR2> create table staging_table
2 (columns test_varray)
3 /
Table created.
SCOTT@orcl_11gR2> create table testtable
2 (column1 varchar2 (15),
3 column2 varchar2 (15))
4 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> insert into testtable (column1, column2)
2 select column1, column2
3 from staging_table,
4 table (columns)
5 /
9 rows created.
SCOTT@orcl_11gR2> select * from testtable
2 /
REC1_COL1_X REC1_COL2_X
REC2_COL1_X REC2_COL2_X
REC1_COL1_Y REC1_COL2_Y
REC2_COL1_Y REC2_COL2_Y
REC3_COL1_Y REC3_COL2_Y
REC1_COL1_Z REC1_COL2_Z
REC2_COL1_Z REC2_COL2_Z
REC3_COL1_Z REC3_COL2_Z
REC4_COL1_Z REC4_COL2_Z
9 rows selected.
SCOTT@orcl_11gR2>
|
|
|
Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468059 is a reply to message #468053] |
Wed, 28 July 2010 13:48 ![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/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Great!. As usual.
I was just thinking about the same method using varray.
But was stuck with this requirement.
Quote:If the KEYWORD is found, then the next value determines how many value pairs will follow, and therefore how many rows should be created in the affected DB table.
Had no idea how to do it with native means.
The sample data is clean. For key value 4, there are exactly 4 pairs.
If the key value was 3, it should load only 3 out of 4 pairs.
Probably just load all data into staging table and use sql ?.
Thanks
[Updated on: Wed, 28 July 2010 13:57] Report message to a moderator
|
|
|
Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468063 is a reply to message #468059] |
Wed, 28 July 2010 13:57 ![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: 9096 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can use a count of the varrays instead of delimited by whitespace, as demonstrated below. In the example below, I changed the 4 in the data line with 4 pairs to 3. I also added a when clause to test for the keyword and included a line in the data that does not have the keyword.
-- test.dat:
KEYWORD;2;REC1_COL1_X;REC1_COL2_X;REC2_COL1_X;REC2_COL2_X
KEYWORD;3;REC1_COL1_Y;REC1_COL2_Y;REC2_COL1_Y;REC2_COL2_Y;REC3_COL1_Y;REC3_COL2_Y
KEYWORD;3;REC1_COL1_Z;REC1_COL2_Z;REC2_COL1_Z;REC2_COL2_Z;REC3_COL1_Z;REC3_COL2_Z;REC4_COL1_Z;REC4_COL2_Z
ANYWORD;1;COL1_VAL;COL2_VAL
-- test.ctl:
load data
infile test.dat
into table staging_table
replace
when keyword = 'KEYWORD'
trailing nullcols
(keyword filler terminated by ';',
pairs filler terminated by ';',
columns varray count(pairs)
(columns column object
(column1 terminated by ';',
column2 terminated by ';')))
SCOTT@orcl_11gR2> create or replace type test_typ as object
2 (column1 varchar2 (15),
3 column2 varchar2 (15))
4 /
Type created.
SCOTT@orcl_11gR2> create or replace type test_varray as varray(10) of test_typ;
2 /
Type created.
SCOTT@orcl_11gR2> create table staging_table
2 (columns test_varray)
3 /
Table created.
SCOTT@orcl_11gR2> create table testtable
2 (column1 varchar2 (15),
3 column2 varchar2 (15))
4 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> insert into testtable (column1, column2)
2 select column1, column2
3 from staging_table,
4 table (columns)
5 /
8 rows created.
SCOTT@orcl_11gR2> select * from testtable
2 /
REC1_COL1_X REC1_COL2_X
REC2_COL1_X REC2_COL2_X
REC1_COL1_Y REC1_COL2_Y
REC2_COL1_Y REC2_COL2_Y
REC3_COL1_Y REC3_COL2_Y
REC1_COL1_Z REC1_COL2_Z
REC2_COL1_Z REC2_COL2_Z
REC3_COL1_Z REC3_COL2_Z
8 rows selected.
SCOTT@orcl_11gR2>
[Updated on: Wed, 28 July 2010 14:02] Report message to a moderator
|
|
|
|