Home » RDBMS Server » Server Utilities » Loading data from CSV file specially (10.2g)
| Loading data from CSV file specially [message #380327] |
Sat, 10 January 2009 22:16  |
Bangla
Messages: 49
Registered: August 2008
|
Member |
|
|
The CSV file is like below,
ID,coaching_centre,LOCATION,STD_NAME01, STD_NAME02
1,'Oracle Coaching','Dhaka','Momin', 'Abdul'
2,'ISP Coaching','Jhenidah',
From the CSV file first 3 fields will be loaded as usual. But the last fields that is STD_NAME01, STD_NAME02 is bit complex. For each std_name every first three entry from the CSV file will be there. For example for std_name Momin both
1,'Oracle Coaching','Dhaka' and
2,'ISP Coaching','Jhenidah'
rows will be entryed.
Similarly for std_name 'Abdul'
all first three rows will be entryed.
In the table there is 4 columns.
ID,coaching_centre,LOCATION and STD_NAME.
|
|
|
|
|
|
| Re: Loading data from CSV file specially [message #380331 is a reply to message #380327] |
Sat, 10 January 2009 23:15  |
Bangla
Messages: 49
Registered: August 2008
|
Member |
|
|
The CSV file is like below,
ID,coaching_centre, LOCATION, STD_NAME01, STD_NAME02
---------------------------------------------------------
1,'Oracle Coaching', 'Dhaka', 'Momin', 'Abdul'
2,'ISP Coaching', 'Jhenidah',
From the CSV file first 3 fields(ID,coaching_centre, LOCATION) will be loaded as usual.
But the last two fields that is STD_NAME01, STD_NAME02 is bit complex. For each std_name01 and std_name02 every first three fields from the CSV file will be loaded.
For example for std_name01 'Momin' first three fields (ID,coaching_centre, LOCATION) contained values
1,'Oracle Coaching','Dhaka' and
2,'ISP Coaching','Jhenidah'
will be loaded into oracle table.
Similarly for std_name02 'Abdul'
first three fields will be loaded.
In the table there is 4 columns.
ID,coaching_centre,LOCATION and STD_NAME.
Whenever any entry of std_name01 ('MOMIN') (i.e from CSV file field) will be loaded the std_name values will be 'Momin'.
For every STD_NAME02 std_name value will be 'Abdul'.
A bit detail my final table will be,
ID,coaching_centre, LOCATION, STD_NAME
---------------------------------------------------------
1,'Oracle Coaching', 'Dhaka', 'Momin',
1,'Oracle Coaching', 'Dhaka', 'Abdul'
2,'ISP Coaching', 'Jhenidah', 'Momin'
2,'ISP Coaching', 'Jhenidah', 'Abdil'
In CSV file std_name01 and std_name02 entryed are now in rows under std_name.
|
|
|
|
|
|
|
|
| Re: Loading data from CSV file specially [message #380338 is a reply to message #380327] |
Sun, 11 January 2009 00:38 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can use "concatenate 3" to consider every 3 rows as one logical record. Then you can do three loads using the std_name1 and std_name2 from the first line for each of the three rows. Your delimiters will need to be consistent. In the following, the test data has a comma at the end of each line.
-- test.ctl:
load data
infile *
concatenate 3
into table test_tab
trailing nullcols
(id terminated by ',',
coaching_centre terminated by ',',
location terminated by ',',
std_name01 terminated by ',',
std_name02 terminated by ',')
into table test_tab
trailing nullcols
(filler1 FILLER position(1) terminated by ',',
filler2 FILLER terminated by ',',
filler3 FILLER terminated by ',',
std_name01 terminated by ',',
std_name02 terminated by ',',
id terminated by ',',
coaching_centre terminated by ',',
location terminated by ',')
into table test_tab
trailing nullcols
(filler1 FILLER position(1) terminated by ',',
filler2 FILLER terminated by ',',
filler3 FILLER terminated by ',',
std_name01 terminated by ',',
std_name02 terminated by ',',
filler4 FILLER terminated by ',',
filler5 FILLER terminated by ',',
filler6 FILLER terminated by ',',
id terminated by ',',
coaching_centre terminated by ',',
location terminated by ',')
begindata:
1,'Oracle Coaching','Dhaka','Momin','Abdul',
2,'ISP Coaching','Jhenidah',
3,'centre3','loc3',
4,'centre4','loc4','std1_name4','std2_name4',
5,'centre5','loc5',
6,'centre6','loc6',
-- create table:
SCOTT@orcl_11g> create table test_tab
2 (id number,
3 coaching_centre varchar2(20),
4 location varchar2(15),
5 std_name01 varchar2(15),
6 std_name02 varchar2(15))
7 /
Table created.
-- load:
SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl log=test.log
-- results:
SCOTT@orcl_11g> select * from test_tab order by id
2 /
ID COACHING_CENTRE LOCATION STD_NAME01 STD_NAME02
---------- -------------------- --------------- --------------- ---------------
1 'Oracle Coaching' 'Dhaka' 'Momin' 'Abdul'
2 'ISP Coaching' 'Jhenidah' 'Momin' 'Abdul'
3 'centre3' 'loc3' 'Momin' 'Abdul'
4 'centre4' 'loc4' 'std1_name4' 'std2_name4'
5 'centre5' 'loc5' 'std1_name4' 'std2_name4'
6 'centre6' 'loc6' 'std1_name4' 'std2_name4'
6 rows selected.
SCOTT@orcl_11g>
|
|
|
|
|
|
| Re: Loading data from CSV file specially [message #380353 is a reply to message #380327] |
Sun, 11 January 2009 04:26 |
Bangla
Messages: 49
Registered: August 2008
|
Member |
|
|
Just a bit clarification is it possible to create table as,
SCOTT@orcl_11g> create table test_tab
2 (id number,
3 coaching_centre varchar2(20),
4 location varchar2(15),
5 std_name01 varchar2(15))
6 /
And data of std_name02 from CSV file will be loaded to field std_name01.
|
|
|
|
| Re: Loading data from CSV file specially [message #380356 is a reply to message #380327] |
Sun, 11 January 2009 05:00 |
Bangla
Messages: 49
Registered: August 2008
|
Member |
|
|
Actually I wanted from CSV file
1,'Oracle Coaching','Dhaka','Momin','Abdul'
2,'ISP Coaching','Jhenidah'
3,'centre3','loc3'
4,'centre4','loc4'
5,'centre5','loc5'
6,'centre6','loc6'
to
Table data as,
1 Oracle Coaching Dhaka Momin
1 Oracle Coaching Dhaka Abdul
2 ISP Coaching Jhenidah Momin
2 ISP Coaching Jhenidah Abdul
3 centre3 loc3 Momin
3 centre3 loc3 Abdul
4 centre4 loc4 Momin
4 centre4 loc4 Abdul
5 centre5 loc5 Momin
5 centre5 loc5 Abdul
6 centre6 loc6 Momin
6 centre6 loc6 Abdul |
|
|
|
|
|
|
|
| Re: Loading data from CSV file specially [message #380389 is a reply to message #380386] |
Sun, 11 January 2009 11:30 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your descriptions and your data examples don't seem to match. I suggest that you use SQL*Loader to load the data into a staging table as is, then use SQL commands to insert the data from the staging table to the target table in the manner you want.
|
|
|
|
Goto Forum:
Current Time: Sun Apr 28 01:29:32 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
