Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Charles.

Asked: January 12, 2018 - 6:09 pm UTC

Last updated: January 15, 2018 - 9:18 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

Loading table data from external source to oracle for data mining and analysis. One particular table has 7 8000 byte character fields which must be loaded into CLOB columns. Most of these are empty. The data is provided as a tab delimited text file with records potentially upto 127,993 bytes. Some fields are enclosed in double quotes. Some of the CLOB fields contain ",", """, and other punctuation within outer enclosing double quotes. There are a total of 393 columns in each record. My testing file has 43 records (150KB). How can I use SQL*LOADER to load this data from the file including the 7 8000 byte clob columns given this information. (I have not found any good examples and am not as familiar with the Loader as I would like to be). Thank you for your help.

and Connor said...

Take a look here

https://asktom.oracle.com/pls/apex/asktom.search?tag=sql-loader-not-loading-if-column-value-in-multiple-lines-and-value-enclosed-in-double-quotes

but in a nutshell, we need *some* means of determining the field separators. For example, if I see a chunk of data as:

"this is","some data","to be parsed"

Then there needs to be *something* that tells lets us distinguish between:

col1: this is ","some data","to be parsed

versus

col1: this is
col2: some data","to be parsed

versus

col1: this is
col2: some data
col3: to be parsed

Sometimes that means pre-processing the file with an OS utility (which you could do on the fly with an external table definition). Or perhaps loading the entire line into a single clob and parsing that. If you go to the Presentations section in AskTOM (under resources) and search for "Skip" you'll see an example of how we can use a PLSQL package to perform whatever kind of parsing we went.

Bottom line - if you can come up with an *algorithm* to parse the line, we can apply that to sqlldr

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Loading tab delimited text with CLOB columns

Charles Nelson, January 14, 2018 - 10:42 pm UTC

I found it useful in the sense that it does confirm my limited understanding. Where I am uncertain of is how the loader will process the CLOB or other larger double-quoted fields that themselves may contain double quotes. (i.e. SOMEFIELD = "The equipment obtained as rental from a third party ("ACME RENTALS" ) at a monthly cost of $11,432.62 (Purchase cost "$858,912.89"). Rental being more cost effective given that the rental period is estimated to be less tham 20 months for this project." ) Was loaded successfully to MS Excel as tab-delimited input.
Connor McDonald
January 15, 2018 - 9:18 am UTC

Whoops. My apologies. I did not see that you said that the file was tab delimited. I was too busy reading about all the nested quotes.

In that case, it is simply a case of specifying tab as the delimiter

Full example here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=sqlloader-and-tab-delimited-input-data


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.