By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,504 Members | 1,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,504 IT Pros & Developers. It's quick & easy.

Sqlloader: All database in one single plain text file

P: n/a
Hi all,

I'm trying to use sqlloader to import a database with multiple
different tables which has been exported to one single plain text
file. No fixed lenght. Let's say database.txt is something like

fruits,orange,orange
fruits,banana,yellow
fruits,pear,green
users,John Doe,25,34,26th upper street,0, 12-2-2003
users,Fred Flinstone, 24,5,14th Downing Lane,1,1-7-2002

żIs it possible to try an approach based on WHEN statements? I mean:

LOAD DATA
INFILE database.txt
FIELDS TERMINATED BY ','
WHEN (first field is 'fruits')
(name, color)
WHEN (first field is 'users')
(name, age, visits, address, active, since)

I only whant to know if it is possible or not, but will wellcome
advice in ctl file sintax.

Thanks in advance and best regards
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hallo, BadMan,

look there to find the answer:

http://asktom.oracle.com/pls/ask/f?p...:3516903603571

best regards

Ralf
--
lo****@engineer.com (BadMan!) wrote in message news:<8e**************************@posting.google. com>...
Hi all,

I'm trying to use sqlloader to import a database with multiple
different tables which has been exported to one single plain text
file. No fixed lenght. Let's say database.txt is something like

fruits,orange,orange
fruits,banana,yellow
fruits,pear,green
users,John Doe,25,34,26th upper street,0, 12-2-2003
users,Fred Flinstone, 24,5,14th Downing Lane,1,1-7-2002

żIs it possible to try an approach based on WHEN statements? I mean:

LOAD DATA
INFILE database.txt
FIELDS TERMINATED BY ','
WHEN (first field is 'fruits')
(name, color)
WHEN (first field is 'users')
(name, age, visits, address, active, since)

I only whant to know if it is possible or not, but will wellcome
advice in ctl file sintax.

Thanks in advance and best regards

Jul 19 '05 #2

P: n/a
lo****@engineer.com (BadMan!) wrote in message news:<8e**************************@posting.google. com>...
Hi all,

I'm trying to use sqlloader to import a database with multiple
different tables which has been exported to one single plain text
file. No fixed lenght. Let's say database.txt is something like

fruits,orange,orange
fruits,banana,yellow
fruits,pear,green
users,John Doe,25,34,26th upper street,0, 12-2-2003
users,Fred Flinstone, 24,5,14th Downing Lane,1,1-7-2002

żIs it possible to try an approach based on WHEN statements? I mean:

LOAD DATA
INFILE database.txt
FIELDS TERMINATED BY ','
WHEN (first field is 'fruits')
(name, color)
WHEN (first field is 'users')
(name, age, visits, address, active, since)

I only whant to know if it is possible or not, but will wellcome
advice in ctl file sintax.

Thanks in advance and best regards


From looking at the notes and example in the 9.2 Utilities manual it
does not appear that sqlldr is really designed to load different sets
of data into multiple tables. The only expample takes portions of the
same input and places it into different tables, but the input data
format does not change.

It should however be easy to write a C program or Shell script to
split the input file into one file per table based on the first
delimited parameter being the table name: fruits.dat, users.dat,
etc.....

Perhaps a true sqlldr expert will see your post and have time to
respond if there is any practical way to feed multiple layouts to
multiple tables in a single job.

HTH -- Mark D Powell --
Jul 19 '05 #3

P: n/a
db*********@spamgourmet.com (Ralf) wrote in message news:<51**************************@posting.google. com>...
Hallo, BadMan,

look there to find the answer:

http://asktom.oracle.com/pls/ask/f?p...:3516903603571


Darn close to what I'm trying! Thank you very much endeed!

As I suspected, sqlloader doesn't seem designed for such task.

Best regards.
Jul 19 '05 #4

P: n/a
Ma*********@eds.com (Mark D Powell) wrote in message news:<26**************************@posting.google. com>...
From looking at the notes and example in the 9.2 Utilities manual it
does not appear that sqlldr is really designed to load different sets
of data into multiple tables. The only expample takes portions of the
same input and places it into different tables, but the input data
format does not change.
Yap.
It should however be easy to write a C program or Shell script to
split the input file into one file per table based on the first
delimited parameter being the table name: fruits.dat, users.dat,
etc.....
awk is the tool.

But I wanted to know if there was a way to use Oracle-native
platform-independent tools...

Perhaps a true sqlldr expert will see your post and have time to
respond if there is any practical way to feed multiple layouts to
multiple tables in a single job.


Thank you anyway!

Best regards from spain.
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.