Home » RDBMS Server » Server Utilities » How to load data from a file with two types of field delimeters (oracle 10g)
How to load data from a file with two types of field delimeters [message #409415] Mon, 22 June 2009 08:45 Go to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi,

I need to load data from a file in to database tables usin sql loader.
Here the problem is that the data in the file is seperated by two types of delemeters.

Sample data of the file is:
Name^Parts(P1]P2].....]Pn)^Quantity(Q1]Q2].....]Qn)
XYZ^KEYBOARD]MOUSE]LCD^2]3]4
ABC^TV]FRIDGE]AC]VACUMECLEANER]WASHINGMACHINE^1]1]3]1]2
BDE^DESK]TELEPHONE^6]4
KKL^LIGHT]CHAIR^4]4
DOORS^DOOR]COLOR^]5
GDH^SWITCH]BOOK]PEN^]]3
GEH^SPECTS]INK^]3
JTH^MONITOR]COOLER^1]2
SER^BENCH]HARDDISK^1]3
LIP^IDCARD]HANGERS]BELT^]2]2

in this file ^ and ] are delimeters.

The table contains three fields(columns) Name,Parts,Quantity.

Fr each name filed there is more than one parts and quantity fields.so they need to be come as next rows, for the same name field.

How can i handle this in control file.

Thanks in Advance,
Ravindra.
Re: How to load data from a file with two types of field delimeters [message #409430 is a reply to message #409415] Mon, 22 June 2009 11:20 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I presume this is on unix and you are having three fields (Name, Parts and Quantity) in the database and the file you are getting is as follows

Field 1 : Name
Field 2 : It could potentially contain multiple Parts against name separated by "]"
Field 3 : It contains the respective quantity for the mentioned parts separated by "]"

One of the option I could think of is to write a awk script to parse this file and produce another file which generates the format you are after and use that file to load it into the database using sql*loader.

Awk script is something like this

$ cat test.txt
Test,Part1]Part2]Part3]Part4],1]2]3]4
Test2,Part21],1
Test3, Part31]Part32],2]3

awk -F, '{
           cnt = split($2, field_2, "]");
           cnt = split($3, field_3, "]");
           for (i=1; i<=cnt; i ++)
           {
             printf ("%s,", $1);
             printf ("%s,", field_2[i]);
             printf ("%s\n", field_3[i]);
           }
          }' test_output.txt

Please note I don't have access to unix or oracle on this box. So I have typed it. There could be some syntactical errors.

Hope this helps.

From next time if could you format your post it will be much appreciated.

Regards

Raj
Re: How to load data from a file with two types of field delimeters [message #409470 is a reply to message #409430] Mon, 22 June 2009 18:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
There are a lot of ways you could do this. You could load the data into a staging table, using just ^ as the delimiter, then use SQL to parse it and insert it into the target table. Or, you could load it into a staging table that contains varrays of objects, using both delimiters, then use SQL to insert it, as demonstrated below.

-- sample.dat:
XYZ^KEYBOARD]MOUSE]LCD^2]3]4
ABC^TV]FRIDGE]AC]VACUMECLEANER]WASHINGMACHINE^1]1]3]1]2
BDE^DESK]TELEPHONE^6]4
KKL^LIGHT]CHAIR^4]4
DOORS^DOOR]COLOR^]5
GDH^SWITCH]BOOK]PEN^]]3
GEH^SPECTS]INK^]3
JTH^MONITOR]COOLER^1]2
SER^BENCH]HARDDISK^1]3
LIP^IDCARD]HANGERS]BELT^]2]2


-- test.ctl:
LOAD DATA
INFILE 'sample.dat'
INTO TABLE staging_table
REPLACE
TRAILING NULLCOLS
  (name          TERMINATED BY '^',
   parts         VARRAY TERMINATED BY '^'
     (parts        COLUMN OBJECT
       (part         TERMINATED BY ']')),
   quantity      VARRAY TERMINATED BY '^'
     (quantity     COLUMN OBJECT
       (quant        TERMINATED BY ']')))


SCOTT@orcl_11g> CREATE OR REPLACE TYPE part_typ AS OBJECT
  2    (part	  VARCHAR2 (15));
  3  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE part_varray AS VARRAY (10) OF part_typ;
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE quant_typ AS OBJECT
  2    (quant	  NUMBER);
  3  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE quantity_varray AS VARRAY (10) OF quant_typ;
  2  /

Type created.

SCOTT@orcl_11g> CREATE TABLE staging_table
  2    (name	  VARCHAR2 (15),
  3  	parts	  part_varray,
  4  	quantity  quantity_varray)
  5  /

Table created.

SCOTT@orcl_11g> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SCOTT@orcl_11g> SELECT * FROM staging_table
  2  /

NAME
---------------
PARTS(PART)
--------------------------------------------------------------------------------
QUANTITY(QUANT)
--------------------------------------------------------------------------------
XYZ
PART_VARRAY(PART_TYP('KEYBOARD'), PART_TYP('MOUSE'), PART_TYP('LCD'))
QUANTITY_VARRAY(QUANT_TYP(2), QUANT_TYP(3), QUANT_TYP(4))

