OraFAQ Forum: Server Utilities » Importing CSV file into multiple tables using a script

Home » RDBMS Server » Server Utilities » Importing CSV file into multiple tables using a script (Oracle 8, unix/windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Importing CSV file into multiple tables using a script [message #345425]

Wed, 03 September 2008 09:55

AlasdairCM
Messages: 3
Registered: September 2008
Location: London

Junior Member

HI,

I have a csv file that is 8k lines long and I need to import it into multiple tables on my database. The format of the file is as follows

table1|a|b|c...
table2|a|b|c...
table1|a|b|c...
table3|a|b|c...

it goes on and on with the various different tables (18 in total) .

I'm trying to create a script that will run every night to process the data and put it into the table that is required as defined by the first column in the csv file.

Any suggestions on the best way to do this? I am an oracle novice so please be gentle!

Report message to a moderator

Re: Importing CSV file into multiple tables using a script [message #345426 is a reply to message #345425]

Wed, 03 September 2008 10:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

preparse the initial file into 18 files; one per table & the use SQLLDR (SQL Loader) to load the data 1 file per table.

Report message to a moderator

Re: Importing CSV file into multiple tables using a script [message #345427 is a reply to message #345425]

Wed, 03 September 2008 10:05

AlasdairCM
Messages: 3
Registered: September 2008
Location: London

Junior Member

I thought about this, but need to see if there is a way to do it without parsing the original file?

Report message to a moderator

Re: Importing CSV file into multiple tables using a script [message #345428 is a reply to message #345425]

Wed, 03 September 2008 10:05

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Also, post your table structure.
Could be easily scripted. Search the forum for examples.

Report message to a moderator

Re: Importing CSV file into multiple tables using a script [message #345442 is a reply to message #345425]

Wed, 03 September 2008 11:11

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

I presume you want to load record start with table1 into datbase table table1 and so on. Also you know in advance about the ordering of the fields. If my assumptions are correct you don't need to the split the files but it requires a bit of hardcoding (i.e) your control file will contain the list of tables you are expecting. Check this link for more information

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref871

Regards

Raj

Report message to a moderator

Re: Importing CSV file into multiple tables using a script [message #345453 is a reply to message #345442]

Wed, 03 September 2008 12:09

AlasdairCM
Messages: 3
Registered: September 2008
Location: London

Junior Member

thank you, exactly what I was looking for! Legend.

Report message to a moderator

Previous Topic:	imp
Next Topic:	import from client 9.2.0.1, it always hang

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 12 18:53:42 CDT 2024