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  |
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  |
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 |
 |
Barbara Boehmer
Messages: 9102
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>
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 11 07:42:09 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
