This content has been marked as final. Show 8 replies
elmasduro wrote:How does any s/w know about "last line"?
Hi all, i would like to know how to skip the last line in a file using sql loader. i have a file such as:
The last line in file is a number. i was trying to use when clause but no use. if you look at the data above, the first field could also start with numbers. for such a file, how can skip the last line (footer) in the ctl file? thanks
2345665 Tech INC MECA 45CA 572ACTS ERD LLC SUE CCCC 9001029
It is the line before getting End Of File, or no more data; but by then it has already been processed.
remove last line before invoking sqlldr
The SKIP parameter allows you to skip the first record (i.e. SKIP=1). However you cannot skip the last record, but you could allow that row to be rejected and write the script to expect at least one row to fail to load.
OR if you know what is always in the last line (say, it says FOOTER or AUDIT or whatever), you can use "WHEN" clause to skip it.
Edited by: user130038 on Sep 30, 2011 11:09 AM
load data infile ... into table ... ... ... when some_column != 'FOOTER' ... fields terminated by ', ' trailing nullcols (some_column, id, state)
elmasduro wrote:I make a new file without the last record & do not disturb original file content.
i did not choose to do so. The file is generated by some system and that is how it comes. removing the line, means changing how system generate files.
if you are asked in an interview that you cannot remove the line, what will you tell them? implement a different solution?