Re: Basic Sqlldr question

Friday, December 9, 2005, 6:42:09 AM, rjamya (rjamya_at_gmail.com) wrote:

r> I am loading a flatfile fields enclosed by double-quotes and delimited by
r> comma. We found that some column values can also contain double-quotes. How
r> do I _escape_ the double-quotes in the column values?

Been awhile since I've looked into this particular issue, but I believe you can double up on the quotes in your column.

For example, instead of:

'This isn't my first value',12,13,...

Your file should contain:

'This isn''t my first value',12,13...

If you're stuck with the first format above, and you can't change the program that generates the file, then I think you are beyond what SQL*Loader can handle and you'll need to write a script of some sort (shell script, Perl script, etc.) to prep your input file.

Maybe you could get fancy and try to specify the quote+comma as the delimiter for the first field (as in delimited by "',"), but that's a hack and I'm not sure that it could be made to work (though it'd be fun to try it).

If you can post an example of your data, we might be able to come up with some better suggestions. I'm only guessing above at what your input data looks like.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

--
http://www.freelists.org/webpage/oracle-l