sqlldr errors importing txt file with ORA-01841:

I have a script creating a txt file which I want to import into an oracle table using sqlldr
but it is erroring with a ORA-01841: (full) year must be between -4713 and +9999, and not be 0

data

Code:

201403141218,carlo.lambertini,rdp-tcp#9,2,Active,1,14/03/2014 07:25,eu1ptsw001 201403141218,horst.saendler,rdp-tcp#1,3,Active,0,14/03/2014 05:45,eu1ptsw001

ctl file

Code:

options (skip=0) Load Data append into table rfgdba.TS_SESSION_SNAPSHOT fields terminated by ',' trailing nullcols ( snapshot_timestamp DATE "YYYYMMDDHH24MI", username, session_name, id, state, idle_time, logon_time DATE "DD/MM/YYYY HH24:MI", ts_name )

table definition

Code:

CREATE TABLE RFGDBA.TS_SESSION_SNAPSHOT ( SNAPSHOT_TIMESTAMP DATE, USERNAME VARCHAR2(30 BYTE), SESSION_NAME VARCHAR2(30 BYTE), ID NUMBER, STATE VARCHAR2(10 BYTE), IDLE_TIME NUMBER, LOGON_TIME DATE, TS_NAME VARCHAR2(30 BYTE) )

I have tried (via sqlplus) - select to_date('201403141218', 'YYYYMMDDHHMI') from dual;

which returns a value of - 14/03/2014 12:18:00

so I cannot fathom why this is erroring

It seems like you have some bad data in you file. If you are using linux/unix you can use cut to get the
first four characters and pipe that to a sort -u. Then you will know which data is bad.

cut -c-4 my.txt | sort -u

You specify skip=0. You might want to remove that and specify a file for the rejects.

http://docs.oracle.com/cd/B28359_01/...s.htm#i1004846

Quote:

Originally Posted by gandolf989

It seems like you have some bad data in you file. If you are using linux/unix you can use cut to get the
first four characters and pipe that to a sort -u. Then you will know which data is bad.

cut -c-4 my.txt | sort -u

You specify skip=0. You might want to remove that and specify a file for the rejects.

http://docs.oracle.com/cd/B28359_01/...s.htm#i1004846

I have removed the skip=0, a bad file is defined by the calling command - see below

calling command

Code:

sqlldr PHANTOM/m0nit0r@RICSBA1 data=%%i control=c:\ts_users\sqlload_ts_users.ctl log=c:\ts_users\sqlfiles\%%~ni.log bad=c:\ts_users\sqlfiles\%%~ni.bad discard=c:\ts_users\sqlfiles\%%~ni.dis direct=TRUE skip=0 rows=20000 errors=100000

I have changed the script that is creating/formatting the file to be sqlloaded, so that the first field is a date/time field

new data

Code:

2014/03/19 16:23,tony.engwall,rdp,2,Disc,41,19/03/2014 13:10,eu1ptsw001 2014/03/19 16:23,pilar.velasco,rdp-tcp#6,5,Active,0,19/03/2014 15:29,eu1ptsw001

i then changed the ctl file, to not define the field with the problem - snapshot_timestamp

now the error being returned is

Code:

Record 1: Rejected - Error on table RFGDBA.TS_SESSION_SNAPSHOT, column SNAPSHOT_TIMESTAMP. ORA-01858: a non-numeric character was found where a numeric was expected

so this makes me think (as you have suggested that there is something funny with the data)

nb: I am running this on windows

You can open the file with Excel and sort the first column, Install Cygwin for Windows, put the C:\Cygwin\bin directory in you PATH variable
and use the Linux commands that I sent you, or you can really take skip=0 out and allow the file to get loaded with a bad data file to track
all of the bad data coming in. Ultimately, you need to do a better job of figuring out what to do with bad records, that you will probably
continue to get in the future.

.
Your new data:

Code:

2014/03/19 16:23,tony.engwall,rdp,2,Disc,41,19/03/2014 13:10,eu1ptsw001 2014/03/19 16:23,pilar.velasco,rdp-tcp#6,5,Active,0,19/03/2014 15:29,eu1ptsw001

Does not match this: "YYYYMMDDHH24MI"
Why did you change it?
:rolleyes:

I changed the date into a form I thought would get thru sqlldr .

didnt work, which led me to think that perhaps there was some hidden conrol characters.

downloaded a hexeditor and found 1st character !

changed my script that created the output file by adding '-Encoding ASCII ' to the Out-File command
and now I can read in the data successfully

many thanks people for ur help