How would you write a control file for Oracle SQL Loader that treats each record as being composed of 3 commas?

I have read the documentation, however I cannot find how to handle this scenario, without having to edit my data file by hand.

For example, my data file has:


I would like to load the data into the table looking like this:

col1       col2       col3

fd         3232       gfd67gf
peas       989767     jkdfnfgjhf
dhdhjsk    267        ujfdsy
fuyds      637296     ldosi
fduy       873        fuisouyd
try\nsave  2837       ipoi

Note: Where I have "\n" in the last row of col1, I mean an actual line feed not literally "\n".

  • Is there a reason why you can't modify the input file? It's trivial to script a modification.
    – Philᵀᴹ
    Commented Apr 14, 2012 at 12:06
  • I thought SQLLoader could handle this sort of thing
    – kloppers
    Commented Apr 14, 2012 at 12:08
  • Does this help:? How does one load records with multi-line fields? Commented Apr 14, 2012 at 13:11
  • The \n is doable with the link provided by @ypercube above. However, I can't find any way of dealing with multiple records on the same row in this situation. I could be wrong, but I've just spent 1/2 hour trying to massage them in & got nowhere.
    – Philᵀᴹ
    Commented Apr 14, 2012 at 16:49
  • Even with ypercube's link it shows no way of saying a record is formed of 3 commas
    – kloppers
    Commented Apr 15, 2012 at 0:59

1 Answer 1


So the linefeed before peas and dhdhjsk should be skipped but the linefeed before save is part of the data. Which linefeeds should be skipped and which are part of the data? I think such a format is very error prone and should be avoided. Perhaps you can do it the following way but I did not verify this

  • define "," as the physical record separator
  • join three physical records to on logical records
  • to skip a leading \n trim leading whitespace from fields or use sql-functions

The following links to the manual can provide information:

Concatenating physical records to logical records

Input formats


Apply SQL functions

Specifying delimiters

  • I'd say it's not a valid answer if you haven't verified it.
    – Philᵀᴹ
    Commented Apr 16, 2012 at 10:17
  • @Phil Answers do not have to be verified to be accurate. I think people asking questions would prefer an unverified answer over no answer at all. Even if an answer turns out to be wrong it will at least provide a guidepost to what doesn't work. Disclosing that the answer has not been verified is beneficial and allows the OP and voting system to verify the answer. I haven't verified the answer either, but I voted for it because I believe it is headed in the right direction. Commented Apr 16, 2012 at 13:20
  • Ok, no worries. I was just unsure, as it looks to be troublesome.
    – Philᵀᴹ
    Commented Apr 16, 2012 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.