Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
loading a text file into oracle
- Thread starter sweetleaf
- Start date
- Thread starter
- #3
hi vijaychin,
Thanks for your reply. I followed your suggestion and built a .ctl file - but where do i put it? and how do i call it? Is there anything other than calling this .ctl file that needs to be done? is this something that i can do without admin privileges (i am not a DBA).
my .ctl file:
Load Data
infile 'c:\folder1\file1.txt'
fields terminated by "," optionally enclosed by '^'
into table TABLE1(ID,DT,EVENT_DESC,EVENT_AMT,EVENT_CAT)
thanks.
Thanks for your reply. I followed your suggestion and built a .ctl file - but where do i put it? and how do i call it? Is there anything other than calling this .ctl file that needs to be done? is this something that i can do without admin privileges (i am not a DBA).
my .ctl file:
Load Data
infile 'c:\folder1\file1.txt'
fields terminated by "," optionally enclosed by '^'
into table TABLE1(ID,DT,EVENT_DESC,EVENT_AMT,EVENT_CAT)
thanks.
That ctl file you wrote require a small change.
put APPEND (or) REPLACE on top of into table.
in the command prompt, go the diretory c:\folder1 and execute the following command.
sqlldr <user>@dbstring/<passwd> control=<yourctl>.ctl log=<logname>.log
if everything is ok with your ctl file, i mean syntax, you will see the count of the records on your screen. Check the log for any errors.
put APPEND (or) REPLACE on top of into table.
in the command prompt, go the diretory c:\folder1 and execute the following command.
sqlldr <user>@dbstring/<passwd> control=<yourctl>.ctl log=<logname>.log
if everything is ok with your ctl file, i mean syntax, you will see the count of the records on your screen. Check the log for any errors.
- Thread starter
- #5
yes, you can run from wintnt command window. go to your text file directory and try to execute the command. If the command is not recognized, then try to set the path the oracle folder. even if it doesn't work, copy your text file and ctl file to the oracle/bin directory and execute there.
- Thread starter
- #7
ok, i am able to run sqlldr80,
My .ctl file looks like this and reports an "expecting INTO and got "fields terminated by.."
<
Load Data
infile 'bxvu0120020403-2C.txt'
APPEND
into table PPV_MASTERTEXT
(EVENT_ID,EVENT_DT,EVENT_DESC,EVENT_AMT,EVENT_CAT,e1,e2)
fields terminated by "," optionally enclosed by '^'
>
so i tried
<
Load Data
infile 'bxvu0120020403-2C.txt'
APPEND
into table PPV_MASTERTEXT
fields terminated by "," optionally enclosed by '^'
(EVENT_ID,EVENT_DT,EVENT_DESC,EVENT_AMT,EVENT_CAT,e1,e2)
>
.. and get "partial record found at eof - but this is misreporting because it still says this even if i rename the filename in the .ctl file to something that does not exist and run it ..
ca you tell me if the syntax looks correct please,
thanks!
My .ctl file looks like this and reports an "expecting INTO and got "fields terminated by.."
<
Load Data
infile 'bxvu0120020403-2C.txt'
APPEND
into table PPV_MASTERTEXT
(EVENT_ID,EVENT_DT,EVENT_DESC,EVENT_AMT,EVENT_CAT,e1,e2)
fields terminated by "," optionally enclosed by '^'
>
so i tried
<
Load Data
infile 'bxvu0120020403-2C.txt'
APPEND
into table PPV_MASTERTEXT
fields terminated by "," optionally enclosed by '^'
(EVENT_ID,EVENT_DT,EVENT_DESC,EVENT_AMT,EVENT_CAT,e1,e2)
>
.. and get "partial record found at eof - but this is misreporting because it still says this even if i rename the filename in the .ctl file to something that does not exist and run it ..
ca you tell me if the syntax looks correct please,
thanks!
- Thread starter
- #8
ok, i am able to run sqlldr80,
My .ctl file looks like this and reports an "expecting INTO and got "fields terminated by.."
<
Load Data
infile 'file.txt'
APPEND
into table table1
(ID,DT,EVENT_DESC,EVENT_AMT,EVENT_CAT,e1,e2)
fields terminated by "," optionally enclosed by '^'
>
so i tried
<
Load Data
infile 'file1.txt'
APPEND
into table PPV_MASTERTEXT
fields terminated by "," optionally enclosed by '^'
(ID,DT,EVENT_DESC,EVENT_AMT,EVENT_CAT,e1,e2)
>
.. and get "partial record found at eof - but this is misreporting because it still says this even if i rename the filename in the .ctl file to something that does not exist and run it ..
ca you tell me if the syntax looks correct please,
thanks!
My .ctl file looks like this and reports an "expecting INTO and got "fields terminated by.."
<
Load Data
infile 'file.txt'
APPEND
into table table1
(ID,DT,EVENT_DESC,EVENT_AMT,EVENT_CAT,e1,e2)
fields terminated by "," optionally enclosed by '^'
>
so i tried
<
Load Data
infile 'file1.txt'
APPEND
into table PPV_MASTERTEXT
fields terminated by "," optionally enclosed by '^'
(ID,DT,EVENT_DESC,EVENT_AMT,EVENT_CAT,e1,e2)
>
.. and get "partial record found at eof - but this is misreporting because it still says this even if i rename the filename in the .ctl file to something that does not exist and run it ..
ca you tell me if the syntax looks correct please,
thanks!
- Status
Similar threads
- Locked
- Question
- Locked
- Question
