Home » RDBMS Server » Server Utilities » Importing CSV file into multiple tables using a script (Oracle 8, unix/windows)
Importing CSV file into multiple tables using a script [message #345425] Wed, 03 September 2008 09:55 Go to next message
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!
Re: Importing CSV file into multiple tables using a script [message #345426 is a reply to message #345425] Wed, 03 September 2008 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 25041
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.
Re: Importing CSV file into multiple tables using a script [message #345427 is a reply to message #345425] Wed, 03 September 2008 10:05 Go to previous messageGo to next message
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?
Re: Importing CSV file into multiple tables using a script [message #345428 is a reply to message #345425] Wed, 03 September 2008 10:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Also, post your table structure.
Could be easily scripted. Search the forum for examples.
Re: Importing CSV file into multiple tables using a script [message #345442 is a reply to message #345425] Wed, 03 September 2008 11:11 Go to previous messageGo to next message
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
Re: Importing CSV file into multiple tables using a script [message #345453 is a reply to message #345442] Wed, 03 September 2008 12:09 Go to previous message
AlasdairCM
Messages: 3
Registered: September 2008
Location: London
Junior Member
thank you, exactly what I was looking for! Legend.
Previous Topic: imp
Next Topic: import from client 9.2.0.1, it always hang
Goto Forum:
  


Current Time: Tue Dec 06 14:18:28 CST 2016

Total time taken to generate the page: 0.12766 seconds