Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
  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!?!
You have defined 5 fields in your control file. Your fields are terminated by a comma, so you need 5 commas in each record for the 5 fields unless TRAILING NULLCOLS is specified, even though you are loading the ID field with a sequence value via the SQL String.
RE: Comment by OP
That's not my experience with a brief test. With the following control file:
load data
infile *
into table T_new
fields terminated by "," optionally enclosed by '"'
  ID "ID_SEQ.NEXTVAL"
BEGINDATA
1,1,,,
2,2,2,,
3,3,3,3,
4,4,4,4,,
,,,,,
Produced the following output:
Table T_NEW, loaded from every logical record.
Insert option in effect for this table: INSERT
   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
A                                   FIRST     *   ,  O(") CHARACTER            
B                                    NEXT     *   ,  O(") CHARACTER            
C                                    NEXT     *   ,  O(") CHARACTER            
D                                    NEXT     *   ,  O(") CHARACTER            
ID                                   NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "ID_SEQ.NEXTVAL"
Record 1: Rejected - Error on table T_NEW, column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table T_NEW, column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table T_NEW, column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Discarded - all columns null.
Table T_NEW:
  1 Row successfully loaded.
  3 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  1 Row not loaded because all fields were null.
Note that the only row that loaded correctly had 5 commas. Even the 3rd row, with all data values present except ID, the data does not load. Unless I'm missing something...
I'm using 10gR2.
                That is not so, because if the data always has 4 fields (but not 5 commas, or even 4), you do not need the TRAILING NULLCOLS.  Though you're onto something there, it almost makes sense....
– orbfish
                Oct 15, 2010 at 19:47
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"
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
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.