I have an abstruse sqlldr problem that's bothering me. My control file looks something like this:
load data
infile 'txgen.dat'
into table TRANSACTION_NEW
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
( A,
B,
C,
D,
ID "ID_SEQ.NEXTVAL"
)
Data is something like this:
a,b,c,
a,b,,d
a,b,,
a,b,c,d
If I don't put the TRAILING NULLCOLS in, I get the "column not found before end of logical record" error. But although some of the columns are null, the commas are all there, so I don't see a reason for sqlldr to misinterpret the input file, and not get to the end where it generates the ID from the database sequence.
This syntax has worked before with no null columns - why does a null column cause sqlldr to not reach the generated column?
I've got it working, I just want to understand WHY!?!
This question is related to
oracle
sql-loader
I had similar issue when I had plenty of extra records in csv file with empty values. If I open csv file in notepad then empty lines looks like this: ,,,, ,,,, ,,,, ,,,,
You can not see those if open in Excel. Please check in Notepad and delete those records
Try giving 5 ',' in every line, similar to line number 4.
The problem here is that you have defined ID as a field in your data file when what you want is to just use an expression without any data from the data file. You can fix this by defining ID as an expression (or a sequence in this case)
ID EXPRESSION "ID_SEQ.nextval"
or
ID SEQUENCE(count)
See: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#i1008234 for all options
Last column in your input file must have some data in it (be it space or char, but not null). I guess, 1st record contains null after last ',' which sqlldr won't recognize unless specifically asked to recognize nulls using TRAILING NULLCOLS option. Alternatively, if you don't want to use TRAILING NULLCOLS, you will have to take care of those NULLs before you pass the file to sqlldr. Hope this helps
Source: Stackoverflow.com