Home » RDBMS Server » Server Utilities » how to upload (export) the text file to database?
how to upload (export) the text file to database? [message #340414] Tue, 12 August 2008 12:25 Go to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

how to upload (export) the text file to database?
create table t_export_test (name varchar2(10),phone number);

my text file name is "c_dir.txt"
------------------------------------
text file contents
------------------------------------

Vetri;12345;
vel;302303;sudha;123456;


control file "c_dir_test.ctl"
-------------------------------------
LOAD DATA
INFILE c_dir.dat
INSERT
INTO TABLE t_export_test
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(name,
phone
)

sql>sqlldr control =D:\c_dir_test.ctl;

I am getting sql statement invalid

please help me how to export(upload) text file to database?

Re: how to upload (export) the text file to database? [message #340415 is a reply to message #340414] Tue, 12 August 2008 12:28 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sqlldr is an operating system command, not a sqlplus command.

Usually upload is associated with import and download with export, but maybe that's my association.
Re: how to upload (export) the text file to database? [message #340428 is a reply to message #340414] Tue, 12 August 2008 16:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
If you use the host command you can run sqlldr from the sql prompt in SQL*Plus if you like. Make sure that you do not have any spaces on either side of the equal sign:

sql> host sqlldr control=D:\c_dir_test.ctl

Also, make sure that your file names match. If your file name is c_dir.txt, then you need to use that as your infile in your SQL*Loader control file, not c_dir.dat.

Re: how to upload (export) the text file to database? [message #340539 is a reply to message #340414] Wed, 13 August 2008 04:19 Go to previous messageGo to next message
sumanthd
Messages: 10
Registered: June 2008
Location: Hyderabad
Junior Member

For Uploading data to a table u need to keep the table empty.
truncate table t_export_test;

and while loading the data after typing the

sqlldr control =D:\c_dir_test.ctl;

then u will be prompted to enter user name

username:

here u have to punch in the username@databasenme
(if it is a local database no need to type database name that is just type username thats will be sufficient )
and then it asks for

password:
type in the pasword for the database

if u still have error
then please specify the error what u get,so that we can suggest u in a better way.
Re: how to upload (export) the text file to database? [message #340565 is a reply to message #340539] Wed, 13 August 2008 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and don't use IM speak.

Regards
Michel
Re: how to upload (export) the text file to database? [message #340629 is a reply to message #340539] Wed, 13 August 2008 08:37 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sumanthd wrote on Wed, 13 August 2008 05:19
For Uploading data to a table [you] need to keep the table empty.



Incorrect.
Re: how to upload (export) the text file to database? [message #340799 is a reply to message #340428] Thu, 14 August 2008 06:37 Go to previous messageGo to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

Hi Thanks for your help.

i tried it but still nothing uploaded on my table.

can you please send me with example like below:
test table
test.ctl controlfile
test.txt datafile
SQL>sqlldr control=test.ctl;

Thanks,
Victory
Re: how to upload (export) the text file to database? [message #340800 is a reply to message #340799] Thu, 14 August 2008 06:45 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you'll benefit from studying the SQL*Loader documentation.

victoryhendry
send me with example

Documentation is full of examples; your case seems to be a simple one, so - I guess you'd rather discover the solution by yourself, than having it delivered directly to your playground.
Re: how to upload (export) the text file to database? [message #340801 is a reply to message #340428] Thu, 14 August 2008 06:46 Go to previous messageGo to next message
victoryhendry
Messages: 95
Registered: January 2007
Location: Bangalore
Member

i tried it but still nothing uploaded on my table.and i didnt get any error while executing the sqlldr query?can u please give some example will try same thing?

can you please send me with example like below:
test table
test.ctl controlfile
test.txt datafile
SQL>sqlldr control=test.ctl;

Thanks,
Victory
Re: how to upload (export) the text file to database? [message #340807 is a reply to message #340801] Thu, 14 August 2008 07:11 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have tried ... what? Reading the manual?

victoryhendry
SQL>sqlldr control=test.ctl;
Re-read Joy Division's and Barbara's answers again (and again, and again ... until you understand what's being said).
Re: how to upload (export) the text file to database? [message #340855 is a reply to message #340801] Thu, 14 August 2008 11:47 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
victoryhendry wrote on Thu, 14 August 2008 04:46

can you please send me with example like below:
test table
test.ctl controlfile
test.txt datafile
SQL>sqlldr control=test.ctl;



-- test table:
SCOTT@orcl_11g> create table test (name varchar2(10),phone number)
  2  /

Table created.


-- test.ctl controlfile:
LOAD DATA
INFILE test.txt
INSERT
INTO TABLE test
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(name,
phone
)


-- test.txt datafile:
Vetri;12345
vel;302303
sudha;123456


-- load data:
SCOTT@orcl_11g> HOST sqlldr scott/tiger control=test.ctl log=test.log


-- results:
SCOTT@orcl_11g> select * from test
  2  /

NAME            PHONE
---------- ----------
Vetri           12345
vel            302303
sudha          123456

SCOTT@orcl_11g> 


-- test.log (check this if you have problems or need details about your load):
SQL*Loader: Release 11.1.0.6.0 - Production on Thu Aug 14 09:42:00 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Control File:   test.ctl
Data File:      test.txt
  Bad File:     test.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TEST, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NAME                                FIRST     *   ;       CHARACTER            
PHONE                                NEXT     *   ;       CHARACTER            


Table TEST:
  3 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             3
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Thu Aug 14 09:42:00 2008
Run ended on Thu Aug 14 09:42:01 2008

Elapsed time was:     00:00:00.94
CPU time was:         00:00:00.07


victoryhendry is currently offline victoryhendry's Profile
Date Registered: Sun, January 14, 2007
Message Count: 38 Messages(s) (0.07 average messages per day)
Show all messages by victoryhendry
Real Name: vetri
Status: Member
Last Message: Thu, 14 August 2008 04:46
Re: how to upload (export) the text file to database?
Last Visited: Thu, 14 August 2008 04:47
E-mail: [Click here to e-mail the user]
Private Message:
Buddy: add to buddy list
Homepage: http://Reading Books
Gender: Male
Location: Bangalore
Occupation: Engineer
Interests: Reading Books
Biography: Reading Books
Date Of Birth: April 29, 1982
AIM Handle: Reading Books
Yahoo Messenger: Reading Books
MSN Messenger: Reading Books
Jabber: Reading Books
Google Talk: Reading Books
Skype: Reading+Books

Read this:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/part_ldr.htm#i436326

It is a lot like a book. It even has chapters. You just click on them and read them on your screen instead of turning pages.

Previous Topic: How to load the flat files to the oracle database?
Next Topic: can any help me out
Goto Forum:
  


Current Time: Sun Dec 04 16:19:28 CST 2016

Total time taken to generate the page: 0.07053 seconds