PROBLEM WITH SQL LOADER

Looks like you have tab delimited data, Try to use this control file.
Are you passing the right file name to sql loader? (I didnt see an infile clause in the control file)

LOAD DATA
replace
INTO TABLE XYZ
fields terminated by '\t' optionally enclosed by '"'
TRAILING NULLCOLS
(
    ID         
  , EMAIL_TX         
  , DT           DATE "MM/DD/YYYY"
  )

Select allOpen in new window

THE FILE IS NT TAB DELIMITED
AM PASSING THE RGT FILE NAME TO LOADER
AND THE FILE GETS LOADED INTO TABLE WITH THE CTLFILE I MENTIONED ABOVE
BUT GIVES THE ERROR CODE AS MENTIONED

give the command you use to invoke sql loader

Seems to be a command line issue - how do you invoke the loader?

Do you name your file like the file you attached (with test-123.txt)? This might cause the problem, I donÄ't know how the loader will do with the hyphen between test and 123

I INVOKE THE LOADER IN A SHELL SCRIPT

THE NAME TEXT HYPEN 123 IS JUST THE EXAMPLE THAT IS NT THE ORGINAL NAME OF THE FILE

Would you mind telling us the line? The error message indicates that the data-parameter is given twice on the command-line, but this doesn't have to be though, maybe it's a filename with a blank or something like that.

below is the script i run

CTLFILE=tester_123.ctl
LOGFILE=tester_123log
BADFILE=tester_123.bad
DISCFILE=tester_123.dsc

sqlldr   control=$CTLFILE data=test_123\
log=$LOGFILE bad=$BADFILE discard=$DISCFILE \
userid=XXXX\
rows=10000 errors=99999 silent=all
the above script runs but it is giving me the error code at line above
log=$LOGFILE bad=$BADFILE discard=$DISCFILE \

Have you tried this with a blank between test_123 and the backslash?

No

Then do it please, could be possible, that, due to the backslash right next to the filename, the log= ist added to the data-parameter as well.

In the above i just added the an example
what i actually do is specify the dir and then file name

from the example above it looks like there is no space between the file name and the \.

FYI, the more info and accurate info you provide helps the experts to comment on your problem better. You may get the resolution faster.

PLEASE FIND BELOW THE EXACT CODE WHICH AM RUNNING
Sql-loader.txt

The command line looks fine, at least in Windows on one line (unfortunately I don't have a unix envirnoment at hand at the moment), so it may be a problem of either the separation into several lines or with the name of the data file itself.

What I tried:
sqlldr control=test.ctl data=%verzeichnis%/%datei% log=test.log bad=test.bad discard=test.dis userid=%USERNAME%  rows=10000 errors=99999 silent=all

Your controlfile should look EXACTLY like this:

https://www.experts-exchange.com/questions/23859531/NEED-HELP-WITH-SQL-LOADER.html?cid=748&anchorAnswerId=22850819#a22850819

LOAD DATA
REPLACE
INTO TABLE xxxx
FIELDS TERMINATED BY " " TRAILING NULLCOLS
-- This is a TAB -----^
(
    ID
  , EMAIL_TX
  , DT   DATE "MM/DD/YYYY"
)

Select allOpen in new window

Illegal combination of non-alphanumeric characters
#LOAD DATA

This is the error i get when i run the same the control file provided  above

Please post all output from the sql loader log and a copy of your controlfile.

WHEN I USE THE THE CONTROL FILE WITH NO POSITION THE LOG FILE SHOWS RECORDS INSERTED =0
WHEN I USE THE CONTROL FILE WITH FIXED POSITION ALL THE RECORDS ARE LOADING IN THE TABLE BUT GIVES ME THE ERRORCODE AND QUITS FROM THE SCRIPT WITHOUT EXECUTING THE REST

Sorry, my crystal ball is broken and I cannot see your sql loader log file, maybe you could post it?

Please find the attached logfile
Logfile.txt

Any solution for this issue guys?

can somebody please answer this question this shud solve my problem
If the data file has headers for the colums in the file how is it going to work?

Hi,

i can see the data file which you are trying to load "test-123.txt"

i can see the ksh file which which invokes sqlldr "Sql-loader.txt"

i can also see the sql ldr log file "Logfile.txt"

BUT where is the control file "tester_123.ctl" ? can you please attach that as a file here.

1) Also your log file "Logfile.txt" shows that you are using "test.ctl" as the ctl file and not "tester_123.ctl" ? --> can you please confirm ????

2) Also i can see the below from "Logfile.txt" meaning only 2 records are rejected. So what is the acutal issue now ????

  1261 Rows successfully loaded.
  2 Rows not loaded due to data errors.

Thanks for the response
the ctl file am using is
LOAD DATA
REPLACE
INTO TABLE xyz
TRAILING NULLCOLS
(
    ID     POSITION( 1:9)
   , EMAIL     POSITION(11)   TERMINATED BY WHITESPACE  
   , DT    DATE  "MM/DD/YYYY"
   , RCE    CONSTANT 0
  , STATUS  CONSTANT "P"
)
In the real data file there is a header for it
If i remove the header the process is running fine but i cant manually remove the header each time
As i want to automate the process.as of now am running it maually so it is working fine but once it is automated it definately fails.
So can you please suggest me how to over the issue

As per my post #22852783, you should have modified your control file to REMOVE the "POSITION" options.

Try this control file:

LOAD DATA
REPLACE
INTO TABLE xyz
fields terminated by '\t' optionally enclosed by '"'
TRAILING NULLCOLS
(
     ID
   , EMAIL    
   , DT     DATE  "MM/DD/YYYY" 
   , RCE    CONSTANT 0
  , STATUS  CONSTANT "P"
)

Select allOpen in new window

If i remove the postion it is just failing in the begining itself

you can very well skip the header records which you do not want to load with the SKIP parameter.

you have to specify skip=n when you invoke sqlldr in yourcase it will be skip=1 because you want to skip 1 record which is the header

see the below link :

http://www.orafaq.com/wiki/SQL*Loader_FAQ#Can_one_skip_header_records_while_loading.3F

i mean, you can just try with your current control file which is giving error when trying to load header but just include this SKIP parameter as well....

yaa i used skip  in the command line this morning and it was working fine
So is that  the only solution to overcome it..?

Ok Thanks for your quick response  this shud solve my issue