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 Go to next message
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 #380329 is a reply to message #380327] Sat, 10 January 2009 22:40 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I can't understand this requirement ??
Re: Loading data from CSV file specially [message #380331 is a reply to message #380327] Sat, 10 January 2009 23:15 Go to previous messageGo to next message
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 #380332 is a reply to message #380327] Sat, 10 January 2009 23:15 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

2,'ISP Coaching',    'Jhenidah',


Posted requirement do not state how to handle blank/missing/NULL names as shown above.

As for Oracle - 'MOMIN' <> 'Momin'
Does case sensitivity matter to your application?
Please be consistent & correct in your postings.

I suggest that you should (ab)use External Tables instead of SQL Loader.

[Updated on: Sat, 10 January 2009 23:28]

Report message to a moderator

Re: Loading data from CSV file specially [message #380337 is a reply to message #380327] Sun, 11 January 2009 00:31 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
case sensitivity does not matter. Space also not matter. BlackSwan If you don't know the answer, don't make the post cumbersome.
Re: Loading data from CSV file specially [message #380338 is a reply to message #380327] Sun, 11 January 2009 00:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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 #380339 is a reply to message #380327] Sun, 11 January 2009 00:41 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
Amazing. Thank you very much.
Re: Loading data from CSV file specially [message #380353 is a reply to message #380327] Sun, 11 January 2009 04:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #380385 is a reply to message #380356] Sun, 11 January 2009 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend to study and use external table.

Regards
Michel
Re: Loading data from CSV file specially [message #380386 is a reply to message #380327] Sun, 11 January 2009 09:57 Go to previous messageGo to next message
Bangla
Messages: 49
Registered: August 2008
Member
I would but I have to load data over network.
Re: Loading data from CSV file specially [message #380389 is a reply to message #380386] Sun, 11 January 2009 11:30 Go to previous message
Barbara Boehmer
Messages: 8635
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.

Previous Topic: SQL*LOADER Question
Next Topic: ORACLE error 3113 encountered
Goto Forum:
  


Current Time: Fri Dec 09 02:21:34 CST 2016

Total time taken to generate the page: 0.10539 seconds