ABC
PART_VARRAY(PART_TYP('TV'), PART_TYP('FRIDGE'), PART_TYP('AC'), PART_TYP('VACUME
CLEANER'), PART_TYP('WASHINGMACHINE'))
QUANTITY_VARRAY(QUANT_TYP(1), QUANT_TYP(1), QUANT_TYP(3), QUANT_TYP(1), QUANT_TY
P(2))

BDE
PART_VARRAY(PART_TYP('DESK'), PART_TYP('TELEPHONE'))
QUANTITY_VARRAY(QUANT_TYP(6), QUANT_TYP(4))

KKL
PART_VARRAY(PART_TYP('LIGHT'), PART_TYP('CHAIR'))
QUANTITY_VARRAY(QUANT_TYP(4), QUANT_TYP(4))

DOORS
PART_VARRAY(PART_TYP('DOOR'), PART_TYP('COLOR'))
QUANTITY_VARRAY(QUANT_TYP(NULL), QUANT_TYP(5))

GDH
PART_VARRAY(PART_TYP('SWITCH'), PART_TYP('BOOK'), PART_TYP('PEN'))
QUANTITY_VARRAY(QUANT_TYP(NULL), QUANT_TYP(NULL), QUANT_TYP(3))

GEH
PART_VARRAY(PART_TYP('SPECTS'), PART_TYP('INK'))
QUANTITY_VARRAY(QUANT_TYP(NULL), QUANT_TYP(3))

JTH
PART_VARRAY(PART_TYP('MONITOR'), PART_TYP('COOLER'))
QUANTITY_VARRAY(QUANT_TYP(1), QUANT_TYP(2))

SER
PART_VARRAY(PART_TYP('BENCH'), PART_TYP('HARDDISK'))
QUANTITY_VARRAY(QUANT_TYP(1), QUANT_TYP(3))

LIP
PART_VARRAY(PART_TYP('IDCARD'), PART_TYP('HANGERS'), PART_TYP('BELT'))
QUANTITY_VARRAY(QUANT_TYP(NULL), QUANT_TYP(2), QUANT_TYP(2))


10 rows selected.

SCOTT@orcl_11g> CREATE TABLE target_table
  2    (name	  VARCHAR2 (15),
  3  	parts	  VARCHAR2 (15),
  4  	quantity  NUMBER)
  5  /

Table created.

SCOTT@orcl_11g> INSERT INTO target_table
  2    (name, parts, quantity)
  3  SELECT a.name, a.part, b.quant
  4  FROM   (SELECT name, p.part,
  5  		    ROW_NUMBER () OVER
  6  		      (PARTITION BY name
  7  		       ORDER BY ROWNUM) AS rn
  8  	     FROM   staging_table st,
  9  		    TABLE (st.parts) p) a,
 10  	    (SELECT name, q.quant,
 11  		    ROW_NUMBER () OVER
 12  		      (PARTITION BY name
 13  		       ORDER BY ROWNUM) AS rn
 14  	     FROM   staging_table st,
 15  		    TABLE (st.quantity) q) b
 16  WHERE  a.name = b.name
 17  AND    a.rn   = b.rn
 18  /

26 rows created.

SCOTT@orcl_11g> SELECT * FROM target_table
  2  /

NAME            PARTS             QUANTITY
--------------- --------------- ----------
ABC             TV                       1
ABC             FRIDGE                   1
ABC             AC                       3
ABC             VACUMECLEANER            1
ABC             WASHINGMACHINE           2
BDE             DESK                     6
BDE             TELEPHONE                4
DOORS           DOOR
DOORS           COLOR                    5
GDH             SWITCH
GDH             BOOK
GDH             PEN                      3
GEH             SPECTS
GEH             INK                      3
JTH             MONITOR                  1
JTH             COOLER                   2
KKL             LIGHT                    4
KKL             CHAIR                    4
LIP             IDCARD
LIP             HANGERS                  2
LIP             BELT                     2
SER             BENCH                    1
SER             HARDDISK                 3
XYZ             KEYBOARD                 2
XYZ             MOUSE                    3
XYZ             LCD                      4

26 rows selected.

SCOTT@orcl_11g>

Re: How to load data from a file with two types of field delimeters [message #409590 is a reply to message #409470] Tue, 23 June 2009 04:12 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi Barbara,
Thank you very much.
Re: How to load data from a file with two types of field delimeters [message #409682 is a reply to message #409590] Tue, 23 June 2009 08:18 Go to previous messageGo to next message
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
Member
Hi Barbara,
Can you explain me the control file script in detail.It will be very helpful for me to understand the script.

Thanks and Regards,
Ravindra.
Re: How to load data from a file with two types of field delimeters [message #409741 is a reply to message #409682] Tue, 23 June 2009 16:06 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The method that I used is explained and demonstrated in the following section of the Oracle 10g online documentaiton:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_loading.htm#i1007180
Previous Topic: SQL Loader
Next Topic: Unable to export users logical schema,,,!!!
Goto Forum:
  


Current Time: Wed Dec 07 16:20:39 CST 2016

Total time taken to generate the page: 0.06325 seconds