Home » RDBMS Server » Server Utilities » SQL-Loader: How to dynamically create multiple records out of one CSV input line? (SqlLoader)
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
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?

Report message to a moderator

Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468006 is a reply to message #468005] Wed, 28 July 2010 09:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But how to handle this?
EXTERNAL TABLE

Report message to a moderator

Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468007 is a reply to message #468005] Wed, 28 July 2010 09:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Is the KEYWORD static within a single datafile/load or would it vary?

Report message to a moderator

Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468008 is a reply to message #468007] Wed, 28 July 2010 09:44 Go to previous messageGo to next message
Victoria2
Messages: 3
Registered: July 2010
Junior Member
Mahesh Rajendran wrote on Wed, 28 July 2010 16:39
Is the KEYWORD static within a single datafile/load or would it vary?

The KEYWORD is static (constant) within a single datafile. Lines without the KEYWORD should be ignored.

[Updated on: Wed, 28 July 2010 10:03]

Report message to a moderator

Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468032 is a reply to message #468008] Wed, 28 July 2010 11:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
The sample file you have posted has inconsistent delimiters.
Both comma and semicolon is been used. Is there a pattern i was missing?
Or it is just a typo while posting here?

If the delimiters are consistent, it could be very easy to use some scripting.
Some like this. You fix the datafile to your need and load it using sqlldr. I am using "OraFaq" as keyword.
Kaapi:ora magvivek$ cat someFile
OraFaq;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
OraFaq;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

Kaapi:ora magvivek$  awk -F";" '$1 == "OraFaq"' someFile |  cut -d ';' -f 3-  |awk -F";" '{for(i=1;i<=NF;i++){ printf("%s%s",$i,i%2?";":"\n")}}' 
REC1_COL1_X;REC1_COL2_X
REC2_COL1_X;REC2_COL2_X
REC1_COL1_Z;REC1_COL2_Z
REC2_COL1_Z;REC2_COL2_Z
REC3_COL1_Z;REC3_COL2_Z
REC4_COL1_Z;REC4_COL2_Z


There are some native methods too. Could be helpful if the delimiters have a pattern.

Report message to a moderator

Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468033 is a reply to message #468032] Wed, 28 July 2010 11:52 Go to previous messageGo to next message
Victoria2
Messages: 3
Registered: July 2010
Junior Member
Mahesh Rajendran wrote on Wed, 28 July 2010 18:50
The sample file you have posted has inconsistent delimiters.
Both comma and semicolon is been used.

Or it is just a typo while posting here?

Oops, sorry Embarassed Just typos...

Report message to a moderator

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 messageGo to next message
Barbara Boehmer
Messages: 9101
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>

Report message to a moderator

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 messageGo to next message
Mahesh Rajendran
Messages: 10708
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 messageGo to next message
Barbara Boehmer
Messages: 9101
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

Re: SQL-Loader: How to dynamically create multiple records out of one CSV input line? [message #468064 is a reply to message #468063] Wed, 28 July 2010 13:59 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Many thanks. Learned something new.

[Updated on: Wed, 28 July 2010 14:00]

Report message to a moderator

Previous Topic: Export Error.
Next Topic: Data Pump Error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 23 14:16:33 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.