Home » SQL & PL/SQL » SQL & PL/SQL » script to bulk load csv file data to table when different but depends on csv data
script to bulk load csv file data to table when different but depends on csv data [message #195597] Fri, 29 September 2006 10:32 Go to next message
abhijion
Messages: 9
Registered: May 2006
Junior Member
hi,

i need to load data from an excel sheet into a table which is in the folllowing column format:---

industry_group
industry_sector
industry_vertical
department
role

1 industry group has n sectors..each sector has n verticals n so on.

the catch is that corresponding table
called classification has structure like :--
[classification_id]
,[classification_sobject_id]
,[classification_name]
,[parent_id]
,[object_level]
,[is_active]
,[level_name]

this table is in object oriented format so that the parent table S_object stores object_id and parent_id of all the levels of classification.
i.e. say industry group is level 1 has object_id 1 and parent_id 0.
vertical is level 2 has object_id values(2,3,4,5,6) corresponding to different industry groups and parent_id 2.


now i need to write a script that should be reusable if number of levels increases, (say company_type under sector level) , and load corresponding values in table .

assume that I have made an entry in s_object table for new level added .(for each level in excelsheet there is an entry in s_object )

now all these values in excel sheet will go into classification_name column of classification table and parent_id and classification_sobject_id should be from s_object table.

any idea how to write a script that can be used dynamically to do this job.

if you need i may provide sample data from s_object,classification tables and excel sheet .

all clarifications are welcome.

Re: script to bulk load csv file data to table when different but depends on csv data [message #195855 is a reply to message #195597] Mon, 02 October 2006 16:09 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Get the data in a flat file on the dataserver , define an external table on that file , and do an insert into .. as select from external table.

If you can give sample data and expected output , may be we can have a go at the SQL too.

Srini
icon10.gif  Re: script to bulk load csv file data to table when different but depends on csv data [message #196424 is a reply to message #195597] Thu, 05 October 2006 05:43 Go to previous message
rikfair
Messages: 22
Registered: October 2006
Location: UK
Junior Member
This site shows the basics for external tables.

http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11210

You can then write some plsql to process the data from the csv file and insert it into your table.

Razz
Previous Topic: want to execute some DML stement from Developer
Next Topic: Crystal report with sql
Goto Forum:
  


Current Time: Sat Dec 10 16:34:53 CST 2016

Total time taken to generate the page: 0.26176 seconds