2

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:

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

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".

Improve this question
5
  • Is there a reason why you can't modify the input file? It's trivial to script a modification. – Philᵀᴹ Apr 14 '12 at 12:06
  • I thought SQLLoader could handle this sort of thing – kloppers Apr 14 '12 at 12:08
  • Does this help:? How does one load records with multi-line fields? – ypercubeᵀᴹ Apr 14 '12 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ᵀᴹ Apr 14 '12 at 16:49
  • Even with ypercube's link it shows no way of saying a record is formed of 3 commas – kloppers Apr 15 '12 at 0:59
2

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

Whitespace

Apply SQL functions

Specifying delimiters

Improve this answer
3
  • I'd say it's not a valid answer if you haven't verified it. – Philᵀᴹ Apr 16 '12 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. – Leigh Riffel Apr 16 '12 at 13:20
  • Ok, no worries. I was just unsure, as it looks to be troublesome. – Philᵀᴹ Apr 16 '12 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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